path: root/mysql-test/main/foreign_key.test

#
# Test syntax of foreign keys
#

--source include/have_innodb.inc
--source include/have_partition.inc
--source include/lcase_names.inc

--disable_warnings
drop table if exists t1,t2;
--enable_warnings

create table t2 (
  a int unique, c int unique);

create table t3 (
  a int unique, c int unique, d int unique,
  r int references t3(d));

create table t1 (
	a int not null references t2,
	b int not null constraint t2_c references t2 (c),
	primary key (a,b),
	foreign key (a) references t3 match full,
	foreign key (a) references t3 match partial,
	foreign key (a,b) references t3 (c,d) on delete no action
	  on update no action,
	foreign key (a,b) references t3 (c,d) on update cascade,
	foreign key (a,b) references t3 (c,d) on delete set default,
	foreign key (a,b) references t3 (c,d) on update set null);

create index a on t1 (a);
create unique index b on t1 (a,b);
--error ER_ROW_IS_REFERENCED_2
drop tables t2;
--error ER_ROW_IS_REFERENCED_2
drop tables t3;
drop tables t1, t2, t3;

# End of 4.1 tests

#
# Test DELETE IGNORE
# Bug#44987 DELETE IGNORE and FK constraint
#

create table t1 (id int primary key) engine = innodb;
create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb;
insert into t1 values (1), (2), (3), (4), (5), (6);
insert into t2 values (3), (5);

--error 1451
delete from t1;
select * from t1;

delete ignore from t1;
select row_count();
select * from t1;
drop table t2;
drop table t1;

#
# Bug#34455 (Ambiguous foreign keys syntax is accepted)
#

--disable_warnings
drop table if exists t_34455;
--enable_warnings

# 2 match clauses, illegal
--error ER_PARSE_ERROR
create table t_34455 (
  a int not null,
  foreign key (a) references t3 (a) match full match partial);

# match after on delete, illegal
--error ER_PARSE_ERROR
create table t_34455 (
  a int not null,
  foreign key (a) references t3 (a) on delete set default match full);

# match after on update, illegal
--error ER_PARSE_ERROR
create table t_34455 (
  a int not null,
  foreign key (a) references t3 (a) on update set default match full);

# 2 on delete clauses, illegal
--error ER_PARSE_ERROR
create table t_34455 (
  a int not null,
  foreign key (a) references t3 (a)
  on delete set default on delete set default);

# 2 on update clauses, illegal
--error ER_PARSE_ERROR
create table t_34455 (
  a int not null,
  foreign key (a) references t3 (a)
  on update set default on update set default);

create table t_34455 (a int not null);

# 2 match clauses, illegal
--error ER_PARSE_ERROR
alter table t_34455
  add foreign key (a) references t3 (a) match full match partial);

# match after on delete, illegal
--error ER_PARSE_ERROR
alter table t_34455
  add foreign key (a) references t3 (a) on delete set default match full);

# match after on update, illegal
--error ER_PARSE_ERROR
alter table t_34455
  add foreign key (a) references t3 (a) on update set default match full);

# 2 on delete clauses, illegal
--error ER_PARSE_ERROR
alter table t_34455
  add foreign key (a) references t3 (a)
  on delete set default on delete set default);

# 2 on update clauses, illegal
--error ER_PARSE_ERROR
alter table t_34455
  add foreign key (a) references t3 (a)
  on update set default on update set default);

drop table t_34455;

--echo #
--echo # MDEV-18460 Don't allow multiple table CONSTRAINTs with the same name.
--echo #

CREATE TABLE tpk (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL) ENGINE=Innodb;
--error ER_DUP_CONSTRAINT_NAME
CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1), CONSTRAINT sid CHECK (c2>15));

CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1));
--error ER_DUP_CONSTRAINT_NAME
ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15);
DROP TABLE tfk;

CREATE TABLE tfk (c1 INT, c2 INT,
  CONSTRAINT sid FOREIGN KEY (c1) REFERENCES tpk (id)) ENGINE=Innodb;
show create table tfk;
--error ER_DUP_CONSTRAINT_NAME
ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15);
--error ER_DUP_KEYNAME
ALTER TABLE tfk ADD CONSTRAINT sid UNIQUE(c2);
DROP TABLE tfk;

DROP TABLE tpk;

--echo #
--echo # MDEV-16417 Store Foreign Key metadata outside of InnoDB
--echo #
set default_storage_engine= innodb;

--echo # Check create table
create or replace table t1 (id int primary key);
create or replace table t2 (id int primary key, foreign key (id) references t1(id));
select * from t1, t2;
check tables t1, t2;
flush tables t1, t2;
check tables t1, t2;
drop table t2, t1;

create table t1 (id int primary key);
create table t2 (id int references t1(id)) select id from t1;
check tables t1, t2;
flush tables;
check tables t1, t2;
drop table t2, t1;

create database D;
create table D.T1(id int primary key);
create table t2(id int primary key, f1 int references D.T1(id));
select * from D.T1;
--replace_result d.t1 D.T1
check table D.T1;
drop table t2;
--replace_result d.t1 D.T1
check table D.T1;
flush tables;
--replace_result d.t1 D.T1
check table D.T1;
drop table D.T1;
drop database D;

--echo # Check duplicate id
create or replace table t1 (id int primary key) engine innodb;
--error ER_DUP_CONSTRAINT_NAME
create or replace table t2 (id2 int,
  constraint c foreign key (id2) references t1 (id),
  constraint c foreign key (id2) references t1 (id));

create or replace table t2 (id2 int, constraint c foreign key (id2) references t1 (id));
--error ER_DUP_CONSTRAINT_NAME
alter table t2 add constraint c foreign key (id2) references t1 (id);
--error ER_DUP_CONSTRAINT_NAME
create or replace table t3 (id2 int, constraint c foreign key (id2) references t1 (id));
create or replace table t3 (id2 int, constraint C foreign key (id2) references t1 (id));
create or replace table t3 (id2 int);
--error ER_DUP_CONSTRAINT_NAME
alter table t2 add constraint c foreign key (id2) references t1 (id);
alter table t2 add constraint fk_t3 foreign key (id2) references t1 (id);
--error ER_DUP_CONSTRAINT_NAME
alter table t3 add foreign key (id2) references t1 (id);
drop tables t3, t2, t1;

--echo # Check rename column, lock tables
create or replace table t1 (id int primary key);
create or replace table t2 (id int primary key);
create or replace table t3 (id int primary key);
create or replace table ch1 (
  id int, id2 int,
  foreign key (id) references t1 (id),
  foreign key (id2) references t2 (id),
  foreign key (id) references t3 (id));
select * from t1, t2, t3;
--connect con1, localhost, root
lock tables t3 read;
--connection default
set @saved_lock_wait_timeout= @@lock_wait_timeout;
set lock_wait_timeout= 1;
alter table ch1 change id2 xid2 int;
--error ER_LOCK_WAIT_TIMEOUT
alter table ch1 change id xid int;
set lock_wait_timeout= @saved_lock_wait_timeout;
--connection con1
unlock tables;
--disconnect con1
--connection default
alter table ch1 change id xid int;
select * from ch1;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
drop tables ch1, t2, t1, t3;

--echo # Rename column on referenced table
create or replace table t1 (id int primary key);
create or replace table t2 (id int references t1, id2 int references t1(id));
select * from t2;
alter table t1 change id xid int;
show create table t2;
select * from t1;
check table t2;
flush table t2;
check table t2;
alter table t1 rename column xid to yid;
show create table t2;
select * from t1;
check table t2;
flush table t2;
check table t2;
drop tables t2, t1;

--echo # Check rename table
create or replace table t1 (id int primary key);
create or replace table t2 (id int references t1);
select * from t2, t1;
rename table t2 to t3;
create table t2 (x int);
check table t1;
flush tables t1;
check table t1;
drop tables t3, t1, t2;

create or replace table t1 (id int primary key);
create or replace table t2 (id int primary key);
create or replace table t3 (id int primary key);
select * from t1, t2, t3;
create or replace table t4 (
  id int primary key, id2 int references t4(id),
  foreign key (id) references t1 (id),
  foreign key (id2) references t2 (id),
  foreign key (id) references t3 (id));
select * from t4;
rename table t4 to xt4;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
alter table xt4 rename to yt4, algorithm=inplace;
select * from t1, t2, t3;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
alter table yt4 rename to zt4, algorithm=copy;
select * from t1, t2, t3;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
drop tables zt4, t2, t1, t3;

--echo # Rename of referenced table
create or replace table t1 (id int primary key);
create or replace table t2 (id int references t1);
select * from t2;
rename table t1 to xt1;
show create table t2;
select * from xt1;
check tables t2, xt1;
flush tables t2, xt1;
check tables t2, xt1;
alter table xt1 rename to yt1, algorithm=inplace;
show create table t2;
select * from yt1;
check tables t2, yt1;
flush tables t2, yt1;
check tables t2, yt1;
alter table yt1 rename to t1, algorithm=copy;
show create table t2;
select * from t1;
check tables t2, t1;
flush tables t2, t1;
check tables t2, t1;
drop tables t2, t1;

--echo # Multi-rename
create or replace table t1 (x int unique, y int unique, z int unique);
create or replace table t2 (x int references t1(x));
create or replace table t3 (x int references t2(x), y int references t1(y));
create or replace table t4 (x int);
create or replace table fail_t4 (x int);
--error ER_TABLE_EXISTS_ERROR
rename table t1 to xt1, t4 to fail_t4;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
--error ER_TABLE_EXISTS_ERROR
rename table t1 to xt1, t2 to xt2, t3 to xt3, t4 to fail_t4;
check tables t1, t2, t3, xt1, xt2, xt3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
--error ER_TABLE_EXISTS_ERROR
rename table t3 to xt3, t2 to xt2, t1 to xt1, t4 to fail_t4;
check tables t1, t2, t3, xt1, xt2, xt3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
rename table t1 to xt1, t2 to xt2, t3 to xt3;
check tables xt1, xt2, xt3;
flush tables xt1, xt2, xt3;
check tables xt1, xt2, xt3;
rename table xt3 to t3, xt2 to t2, xt1 to t1;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
drop tables fail_t4, t4;
rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
check tables t1, t2, t3;
flush tables t1, t2, t3;
check tables t1, t2, t3;
drop tables t1, t3, t2;

--echo # Check drop table
create or replace table t1 (id int primary key);
create or replace table ch1 (id int, foreign key (id) references t1 (id));
select * from t1;
select * from ch1;
--error ER_ROW_IS_REFERENCED_2
drop table t1;
drop tables ch1, t1;

--echo # Check drop database
create or replace table t1 (id int primary key);
select * from t1;
create or replace database test2;
use test2;
create or replace table ch1 (
  id int, id2 int,
  foreign key (id) references test.t1 (id));
select * from ch1;
use test;
drop database test2;
check tables t1;
flush tables t1;
check tables t1;
drop tables t1;

--echo # Check add foreign key
create or replace table t1(fld1 int not null primary key);
create or replace table t2(fld1 int not null, fld2 int as (fld1) virtual);
insert into t1 values(1);
insert into t2 values(1, default);
set foreign_key_checks= 0;
alter table t2 add index(fld2), add foreign key (fld1) references t1(fld1)
  on update cascade, algorithm=inplace;
set foreign_key_checks= 1;
update t1 set fld1= 2;
select fld2 from t2;
select * from t2;
drop table t2, t1;

--echo # Check drop column, drop foreign key
create or replace table t1 (id int primary key, a int);
create or replace table t2 (id int, a int, foreign key fk (id) references t1 (id));
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
alter table t1 drop id;
--error ER_WRONG_FK_DEF
alter table t2 drop id;
alter table t2 drop foreign key fk;
drop tables t1, t2;

--echo # Check self-references
--error ER_WRONG_FK_DEF
create or replace table t1 (f1 integer primary key references t1(f1));
--error ER_WRONG_FK_DEF
create or replace table t1 (f1 integer primary key, foreign key (f1) references t1(f1));
create or replace table t1 (f1 integer primary key);
--error ER_WRONG_FK_DEF
alter table t1 add constraint c1 foreign key (f1) references t1(f1);

create or replace table t1 (id int primary key, id2 int references t1 (id));
check tables t1;
flush tables t1;
check tables t1;
show create table t1;
--error ER_NO_REFERENCED_ROW_2
insert into t1 values (1, 2);
insert into t1 values (1, 1);
alter table t1 change id2 id3 int;
check tables t1;
flush tables t1;
check tables t1;
show create table t1;
alter table t1 add foreign key (id3) references t1 (id);
check tables t1;
flush tables t1;
check tables t1;
show create table t1;
alter table t1 change id id4 int;
show create table t1;
check tables t1;
flush tables t1;
check tables t1;
show create table t1;
alter table t1 drop foreign key fk_t1;
check tables t1;
flush tables t1;
check tables t1;
show create table t1;
rename table t1 to t2;
check tables t2;
flush tables t2;
check tables t2;
show create table t2;
# TODO: actually this should succeed (it fails in upstream too)
--error ER_ROW_IS_REFERENCED_2
delete from t2;
drop table t2;

set default_storage_engine= default;

--echo # Prohibit wrong references and fix field name case
--error ER_WRONG_FK_DEF
create table t1 (A int unique key, x timestamp references t1(a));
--error ER_WRONG_FK_DEF
create table t1 (A int unique key, x int references t1(b));
create table t1 (A int unique key, x int references t1(a));
--error ER_WRONG_FK_DEF
alter table t1 add foreign key(x) references t1(b);
alter table t1 add foreign key(x) references t1(a);

show create table t1;
--error ER_WRONG_FK_DEF
create table t2 (AA int, x int references t1(ax));
--error ER_WRONG_FK_DEF
create table t2 (AA int, x timestamp references t1(a));

create table t2 (AA int, x int references t1(a));

--error ER_WRONG_FK_DEF
alter table t2 add foreign key(aa) references t1(b);
alter table t2 add foreign key(aa) references t1(a);

show create table t2;
drop tables t2, t1;

--echo # Foreign keys and partitioning
create table t1 (x int primary key) partition by hash(x) partitions 3;
--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
create table t2 (y int references t1(x));
create table t2 (y int);
--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
alter table t2 add foreign key (y) references t1 (x);
drop tables t2, t1;
create table t1 (x int primary key);
--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
create table t2 (y int references t1(x)) partition by hash(x) partitions 3;
create table t2 (y int) partition by hash(y) partitions 3;
--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
alter table t2 add foreign key (y) references t1 (x);
create or replace table t2 (y int references t1(x));
--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
alter table t1 partition by hash(x) partitions 3;
--error ER_FEATURE_NOT_SUPPORTED_WITH_PARTITIONING
alter table t2 partition by hash(y) partitions 3;
drop tables t2, t1;