0

I have two tables movies and cinema. In the cinema table, there is a primary key called cinema_id. I am trying to make this primary key from the cinema table a foreign key in the movies table. So I used

alter table movies
add foreign key(cinema_id)
references cinema(cinema_id)

but I get an error message that says:

#1072 - Key column 'cinema_id' doesn't exist in table

so I added a column and then tried to create a foreign key again and there was an error message that said :

#1452 - Cannot add or update a child row: a foreign key constraint fails

I have already checked the column type and it is the same. Can someone please help me with this? I have no idea what to do.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
User4356
  • 3
  • 1
  • 3

1 Answers1

0

You are using a wrong syntax. You have to create a constraint and not simply add a foreign key.. Try this:

ALTER TABLE MOVIES ADD CONSTRAINT MOVIES_CINEMA_FK
FOREIGN KEY (CINEMA_ID) 
REFERENCES CINEMA(CINEMA_ID);

MySQL Documentation

agim
  • 1,841
  • 12
  • 19
  • Hi, i tried this and it still gave me the same error – User4356 Mar 01 '14 at 14:31
  • Do you have any record in the table `movies`? If yes, you must make sure, that the values are also in the table `cinema` – agim Mar 01 '14 at 14:32
  • I do have dummy data in both the cinema and movie table.So the columns in the movie table have to be in the cinema table as well? – User4356 Mar 01 '14 at 14:45
  • yes.. every value of `movies.cinema_id` has to be in `cinema.cinema_id` otherwise you have a constraint violation. – agim Mar 01 '14 at 14:51
  • ok, are you saying that all the columns in the movies table has to be in the cinema table as well? – User4356 Mar 01 '14 at 16:35
  • Not all the columns. All the values in the cinema_id in the movies table have to have exist in the cinema_id column in the cinema table. For instance if cinema_id 23 is in the movies table then there needs to be a cinema_id 23 in the cinema table. – Greg Mar 01 '14 at 16:44