CODEWITHSUNDEEP
mysqlforeign-keysconstraints
S.Kaiser
S.Kaiser

Reputation: 13

Cannot add foreign key restraint error in MySQL?

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`)

);

Upvotes: 0

Views: 43

Answers (1)

OLIVER.KOO
OLIVER.KOO

Reputation: 6023

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.

Upvotes: 1

Related Questions