I am trying to set up a relationship between two tables in SQL script but the error 1452 keeps coming up every time I run this. I have no idea why & have tried everything. I have "description" from the stock table as the foreign key in my customer table. "Description" is the primary key in the stock table. What am I doing wrong. I am very new to SQL so please excuse my ignorance if this is indeed a simple error.
Thanks,
DROP DATABASE IF EXISTS dunnes;
create database dunnes;
USE dunnes;
DROP table IF exists CUSTOMERS;
DROP table if exists MOVIES;
DROP table if exists STOCK;
DROP table if exists TRANSACTIONS;
CREATE TABLE MOVIES(
genre varchar(10) not null,
title varchar (25) not null,
released NUMERIC (4),
actor varchar (30) Not null,
movie_Id varchar (6) PRIMARY KEY);
create table STOCK(
item_No varchar(5) NOT NULL,
cost numeric (4,2) DEFAULT 00.00 NOT NULL,
description varchar (30) PRIMARY KEY,
quantity numeric (10) not null);
CREATE table TRANSACTIONS (
transaction_Id NUMERIC (4) PRIMARY KEY,
trans_Date DATE NOT NULL,
return_Date DATE NOT NULL,
cost numeric (4,2) DEFAULT 00.00 not null,
movie_Id varchar (6),
title varchar (25) not null,
index (movie_Id),
CONSTRAINT FK_TR_movie_Id foreign key (movie_Id) references MOVIES(movie_Id)
);
CREATE table CUSTOMERS (
cust_Num NUMERIC (4) PRIMARY KEY,
cust_Fname VARCHAR (20) NOT NULL,
cust_Sname VARCHAR (20) NOT NULL,
cust_address VARCHAR (50) NOT NULL,
cust_dob DATE NOT NULL,
cust_membership VARCHAR (10) NOT NULL,
description varchar (30) NOT NULL,
cost numeric (4,2) DEFAULT 00.00 NOT NULL,
index (description),
CONSTRAINT FK_TR_description foreign key (description) references STOCK(description)
);
INSERT INTO customers VALUES (001, "Denise" , "Geoghegan" , "1 Main Street, Galway" , "1990-05-22", "Active", "King Crisps", "0.99");
INSERT INTO customers VALUES (002, "John", "Doe", "Apartment 17, Sandyford, Dublin", "1992-06-01", "Active", "Pringles - Small","1.50" );
INSERT INTO customers VALUES (003, "Rebecca", "Smyth", "13 Hillcrest, Lucan, Co. Dublin", "1989-10-21", "Expired", "Pringles - Large", "2.50");
INSERT INTO customers VALUES (004, "Ronan", "O'Neill", "250 Navan Road, Dublin 15", "1985-11-22", "Active", "Mars Bar", "00.80");
INSERT INTO customers VALUES (005, "Todd", "Richards", "5 Brookfield Lodge, Maynooth, Co. Kildare", "1995-05-17", "Active", "Twix Bar", "00.80");
INSERT INTO customers VALUES (006, "Eileen", "Llyod", "15 Seaview, Camolin, Co. Wexford", "1985-03-02", "Active","Can - Coke", "01.10" );
INSERT INTO customers VALUES (007, "Siobhan", "Coonan", "15 South Main Street, Howth , Co. Dublin", "1975-07-22", "Expired", "Can - Club Orange", "01.10");
INSERT INTO customers VALUES (008, "Ollie", "Ryan", "18 Ryebridge, Celbridge. Co. Kildare", "1994-05-22", "Active", "Can - 7up", "01.10");
INSERT INTO customers VALUES (009, "Nuala", "McLoughlin", "151 Richmond Hill, Howth, Co. Dublin", "1987-06-12", "Expired", "Popcorn - Small", "00.99");
INSERT INTO customers VALUES (010, "Richard", "Finn", "115 Newtown Hall, Kilcock, Co. Wexford", "1985-12-25", "Expired", "Popcorn - SmalL", "1.99");
commit;
commit;
insert into movies values ("Action", "Rocky I", "1977", "Sylvester Stalone", "7701AM");
insert into movies values ("Romcom", "The Holiday", "2006", "Cameron Diaz", "0677RC");
insert into movies values ("Romcom", "Love Actually", "2003", "Hugh Grant", "0378RC");
insert into movies values ("Action", "Rocky II", "1979", "Sylvester Stalone", "7999AR");
insert into movies values ("Children", "Disney - Peter Pan", "1953", "Bobby Driscoll", "5366CM");
insert into movies values ("Children", "Disney - The Lion King", "1994", "Jeremy Irons", "9452CM");
insert into movies values ("Action", "Rocky III", "1982", "Sylvester Stalone", "8286AM");
insert into movies values ("Drama", "My Girl", "1991", "Anna Chlumsky", "9165DM");
insert into movies values ("Action", "Sherlock Holmes", "2009", "Robert Downey Jr.", "0942AM");
insert into movies values ("Drama", "Man on Fire", "2004", "Denzel Washington", "0412DM");
commit;
insert into stock values ("A101", "00.99", "King Crisps", "100");
insert into stock values ("A102", "1.50", "Pringles Small", "77");
insert into stock values ("A103", "2.50", "Pringles Large", "24");
insert into stock values ("A104", "00.80", "Mars Bar", "112");
insert into stock values ("A105", "00.80", "Twix Bar", "40");
insert into stock values ("A106", "1.10", "Can - Coke", "30");
insert into stock values ("A107", "1.10", "Can - Club Orange", "55");
insert into stock values ("A108", "1.10", "Can - 7Up", "15");
insert into stock values ("A109", "00.99", "Popcorn - Small", "163");
insert into stock values ("A1010", "1.99", "Popcorn - Large", "44");
commit;
INSERT INTO transactions values (2311, "2016-11-01", "2016-11-25", "2.50", "7701AM", "Rocky I");
INSERT INTO transactions values (2312, "2016-11-01", "2016-11-25", "2.50", "0677RC", "The Holiday" );
INSERT INTO transactions values (2413, "2016-11-02", "2016-11-27", "5.00", "0378RC", "Love Actually");
INSERT INTO transactions values (2414, "2016-11-02", "2016-11-29", "2.50", "7999AR", "Rocky II");
INSERT INTO transactions values (2515, "2016-11-05", "2016-12-01", "2.50", "5366CM", "Disney - Peter Pan");
INSERT INTO transactions values (1016, "2016-11-10", "2016-12-05", "7.50", "9452CM", "Disney - The Lion King");
INSERT INTO transactions values (1517, "2016-11-05", "2016-12-08", "2.50", "8286AM", "Rocky III" );
INSERT INTO transactions values (1011, "2016-11-1", "2016-12-12", "5.00", "9165DM", "My Girl");
INSERT INTO transactions values (1211, "2016-11-22", "2016-12-15", "2.50", "0942AM", "Sherlock Holmes");
INSERT INTO transactions values (2512, "2016-11-30", "2016-12-19", "5.50", "0412DM", "Man on Fire" );
commit;
select * from CUSTOMERS
Here is the full error:
INSERT INTO customers VALUES (001, "Denise" , "Geoghegan" , "1 Main Street, Galway" , "1990-05-22", "Active", "King Crisps", "00.99")
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (dunnes.customers, CONSTRAINT FK_TR_description FOREIGN KEY (description) REFERENCES stock (description))
