CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbench
dabadaba
dabadaba

Reputation: 9522

Error when adding foreign key

I had a table named movies which had the fields id as primary key, and two varchars: title and genre.

I created a new table named genres with the int field id as primary key and desription varchar. I changed the field genre in my movies table so I could create a foreign key referencing a genre.

However, Mysql Workbench says there's an error when creating the foreign key.

Here's the statement:

ALTER TABLE `managemovies`.`movies` 
  ADD CONSTRAINT `genre_reference`
  FOREIGN KEY (`genre` )
  REFERENCES `managemovies`.`genres` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `genre_reference_idx` (`genero` ASC) ;

Error:

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails  (`managemovies`.`#sql-3ba_2b`, CONSTRAINT `genre_reference` FOREIGN KEY (`genre`) REFERENCES `genres` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

SQL Statement: [... same statement than above ... ] ERROR: Error when running failback script. Details follow.

ERROR 1046: No database selected
SQL Statement:
CREATE TABLE `movies` [...]

[... the errors above repeated again ...]

Upvotes: 1

Views: 280

Answers (3)

THULLURU PREM CHAND
THULLURU PREM CHAND

Reputation: 1

I have faced same issue i got resolved later..

for this answer is simple you just need to add atleast a row of values in both tables then try to add the foreign key

Upvotes: 0

vishad
vishad

Reputation: 1164

It looks like your table movies has data in genre column which is not present in genres.id column.

Your statement should work after removing the invalid data.

Hope it helps

Vishad

Upvotes: 0

Remya Murali
Remya Murali

Reputation: 226

clear your table contents and try adding foreign key.

if your table contain data which not matching the foreign key field value you will see this error ...

Upvotes: 1

Related Questions