Basic MySql Inventory, what am I doing wrong?

[Navan]

hey guys, just practicing and learning mysql. could you help me out with what I'm doing wrong here? I'm unable to connect the Inventory table with PurchaseOrder table using the StoreID as a foreign key in the PurchaseOrder table.

 

 

/* Drop Tables if already exist */
DROP TABLE IF EXISTS SalesOrder;
DROP TABLE IF EXISTS PurchaseOrder;
DROP TABLE IF EXISTS CustomerDetails;
DROP TABLE IF EXISTS Inventory;
DROP TABLE IF EXISTS SupplierDetails;
/* Creation of full set of tables */
CREATE TABLE CustomerDetails (
    CustomerID INT (7) NOT NULL AUTO_INCREMENT,
    CustFirstName VARCHAR (10) NOT NULL,
    CustLastName VARCHAR (10) NULL,
    CustAddress VARCHAR (25) NOT NULL,
    CustPhone INT (10) NULL,
    PRIMARY KEY (CustomerID)
);
ALTER TABLE CustomerDetails AUTO_INCREMENT = 123001;
    
CREATE TABLE Inventory (
    ProductID INT (7) NOT NULL,
    StoreID INT (15) NOT NULL,
    StoreName VARCHAR (10) NOT NULL,
    ProdQty INT (7) NULL,
    PRIMARY KEY (ProductID, StoreID)
);
    
CREATE TABLE SalesOrder (
    SalesID INT (7) NOT NULL AUTO_INCREMENT,
    CustomerID INT (7) NOT NULL,
    ProdID INT (7) NOT NULL,
    ProductName VARCHAR (10) NULL,
    SalesQty INT (7) NOT NULL,
    SalesPrice INT (10) NOT NULL,
    DateOfSale INT (10) NOT NULL,
    PRIMARY KEY (SalesID),
    FOREIGN KEY (ProdID) REFERENCES Inventory (ProductID),
    FOREIGN KEY (CustomerID) REFERENCES CustomerDetails (CustomerID)
);
ALTER TABLE SalesOrder AUTO_INCREMENT = 246001;
CREATE TABLE SupplierDetails (
    SupplierID INT (7) NOT NULL,
    ItemID INT (7) NOT NULL,
    ItemName VARCHAR (10) NULL,
    SupplierName VARCHAR (10) NOT NULL,
    SupplierAddress VARCHAR (20) NOT NULL,
    SupplierPhone INT (10) NULL,
    PRIMARY KEY (SupplierID, ItemID)
);
CREATE TABLE PurchaseOrder (
    DeptID INT (15) NOT NULL,
    PurchaseID INT (7) NOT NULL AUTO_INCREMENT,
    SupID INT (7) NOT NULL,
    ArticleID INT (7) NOT NULL,
    PurchaseQty INT (7) NOT NULL,
    PurchaseCost INT (10) NOT NULL,
    PurchaseDate INT (10) NOT NULL,
    PRIMARY KEY (PurchaseID),
    FOREIGN KEY (SupID, ArticleID) REFERENCES SupplierDetails (SupplierID, ItemID),
    FOREIGN KEY (DeptID) REFERENCES Inventory (StoreID)
);
ALTER TABLE PurchaseOrder AUTO_INCREMENT = 369001; 

I end up with the error that can not create PurchaseOrder table.

[Seahorsepip Veteran]

Try changing:

DeptID INT (15) NOT NULL,
PurchaseID INT (7) NOT NULL AUTO_INCREMENT,

to:

PurchaseID INT (7) NOT NULL AUTO_INCREMENT,
DeptID INT (15) NOT NULL,

It's probably just the order of columns that causes the issue. Otherwise if above does not work remove all items from the CREATE TABLE PurchaseOrder and add one at a time to test which item causes the problem.

[Navan]

I just created a new db. Thanks for your time.