Danestyles Danestyles - 1 year ago 127
SQL Question

sql fiddle code for foreign key and primary key

I need help trying to figure out the code for these two tables. I am using SQL Fiddle.

Oder Table

Donut Order ID (PK) INT (10)
Customer ID(FK) INT (10)
Order Date DATETIME (6)
Special Notes VARCHAR (30)

Order Line Item Table

Donut Order ID (PK)(FK) INT (10)
Donut ID (PK)(FK) INT (10)
Qty INT (10)

I have my Customer Table and my Donut Table but I keep getting errors when I try to tables with foreign keys. Any help would be greatly appreciated.

Answer Source

I have tried your schema and there schema and it is working fine for me

Try this to create schema for OrderTable with foreign key reference

 CREATE TABLE `OrderTable` (
  `donutorderid` varchar(10) NOT NULL,
  `customerid` varchar(10) NOT NULL,
  `oderdate` datetime(6) NOT NULL,
  PRIMARY KEY (`donutorderid`),
    CONSTRAINT `ordertable_ibfk_1` FOREIGN KEY (`customerid`) REFERENCES `Customer` (`customer_id`)

So there was a small error in your ALTER command

in Customer table the name of field is customer_id and not customerid.

Try this:-

ALTER TABLE OrderTable ADD INDEX checks (customerid), ADD CONSTRAINT checks FOREIGN KEY (customerid) REFERENCES Customer (customer_id); 

SQLfiddle for same:- http://sqlfiddle.com/#!9/0eb295

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download