I am trying to make a rental database using mysql (Workbench). The first table w

Question

I am trying to make a rental database using mysql (Workbench). The first table was created without incident. Here is the code I used:

CREATE TABLE Customer

(

cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

cust_fname VARCHAR(20) NOT NULL,

cust_lname VARCHAR(20) NOT NULL,

cust_street VARCHAR(20) NOT NULL,

cust_city VARCHAR(20) NOT NULL,

cust_state VARCHAR(20) NOT NULL,

cust_zip VARCHAR (20) NOT NULL,

PRIMARY KEY (cust_id) ) ENGINE=InnoDB;

Now I am trying to set up the second table and I get errors: "(cust_ID) does not exist" and "cannot add foreign key constraint". Here is the code I used:

CREATE TABLE movie_info
(
movie_id INT UNSIGNED NOT NULL,
movie_title varchar (20) NOT NULL,
movie_releasedate DATE NOT NULL,
movie_rating varchar (20) NOT NULL,
movie_qty Int (6) not null,
PRIMARY KEY (movie_id),
FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
) ENGINE=InnoDB;

I am VERY new to this and cannot figure out what I'm doing wrong. Please help!

Explanation / Answer

foreign key is a attribute/column which is defined in one table and refernces to other table through its primary key

i.e. a attribute which is a part of schema of one table is only used for refrencing to an attribute which is part of schema of table to which you are refrencing the forst table

and thats is what the problem in this schema

you are using cust_id as foreign key in second table but it is nowhere defined earlier and thats why you get error: "(cust_ID) does not exist" because it does not.

and also take care when you define cust_id in second table it should

have the same datatype as of the attribute of the attribute cust_id from first table ie int unsigned which may be a probable reason to get  "cannot add foreign key constraint"

also make sure the attribute from the first table is primary key

so, the correct syntax will be:

CREATE TABLE Customer

(

cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

cust_fname VARCHAR(20) NOT NULL,

cust_lname VARCHAR(20) NOT NULL,

cust_street VARCHAR(20) NOT NULL,

cust_city VARCHAR(20) NOT NULL,

cust_state VARCHAR(20) NOT NULL,

cust_zip VARCHAR (20) NOT NULL,

PRIMARY KEY (cust_id) ) ENGINE=InnoDB;

CREATE TABLE movie_info
(
movie_id INT UNSIGNED NOT NULL,
movie_title varchar (20) NOT NULL,
movie_releasedate DATE NOT NULL,
movie_rating varchar (20) NOT NULL,
movie_qty Int (6) not null,

cust_id INT UNSIGNED,
PRIMARY KEY (movie_id),
FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
) ENGINE=InnoDB;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.