CODEWITHSUNDEEP
mysqldatabase
Darko Romanov
Darko Romanov

Reputation: 2805

MySQL One-To-Many Foreign Key strategy

I have a table:

instances

with the PK called 'id' (int autoincrement)

and two other tables:

values_text

values_int

these 2 tables have these fields:

instance_id / code / value

with a PK made by instance_id and code.

A record of instance can have 0, 1 or many related records in values_text and values_int.

I want to add foreign keys from instances to values_int and values_text so that when I delete a record from instances the related records are deleted too from values_text and values_int.

I was able to create the foreign keys, the problem is that I can't create a record in instance table because I get the error:

Cannot add or update a child row: a foreign key constraint fails (instances, CONSTRAINT instances_fk_ab43df FOREIGN KEY (id) REFERENCES values_text (instance_id))

How can I set correctly the foreign keys?

Upvotes: 0

Views: 81

Answers (1)

void
void

Reputation: 7890

you have created the PK/FK vice versa, in fact your foreign key is instance_id (in both values_text and values_text tables) not id in instances.

remove the foreign key constraint on id in instances instead of it make the foreign key constraint on instance_id (in both values_text and values_text tables) and also leave id in instances as primary key.

Upvotes: 2

Related Questions