Development Urgent! SQL help with foreign keys!!!

julianmartin · 26 Mar 2009

Hi guys, sorry this is obviously a coursework help thread - I might as well come out with it!

Really struggling with SQL here, have a deadline in a few hours and towards the end of this coursework I have stumbled across a pretty big problem with foreign keys.

When I try to call a foreign key, say cart_id from table Item, it just comes up blank.

What have I done wrong?

CREATE TABLE Cart (
cart_id CHAR(10) NOT NULL,
cart_description TEXT NULL,
cart_contents INTEGER UNSIGNED NULL,
minimum_stock_level INTEGER UNSIGNED NULL,
maximum_stock_level INTEGER UNSIGNED NULL,
cart_barcode CHAR(10) NOT NULL,
supplier_id CHAR(10) NOT NULL
);

CREATE TABLE Item (
item_id CHAR(10) NOT NULL,
item_description TEXT NULL,
item_sale_price DECIMAL(8,2) NULL,
item_cost_price DECIMAL(8,2) NULL,
item_shelf_level INTEGER UNSIGNED NULL,
minimum_shelf_level INTEGER UNSIGNED NULL,
maximum_shelf_level INTEGER UNSIGNED NULL,
item_barcode CHAR(10) NULL,
cart_id CHAR(10) NOT NULL,
cart_barcode CHAR(10) NOT NULL

);

CREATE TABLE Order_1 (
order_id CHAR(10) NOT NULL,
order_date DATETIME NULL,
date_required DATETIME NULL,
supplier_id CHAR(10) NOT NULL,
cart_contents CHAR(10) NOT NULL

);

CREATE TABLE Order_Item (
order_id CHAR(10) NOT NULL,
item_cost_price DECIMAL(8,2) NULL,
cart_quantity INTEGER UNSIGNED NULL,
cart_contents INTEGER UNSIGNED NULL,
cart_id CHAR(10) NOT NULL

);

CREATE TABLE Supplier (
supplier_id CHAR(10) NOT NULL,
supplier_address TEXT NULL,
supplier_city TEXT NULL,
supplier_county TEXT NULL,
supplier_postcode CHAR(7) NULL,
supplier_tel CHAR(14) NULL,
cart_id CHAR(10) NOT NULL,
supplier_barcode TEXT(10) NULL

);

ALTER TABLE Cart ADD PRIMARY KEY (cart_id);
ALTER TABLE Item ADD PRIMARY KEY (item_id);
ALTER TABLE Order_1 ADD PRIMARY KEY (order_id);
ALTER TABLE Order_item ADD PRIMARY KEY (order_id);
ALTER TABLE Supplier ADD PRIMARY KEY (supplier_id);

ALTER TABLE Item ADD CONSTRAINT FK_Item_Cart FOREIGN KEY (cart_id) REFERENCES Cart (Cart_id);
ALTER TABLE Item ADD CONSTRAINT FK_Item_Cart2 FOREIGN KEY (cart_barcode) REFERENCES Cart (Cart_barcode);
ALTER TABLE Cart ADD CONSTRAINT FK_Cart_Supplier FOREIGN KEY (supplier_id) REFERENCES Supplier (supplier_id);
ALTER TABLE Order_1 ADD CONSTRAINT FK_Order_1_Supplier FOREIGN KEY (supplier_id) REFERENCES Supplier (supplier_id);
ALTER TABLE Order_1 ADD CONSTRAINT FK_Order_1_Cart FOREIGN KEY (cart_contents) REFERENCES Cart (cart_contents);
ALTER TABLE Order_Item ADD CONSTRAINT FK_Order_Item_Order_1 FOREIGN KEY (order_id) REFERENCES Order_1 (order_id);
ALTER TABLE Order_Item ADD CONSTRAINT FK_Order_Item_Cart FOREIGN KEY (cart_id) REFERENCES Cart (cart_id);
ALTER TABLE Supplier ADD CONSTRAINT FK_Supplier_Cart FOREIGN KEY (cart_id) REFERENCES Cart (cart_id);
ALTER TABLE Item ADD CONSTRAINT FK_Item_Order_Item FOREIGN KEY (item_cost_price) REFERENCES Order_Item (item_cost_price);

INSERT INTO Cart(cart_id, cart_description, cart_contents, minimum_stock_level, maximum_stock_level, cart_barcode)
VALUES('C001', 'Chateau Fonroque Half Case', '6', '4', '10', 'CHFQSE');

INSERT INTO Cart(cart_id, cart_description, cart_contents, minimum_stock_level, maximum_stock_level, cart_barcode)
VALUES('C002', 'Chateau Mouton Rothschild Case', '12', '1', '5', 'CHMRPL');

INSERT INTO Cart(cart_id, cart_description, cart_contents, minimum_stock_level, maximum_stock_level, cart_barcode)
VALUES('C003', 'Chateau Angelus Half Case', '6', '4', '10', 'CHANSE');

INSERT INTO Cart(cart_id, cart_description, cart_contents, minimum_stock_level, maximum_stock_level, cart_barcode)
VALUES('C004', 'Chateau Petrus Half Case', '6', '1', '3', 'CHPEPO');

INSERT INTO Item(item_id, item_description, item_sale_price, item_cost_price, item_shelf_level, minimum_shelf_level, maximum_shelf_level, item_barcode)
VALUES('i001', 'Chateau Fonroque', '35.99', '16.50', '10', '8', '12', 'BCHFQSE');

INSERT INTO Item(item_id, item_description, item_sale_price, item_cost_price, item_shelf_level, minimum_shelf_level, maximum_shelf_level, item_barcode)
VALUES('i002', 'Chateau Mouton Rothschild', '149.99', '90.00', '4', '2', '6', 'BCHMRPL');

INSERT INTO Item(item_id, item_description, item_sale_price, item_cost_price, item_shelf_level, minimum_shelf_level, maximum_shelf_level, item_barcode)
VALUES('i003', 'Chateau Angelus', '129.99', '84.00', '4', '8', '12', 'BCHANSE');

INSERT INTO Item(item_id, item_description, item_sale_price, item_cost_price, item_shelf_level, minimum_shelf_level, maximum_shelf_level, item_barcode)
VALUES('i004', 'Chateau Petrus', '299.99', '141.00', '4', '2', '6', 'BCHPEPO');

INSERT INTO Order_1(order_id, order_date, date_required)
VALUES('od01', '2008-02-03', '2008-02-28');

INSERT INTO Order_1(order_id, order_date, date_required)
VALUES('od02', '2008-03-09', '2008-03-31');

INSERT INTO Order_1(order_id, order_date, date_required)
VALUES('od03', '2008-01-03', '2008-01-15');

INSERT INTO Order_1(order_id, order_date, date_required)
VALUES('od04', '2008-06-12', '2008-06-29');

INSERT INTO Order_Item(order_id, cart_quantity)
VALUES('od01', '7');

INSERT INTO Order_Item(order_id, cart_quantity)
VALUES('od02', '12');

INSERT INTO Order_Item(order_id, cart_quantity)
VALUES('od03', '5');

INSERT INTO Order_Item(order_id, cart_quantity)
VALUES('od04', '6');

INSERT INTO Supplier(supplier_id, supplier_address, supplier_city, supplier_county, supplier_postcode, supplier_tel, supplier_barcode)
VALUES('sup01', '14 Marrow Hill', 'Luton', 'Oxfordshire', 'OX12DF9', '01252812391', 'WNEM');

INSERT INTO Supplier(supplier_id, supplier_address, supplier_city, supplier_county, supplier_postcode, supplier_tel, supplier_barcode)
VALUES('sup02', 'Vin House, Rhone Drive', 'Portsmouth', 'Hampshire', 'PO16NA', '02392471379', 'POVN');

INSERT INTO Supplier(supplier_id, supplier_address, supplier_city, supplier_county, supplier_postcode, supplier_tel, supplier_barcode)
VALUES('sup03', '41 Break Street', 'Northampton', 'Northamptonshire', 'NN114RT', '01604812471', 'NHBNS');

INSERT INTO Supplier(supplier_id, supplier_address, supplier_city, supplier_county, supplier_postcode, supplier_tel, supplier_barcode)
VALUES('sup04', 'Extreme Wines LTD Bagshot Road', 'Chobham', 'Surrey', 'KT319GH', '01898451287', 'XTWN');
Click to expand...

Any help would be SERIOUSLY appreciated as I'm going nuts here.....

Alex6969 · 26 Mar 2009

It's been a while but let's see if I still have the magic touch.

Try adding "CONSTRAINT FK Cart_ID" in your create table code. That tells the DB that "Cart_ID" is the foreign key for that table. Also throw in a "CONSTRAINT PK 'whatever'". This will give your table a Primary key as well.

Also, depending on what software you are using, Oracle, M$ SQL Server Mgnt. Studio, or Access, have different ways you need access data from tables. Ensure that you are referencing the table correctly, etc, etc. I won't go on about all that since I'm sure you know that already.

Good luck!

julianmartin · 27 Mar 2009

Thanks Alex...transpires FK's aren't as useful as they should be in the MySQL version that our uni runs....explaining my loss of understanding, but also meaning my repeated efforts were futile. Great!

Thanks again though. Rep+

Log in or Sign up

Development Urgent! SQL help with foreign keys!!!

julianmartin resident cyborg.

Alex6969 If it's fixed, I can break it.

julianmartin resident cyborg.

Share This Page

Log in or Sign up

Development Urgent! SQL help with foreign keys!!!

julianmartin resident cyborg.

Alex6969 If it's fixed, I can break it.

julianmartin resident cyborg.

Share This Page

Useful Searches