外部キー更新時挙動確認

• MySQL

(8.0.22)

drop table tab2;
drop table tab1;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

show create table tab1;
show create table tab2;

※自動で外部キーにインデックスが作成される。

insert into tab1 values(10,0);
insert into tab1 values(20,0);
insert into tab1 values(30,0);
insert into tab1 values(40,0);
insert into tab1 values(50,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(3,30);

select * from tab1;
select * from tab2;

start transaction;
update tab1 set col2 = 1 where col1 = 30;

start transaction;
update tab2 set col2 = 30 where col1 = 1;
update tab2 set col2 = 40 where col1 = 2;
update tab2 set col2 = 10 where col1 = 3;

rollback;

確認結果:

親で主キー: 30の行の非キーを更新した場合

子で外部キー: 10 → 30 の更新は待ちになる ★(READ-COMMITTEDにしても待ちになる)
子で外部キー: 20 → 40 の更新は可能
子で外部キー: 30 → 10 の更新は可能

• Oracle

(19c)

drop table tab2 purge;
drop table tab1 purge;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

select * from user_indexes where table_name in ('TAB1','TAB2');

insert into tab1 values(10,0);
insert into tab1 values(20,0);
insert into tab1 values(30,0);
insert into tab1 values(40,0);
insert into tab1 values(50,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(3,30);

commit;

select * from tab1;
select * from tab2;

update tab1 set col2 = 1 where col1 = 30;

update tab2 set col2 = 30 where col1 = 1;
update tab2 set col2 = 40 where col1 = 2;
update tab2 set col2 = 10 where col1 = 3;

rollback;

確認結果:

親で主キー: 30の行の非キーを更新した場合

子で外部キー: 10 → 30 の更新は可能
子で外部キー: 20 → 40 の更新は可能
子で外部キー: 30 → 10 の更新は可能

• PostgreSQL

(13)

drop table tab2;
drop table tab1;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

\d+ tab1;
\d+ tab2;

insert into tab1 values(10,0);
insert into tab1 values(20,0);
insert into tab1 values(30,0);
insert into tab1 values(40,0);
insert into tab1 values(50,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(3,30);

select * from tab1;
select * from tab2;

start transaction;
update tab1 set col2 = 1 where col1 = 30;

start transaction;
update tab2 set col2 = 30 where col1 = 1;
update tab2 set col2 = 40 where col1 = 2;
update tab2 set col2 = 10 where col1 = 3;

rollback;

確認結果:

親で主キー: 30の行の非キーを更新した場合

子で外部キー: 10 → 30 の更新は可能
子で外部キー: 20 → 40 の更新は可能
子で外部キー: 30 → 10 の更新は可能

• SQL Server

(2019)

drop table tab2;
drop table tab1;

create table tab1(col1 int not null,col2 int);
create table tab2(col1 int not null,col2 int);

alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);

alter table tab2 add constraint tab2fk foreign key(col2) references tab1(col1);

select object_name(object_id) object_name,*
from sys.indexes
where object_name(object_id) in ('tab1','tab2')
;

insert into tab1 values(10,0);
insert into tab1 values(20,0);
insert into tab1 values(30,0);
insert into tab1 values(40,0);
insert into tab1 values(50,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(3,30);

select * from tab1;
select * from tab2;

begin transaction;
update tab1 set col2 = 1 where col1 = 30;

begin transaction;
update tab2 set col2 = 30 where col1 = 1;
update tab2 set col2 = 40 where col1 = 2;
update tab2 set col2 = 10 where col1 = 3;

rollback;

確認結果:

親で主キー: 30の行の非キーを更新した場合

子で外部キー: 10 → 30 の更新は待ちになる ★(READ COMMITTED SNAPSHOTにしても待ちになる)
子で外部キー: 20 → 40 の更新は可能
子で外部キー: 30 → 10 の更新は可能