2 Replies - 1765 Views - Last Post: 29 March 2013 - 02:55 AM

#1 RonJon22  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 28-March 13

Im am getting an error on my foreign keys.

Posted 28 March 2013 - 04:44 PM

I have everything working but my SHOPPING_CART is having trouble.
I needs to make customerid (FK into the customers table) and the productID (FK into the products table). But I keep getting an error in the SHOPPING_CART. Im so stuck. This is my first year with sql by the way. I kind of got it but the keys are throwing me off. Any help thanks.


DROP DATABASE IF EXISTS WebStore;
CREATE DATABASE WebStore;
USE WebStore;

CREATE TABLE Categories (
  CatID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name VARCHAR(20) NOT NULL,
  Descr VARCHAR(120) NOT NULL,
  IconURL VARCHAR(64) NOT NULL,
  PRIMARY KEY (CatID)
);

CREATE TABLE Products (
  ProdID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name VARCHAR(20) NOT NULL,
  Category INTEGER UNSIGNED,
  Descr VARCHAR(120) NOT NULL,
  Price FLOAT NOT NULL,
  Stock INTEGER UNSIGNED NOT NULL,
  IconURL VARCHAR(64) NOT NULL,
  PRIMARY KEY (ProdID),
  CONSTRAINT FK_Cat FOREIGN KEY FK_Cat (Category)
    REFERENCES Categories (CatID)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

INSERT INTO Categories VALUES(null,"Laptops","Small computers you can 

carry","http://localhost/images/laptop.jpg");
SET @laptops := LAST_INSERT_ID();
INSERT INTO Categories VALUES(null,"Desktops","Big computers you cannot 

carry","http://localhost/images/desktop.jpg");
SET @desktops := LAST_INSERT_ID();
INSERT INTO Categories VALUES(null,"Tablets"," Flat things you lose 

frequently","http://localhost/images/tablet.jpg");
SET @tablets := LAST_INSERT_ID();

INSERT INTO Products VALUES(null,"DULL 1200",@desktops,"A big dull 

machine",1290.99,23,"http://localhost/images/dull1200.jpg");
INSERT INTO Products VALUES(null,"8P Totalo",@desktops,"Almost as big as 

the DULL",990.99,2,"http://localhost/images/8ptotalo.jpg");
INSERT INTO Products VALUES(null,"LaNuveau Bingster",@desktops,"Comes in 

blue and red",690.99,12,"http://localhost/images/lanuveaubingster.jpg");

INSERT INTO Products VALUES(null,"DULL 122",@laptops,"Small, portable and 

useless",422.99,4,"http://localhost/images/dull122.jpg");
INSERT INTO Products VALUES(null,"8P Tootsie",@laptops,"Sticky and too 

heavy",559.99,12,"http://localhost/images/8ptootsie.jpg");
INSERT INTO Products VALUES(null,"LaNuveau Shoobie XT",@laptops,"Weighs a 

ton but looks 

sharp",1690.99,122,"http://localhost/images/lanuveaushoobiext.jpg");

INSERT INTO Products VALUES(null,"Motor Roller 12",@tablets,"The only one 

with a oval screen",422.99,4,"http://localhost/images/mr12.jpg");
INSERT INTO Products VALUES(null,"SamSings OffKey",@tablets,"Needs duct 

tape to run",559.99,2,"http://localhost/images/samsingsOK.jpg");
INSERT INTO Products VALUES(null,"jPet 12",@tablets,"The first that kinda 

sorta works",16290.99,722,"http://localhost/images/jpet12.jpg");



CREATE TABLE Customers (
  CustID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  FirstName VARCHAR(40) NOT NULL,
  LastName VARCHAR(40) NOT NULL,
  Address VARCHAR(60) NOT NULL,
  City VARCHAR(40) NOT NULL,
  State VARCHAR(20) NOT NULL,
  Zip VARCHAR(5) NOT NULL,
  PRIMARY KEY (CustID)
);

INSERT INTO Customers VALUES(null,"Gerald","Bostock","1234 TAAB 

Drive","St. Cleve","FL","12345");
INSERT INTO Customers VALUES(null,"Suzy","Creamcheese","8722 Zappa 

Road","Paris","TX","75460");

CREATE TABLE shopping_cart (
customerID VARCHAR(40) NOT NULL,
productID VARCHAR(40) NOT NULL,
anumber integer unsigned,
Primary key (customerID,productID),

CONSTRAINT FK_Cus FOREIGN KEY FK_Cus (customerID)
REFERENCES Customers (FirstName)

);


Is This A Good Question/Topic? 0
  • +

Replies To: Im am getting an error on my foreign keys.

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Im am getting an error on my foreign keys.

Posted 29 March 2013 - 02:44 AM

Line 99 should be:
CONSTRAINT FK_Cus FOREIGN KEY (customerID)


Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3806
  • View blog
  • Posts: 13,488
  • Joined: 12-December 12

Re: Im am getting an error on my foreign keys.

Posted 29 March 2013 - 02:55 AM

and it should really be connecting cutomerID to CustID not FirstName, which is not unique.

This post has been edited by andrewsw: 29 March 2013 - 02:56 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1