📄 fk_nonsps.out
字号:
ij> insert into p values (2,2);1 row inserted/updated/deletedij> -- okupdate f set x = x+1, y = y+1;1 row inserted/updated/deletedij> select * from f;X |Y -----------------------2 |2 ij> select * from p;X |Y -----------------------1 |1 2 |2 ij> -- okupdate p set x = x+1, y = y+1;2 rows inserted/updated/deletedij> -- failupdate p set x = x+1, y = y+1;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (2,2). The statement has been rolled back.ij> -- -- BOUNDARY CONDITIONS--delete from f;1 row inserted/updated/deletedij> delete from p;2 rows inserted/updated/deletedij> insert into f select * from f;0 rows inserted/updated/deletedij> delete from p where x = 9999;0 rows inserted/updated/deletedij> update p set x = x+1, y=y+1 where x = 999;0 rows inserted/updated/deletedij> insert into p values (1,1);1 row inserted/updated/deletedij> insert into f values (1,1);1 row inserted/updated/deletedij> update p set x = x+1, y=y+1 where x = 999;0 rows inserted/updated/deletedij> delete from p where x = 9999;0 rows inserted/updated/deletedij> insert into f select * from f;1 row inserted/updated/deletedij> ---- test a CURSOR--delete from f;2 rows inserted/updated/deletedij> delete from p;1 row inserted/updated/deletedij> insert into p values (1,1);1 row inserted/updated/deletedij> insert into f values (1,1);1 row inserted/updated/deletedij> autocommit off;ij> get cursor c as 'select * from p for update of x';ij> next c;X |Y -----------------------1 |1 ij> -- failupdate p set x = 666 where current of c;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1). The statement has been rolled back.ij> close c;ij> get cursor c as 'select * from f for update of x';ij> next c;X |Y -----------------------1 |1 ij> -- failupdate f set x = 666 where current of c;ERROR 23503: UPDATE on table 'F' caused a violation of foreign key constraint 'FK' for key (666,1). The statement has been rolled back.ij> close c;ij> commit;ij> autocommit on;ij> delete from f;1 row inserted/updated/deletedij> delete from p;1 row inserted/updated/deletedij> insert into p values (0,0), (1,1), (2,2), (3,3), (4,4);5 rows inserted/updated/deletedij> insert into f values (1,1);1 row inserted/updated/deletedij> -- lets add some additional foreign keys to the mixcreate table f2 (x int, y int, constraint fk2 foreign key (x,y) references p);0 rows inserted/updated/deletedij> insert into f2 values (2,2);1 row inserted/updated/deletedij> create table f3 (x int, y int, constraint fk3 foreign key (x,y) references p);0 rows inserted/updated/deletedij> insert into f3 values (3,3);1 row inserted/updated/deletedij> -- okupdate p set x = x+1, y = y+1;5 rows inserted/updated/deletedij> -- error, fk1update p set x = x+1;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1). The statement has been rolled back.ij> update p set y = y+1;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1). The statement has been rolled back.ij> update p set x = x+1, y = y+1;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1). The statement has been rolled back.ij> -- fail of fk3update p set y = 666 where y = 3;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK3' for key (3,3). The statement has been rolled back.ij> -- fail of fk2update p set x = 666 where x = 2;ERROR 23503: UPDATE on table 'P' caused a violation of foreign key constraint 'FK2' for key (2,2). The statement has been rolled back.ij> -- cleanupdrop table f;0 rows inserted/updated/deletedij> drop table f2;0 rows inserted/updated/deletedij> drop table f3;0 rows inserted/updated/deletedij> drop table p;0 rows inserted/updated/deletedij> CALL WAIT_FOR_POST_COMMIT();0 rows inserted/updated/deletedij> ---- SELF REFERENCING--create table s (x int not null primary key, y int references s, z int references s);0 rows inserted/updated/deletedij> -- okinsert into s values (1,null,null);1 row inserted/updated/deletedij> -- okupdate s set y = 1;1 row inserted/updated/deletedij> -- failupdate s set z = 2;ERROR 23503: UPDATE on table 'S' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (2). The statement has been rolled back.ij> -- okupdate s set z = 1;1 row inserted/updated/deletedij> -- okinsert into s values (2, 1, 1);1 row inserted/updated/deletedij> -- okupdate s set x = 666 where x = 2;1 row inserted/updated/deletedij> -- okupdate s set x = x+1, y = y+1, z = z+1;2 rows inserted/updated/deletedij> delete from s;2 rows inserted/updated/deletedij> -- okinsert into s values (1,null,null);1 row inserted/updated/deletedij> -- okinsert into s values (2,null,null);1 row inserted/updated/deletedij> -- okupdate s set y = 2 where x = 1;1 row inserted/updated/deletedij> -- okupdate s set z = 1 where x = 2;1 row inserted/updated/deletedij> select * from s;X |Y |Z -----------------------------------1 |2 |NULL 2 |NULL |1 ij> -- fail update s set x = 0 where x = 1;ERROR 23503: UPDATE on table 'S' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> ---- Now we are going to do a short but sweet-- check to make sure we are actually hitting-- the correct columns--create table p (c1 char(1), y int not null, c2 char(1), x int not null, constraint pk primary key (x,y));0 rows inserted/updated/deletedij> create table f (x int, s smallint, y int, constraint fk foreign key (x,y) references p);0 rows inserted/updated/deletedij> insert into p values ('1',1,'1',1);1 row inserted/updated/deletedij> -- okinsert into f values (1,1,1);1 row inserted/updated/deletedij> insert into p values ('0',0,'0',0);1 row inserted/updated/deletedij> -- okupdate p set x = x+1, y=y+1;2 rows inserted/updated/deletedij> -- faildelete from p where y = 1;ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1). The statement has been rolled back.ij> -- failinsert into f values (1,1,4);ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (1,4). The statement has been rolled back.ij> delete from f;1 row inserted/updated/deletedij> delete from p;2 rows inserted/updated/deletedij> ---- Lets make sure we don't interact poorly with-- 'normal' deferred dmlinsert into p values ('1',1,'1',1);1 row inserted/updated/deletedij> insert into f values (1,1,1);1 row inserted/updated/deletedij> insert into p values ('0',0,'0',0);1 row inserted/updated/deletedij> -- okupdate p set x = x+1, y=y+1 where x < (select max(x)+10000 from p);2 rows inserted/updated/deletedij> -- faildelete from p where y = 1 and y in (select y from p);ERROR 23503: DELETE on table 'P' caused a violation of foreign key constraint 'FK' for key (1,1). The statement has been rolled back.ij> -- insertscreate table f2 (x int, t smallint, y int);0 rows inserted/updated/deletedij> insert into f2 values (1,1,4);1 row inserted/updated/deletedij> -- failinsert into f select * from f2;ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'FK' for key (1,4). The statement has been rolled back.ij> -- okinsert into f2 values (1,1,1);1 row inserted/updated/deletedij> insert into f select * from f2 where y = 1;1 row inserted/updated/deletedij> drop table f2;0 rows inserted/updated/deletedij> drop table f;0 rows inserted/updated/deletedij> drop table p;0 rows inserted/updated/deletedij> CALL WAIT_FOR_POST_COMMIT();0 rows inserted/updated/deletedij> ---- PREPARED STATEMENTS--drop table f;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'F' because it does not exist.ij> drop table p;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'P' because it does not exist.ij> --the reason for this wait call is to wait unitil system tables row deletes--are completed other wise we will get different order fk checks--that will lead different error messages depending on when post commit thread runsCALL WAIT_FOR_POST_COMMIT();0 rows inserted/updated/deletedij> prepare s as 'create table p (w int not null primary key, x int references p, y int not null, z int not null, constraint uyz unique (y,z))';ij> execute s;0 rows inserted/updated/deletedij> remove s;ij> prepare s as 'create table f (w int references p, x int, y int, z int, constraint fk foreign key (y,z) references p (y,z))';ij> execute s;0 rows inserted/updated/deletedij> remove s;ij> prepare s as 'alter table f drop constraint fk';ij> execute s;0 rows inserted/updated/deletedij> remove s;ij> --the reason for this wait call is to wait unitil system tables row deletes--are completed other wise we will get different order fk checksCALL WAIT_FOR_POST_COMMIT();0 rows inserted/updated/deletedij> prepare s as 'alter table f add constraint fk foreign key (y,z) references p (y,z)';ij> execute s;0 rows inserted/updated/deletedij> remove s;ij> prepare sf as 'insert into f values (1,1,1,1)';ij> prepare sp as 'insert into p values (1,1,1,1)';ij> -- failexecute sf;ERROR 23503: INSERT on table 'F' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> -- okexecute sp;1 row inserted/updated/deletedij> execute sf;1 row inserted/updated/deletedij> insert into p values (2,2,2,2);1 row inserted/updated/deletedij> remove sf;ij> prepare sf as 'update f set w=w+1, x = x+1, y=y+1, z=z+1';ij> -- okexecute sf;1 row inserted/updated/deletedij> remove sp;ij> prepare sp as 'update p set w=w+1, x = x+1, y=y+1, z=z+1';ij> -- okexecute sp;2 rows inserted/updated/deletedij> remove sp;ij> prepare sp as 'delete from p where x =1';ij> -- okexecute sp;0 rows inserted/updated/deletedij> remove sp;ij> remove sf;ij> drop table f;0 rows inserted/updated/deletedij> drop table p;0 rows inserted/updated/deletedij> CALL WAIT_FOR_POST_COMMIT();0 rows inserted/updated/deletedij> drop procedure WAIT_FOR_POST_COMMIT;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -