CODEWITHSUNDEEP
mysqlsqlforeign-keys
Andrey Ch
Andrey Ch

Reputation: 323

MySQL: update on a table with multiple foreign key dependencies

Below is the simplified picture of the relationships I have in my DB:

create table attribute (id int auto_increment, primary key (id));
create table state_sample (id int auto_increment, primary key(id));
create table state_sample_attribute (
    state_sample_id int,
    attribute_id int,
    primary key(state_sample_id, attribute_id),
    foreign key (state_sample_id) references state_sample(id) on update cascade,
    foreign key (attribute_id) references attribute(id) on update cascade
);
create table note (
    id int auto_increment,
    state_sample_id int,
    attribute_id int,
    primary key(id),
    foreign key (state_sample_id) references state_sample(id) on update cascade,
    foreign key (state_sample_id, attribute_id)
        references state_sample_attribute(state_sample_id, attribute_id) on update cascade
);

insert into attribute values (1);
insert into state_sample values (1);
insert into state_sample_attribute values (1, 1);
insert into note values (1, 1, 1);

Whenever I try to update the ss table, it fails:

update state_sample set id = 2;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demotemplate`.`note`, CONSTRAINT `note_ibfk_1` FOREIGN KEY (`ss_id`) REFERENCES `ss` (`id`) ON UPDATE CASCADE)

As far as I understand, this is what happens:

Is my assumption correct? And if so, is there a way to work this around?

Upvotes: 1

Views: 1038

Answers (3)

Andrey Ch
Andrey Ch

Reputation: 323

What solved it in the end is dropping the extra FK:

alter table note drop foreign key (state_sample_id) references state_sample(id);

Upvotes: 0

Barmar
Barmar

Reputation: 781004

Give the ssa table its own id primary key, and use that in the foreign key in notes, rather than referencing the ss_id and a_id columns.

create table ssa (
    id int auto_increment,
    ss_id int, 
    a_id int, 
    primary key (id),
    unique key (ss_id, a_id), 
    foreign key (ss_id) references ss(id) on update cascade, 
    foreign key (a_id) references a(id) on update cascade);
create table note (
    id int auto_increment, 
    ss_id int, 
    ssa_id int, 
    primary key(id), 
    foreign key (ss_id) references ss(id) on update cascade, 
    foreign key (ssa_id) references ssa(id) on update cascade);

Now you don't have the redundant dependency.

It's also not clear that note needs ss_id at all, since it's redundant with the related ssa row.

Upvotes: 2

Andrey
Andrey

Reputation: 1759

Try

DISABLE KEYS

or

SET FOREIGN_KEY_CHECKS=0;

make sure to turn it on 

SET FOREIGN_KEY_CHECKS=1;

after.

Upvotes: 1

Related Questions