Cannot add foreign key restraint error in MySQL?

Question

I keep getting the same error in MySQL and i'm not sure what I missing here. I am very new to working with MySQL, so I am not sure if I need to add a constraint maybe? If I do not need a constraint, is there another way to go about defining the relationships? Below is my syntax:

    DROP TABLE IF EXISTS `art`;
    CREATE TABLE `art` (

   `art_id` INT NOT NULL,
   `art_name` VARCHAR(45) NULL,
   `artist_id` INT(11) NULL,
   `location_id` INT(11) NULL,
   `employee_id` INT(11) NULL,
   `art_received` DATE NULL,

  primary key (`art_id`),
  FOREIGN KEY (`artist_id`) REFERENCES artist(`artist_id`),
  FOREIGN KEY (`location_id`) REFERENCES location(`location_id`),
  FOREIGN KEY (`employee_id`) REFERENCES employee(`employee_id`)

  ); 

  DROP TABLE IF EXISTS `employee`;
 CREATE TABLE `employee` (

`employee_id` int(11),
`employee_userid` varchar(45),
`employee_fname` varchar(45),
`employee_lname` varchar(45),
`employee_phone` varchar (10),

primary key (`employee_id`)

); 

 DROP TABLE IF EXISTS `artist`;
 CREATE TABLE `artist` (
`artist_id` int(11),
`artist_first` varchar(45),
`artist_last` varchar(45),
`artwork_title` varchar(45),

primary key (`artist_id`)

 ); 

 DROP TABLE IF EXISTS `location`;
 CREATE TABLE `location` (
`location_id` int(11),
`location_name` varchar(45),
`start_date` date,
`end_date` date,

primary key (`location_id`)

);

If you are doing it in that order, there will be a problem with the art creating a foreign key to a table that does not exist. You need to make sure that all the tables art is referencing are created first. — SenSok
– SenSok, Commented Jul 7, 2017 at 21:25

OLIVER.KOO · Accepted Answer · 2017-07-07 21:43:55Z

simply switch the order of which table gets created first. when you created table art the other three table still have not be created so it doesn't know what artist(artist_id),location(location_id),employee(employee_id) are.

 DROP TABLE IF EXISTS `employee`;
 CREATE TABLE `employee` (

   `employee_id` int(11),
   `employee_userid` varchar(45),
   `employee_fname` varchar(45),
   `employee_lname` varchar(45),
   `employee_phone` varchar (10),

   primary key (`employee_id`)

); 

 DROP TABLE IF EXISTS artist;
 CREATE TABLE `artist` (
   `artist_id` int(11),
   `artist_first` varchar(45),
   `artist_last` varchar(45),
   `artwork_title` varchar(45),

   primary key (`artist_id`)

 ); 

 DROP TABLE IF EXISTS `location`;
 CREATE TABLE `location` (
   `location_id` int(11),
   `location_name` varchar(45),
   `start_date` date,
   `end_date` date,

   primary key (`location_id`)

); 



DROP TABLE IF EXISTS `art`;
CREATE TABLE `art` (

   `art_id` INT NOT NULL,
   `art_name` VARCHAR(45) NULL,
   `artist_id` int(11) NULL,
   `location_id` int(11) NULL,
   `employee_id` int(11) NULL,
   `art_received` DATE NULL,

    primary key (`art_id`),

    FOREIGN KEY (`artist_id`) REFERENCES artist(`artist_id`),
    FOREIGN KEY (`location_id`) REFERENCES location(`location_id`),
    FOREIGN KEY (`employee_id`) REFERENCES employee(`employee_id`)



);

And welcome to StackOverflow. if you find this answer or any other answer helpful please mark it as the solution. That way would help the community and if fellow programmers run into the same problem as you did in the future they can find the solution easily.

This makes perfect sense, and worked perfectly. Thank you for your help.

Collectives™ on Stack Overflow

Cannot add foreign key restraint error in MySQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related