• 0

Basic MySql Inventory, what am I doing wrong?


Question

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. :(

2 answers to this question

Recommended Posts

  • 0

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.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • That would be nice, if excel could refresh pivot tables without bugs. Frequently you refresh a pivot and all parameters get lost. Now this will happen automatically. Bravo MS!
    • If one could ever put all the crap of the world in one box, that would be Microsoft.
    • The most corrupt and vile CEO any company ever had ! The only company that makes an insane amount of money decade after decade but doesn't deliver on even barely passable quality to customers, screws over partners and treats employees like trash
    • PSA: Some other versions of Windows are losing support on October 14 too by Usama Jawad All of us here at Neowin likely know pretty well by now that Windows 10 is reaching end of support on October 14, 2025. You can extend support through paid and "free" means, but if you don't, you won't get any more security or feature updates following the aforementioned date. We also highlighted that the Long-Term Servicing Channel (LTSC) version Windows 10 22H2 is also reaching end of support on that date. Now, Microsoft has reminded customers that yet another variant of Windows is reaching end of life on October 14, 2025 too. On the Windows Release Health dashboard, Microsoft has published a reminder that the Enterprise, Education, and IoT SKUs of Windows 11, version 22H2 will hit end of support on October 14 as well. It is important to keep in mind that the Home and Pro variants of Windows 11, version 22H2 already reached end of life on October 8, 2024, and the extra year of lease on life will end for other SKUs within a few months too. Windows 11, version 22H2 for IoT, Enterprise, and Education was released on September 20, 2022, which means that they would have received just over three years of support by the time they "die", compared to the regular two years for Home and Pro. Customers on any of these versions should consider upgrading to version 23H2 or 24H2 as soon as possible, which have end of support dates of November 10, 2026 and October 12, 2027, respectively. Staying on a supported version of Windows is crucial as that allows you to receive regular security updates on your machine. If you're an IT admin, you should immediately begin planning a migration to a supported version of the operating system, and if you're an employee or someone using these versions of Windows in some other scenario, go to Settings > System > About and check out Windows Specifications > Version.
  • Recent Achievements

    • Dedicated
      Profit earned a badge
      Dedicated
    • One Month Later
      hhgygy earned a badge
      One Month Later
    • Week One Done
      hhgygy earned a badge
      Week One Done
    • One Year In
      NIKI77 earned a badge
      One Year In
    • Week One Done
      artistro08 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      632
    2. 2
      ATLien_0
      237
    3. 3
      Xenon
      167
    4. 4
      neufuse
      146
    5. 5
      +FloatingFatMan
      123
  • Tell a friend

    Love Neowin? Tell a friend!