CODEWITHSUNDEEP
mysqlforeign-keys
Ben Dilts
Ben Dilts

Reputation: 10745

Change value of primary key (ID of a particular record) when foreign key constraints exist

Here's the basic gist of what I'm trying to do:

create table main(id char(1) primary key);

create table other (
  id int primary key auto_increment,
  main_id char(1),
  key m (main_id),
  constraint fk foreign key (main_id) references main(id)
);

insert into main(id) values('a');
insert into other(main_id) values('a');

update main inner join other on other.main_id=main.id
set main.id='b', other.main_id='b'
where main.id='a';

This results in a foreign key constraint failure. Is there any way to accomplish this without dropping the foreign keys (not really an option on my large production database)?

Upvotes: 2

Views: 143

Answers (1)

Ike Walker
Ike Walker

Reputation: 65587

You can do this simply by temporarily setting foreign_key_checks=0 in your session:

set foreign_key_checks=0;

update main inner join other on other.main_id=main.id
set main.id='b', other.main_id='b'
where main.id='a';

Another option is to configure the foreign key with the ON UPDATE CASCADE option so that if the primary key is updated on the parent table it will cascade to the child table:

create table main(id char(1) primary key);

create table other (
  id int primary key auto_increment,
  main_id char(1),
  key m (main_id),
  constraint fk foreign key (main_id) references main(id) on update cascade
);

Upvotes: 2

Related Questions