-- source include/have_ndb.inc
-- source suite/ndb/include/backup_restore_setup.inc

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

# BUG 14095785 - DROPPING FK WITHOUT LABEL CAUSED MYSQLD TO CRASH
-- echo # bug#14095785

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a),
  unique key using hash (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

alter table t2
  add constraint foreign key (b) references t1 (b);

-- error ER_PARSE_ERROR
alter table t2
  drop foreign key;

drop table t2, t1;

# BUG 14095838 - CHANGING PK CAUSES CASCADE FK TO DELETE ROWS
-- echo # bug#14095838

create table t1 (
  a int not null,
  primary key using hash (a)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

-- error ER_CANNOT_ADD_FOREIGN
alter table t2
  add constraint fk1 foreign key (b) references t1 (a)
  on delete cascade on update cascade;
show warnings;

alter table t2
  add constraint fk1 foreign key (b) references t1 (a)
  on delete cascade on update restrict;

drop table t2, t1;

# Bug #14195170  	FAILING CREATE TABLE WITH FK CONSTRAINTS LEAVES ORPHANED .FRM
--echo # bug#14195170

CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=NDB;

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL, PRIMARY KEY(category, id)) ENGINE=NDB;

--error ER_CANNOT_ADD_FOREIGN
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
show warnings;

--error ER_CANNOT_ADD_FOREIGN
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
show warnings;

DROP TABLE customer, product;

# Bug #14124623 - CREATING TABLE WITH FK CONSTRAINT FAILS AND LEAVES DB CORRUPT
--echo # bug#14124623

CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY,
country VARCHAR(30), INDEX county_country_index(country)) ENGINE=ndb;

INSERT INTO counties VALUES
('Berkshire','England'),('Shropshire','England'),('Oxfordshire','England'),('Buckinghamshire','England');

CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county
VARCHAR(30), INDEX county_county_index (county), CONSTRAINT county_town
FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE
RESTRICT) ENGINE=ndb;

INSERT INTO towns VALUES
('Maidenhead','Berkshire'),('Reading','Berkshire'),('Shrewsbury','Shropshire')
,('Oxford','Oxfordshire');

--error ER_FK_NO_INDEX_PARENT
CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
RESTRICT ON UPDATE RESTRICT) engine=ndb;

--error ER_FK_NO_INDEX_PARENT
CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
RESTRICT ON UPDATE RESTRICT) engine=ndb;

CREATE TABLE languages (id INT NOT NULL PRIMARY KEY) engine=ndb;

drop table languages, towns, counties;

--echo # test: child vs parent column types

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a),
  unique key using hash (b)
) engine ndb;

--error ER_FK_INCOMPATIBLE_COLUMNS
create table t2 (
  a int not null,
  b char(20) not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

create table t2 (
  a int not null,
  b char(10) not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_INCOMPATIBLE_COLUMNS
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2, t1;

--echo # test: parent table not exist

--error ER_FK_CANNOT_OPEN_PARENT
create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_CANNOT_OPEN_PARENT
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2;

--echo # test: parent column not exist

create table t1 (
  a int not null,
  primary key using hash (a)
) engine ndb;

--error ER_FK_NO_COLUMN_PARENT
create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_NO_COLUMN_PARENT
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2, t1;

--echo # test: parent key no index

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a)
) engine ndb;

--error ER_FK_NO_INDEX_PARENT
create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_NO_INDEX_PARENT
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2, t1;

--echo # test: parent key no unique index

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_NO_INDEX_PARENT
create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_NO_INDEX_PARENT
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2, t1;

--echo # test: child column not exist

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a),
  unique key using hash (b)
) engine ndb;

--error ER_KEY_COLUMN_DOES_NOT_EXITS
create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (c) references t1 (b)
) engine ndb;
show warnings;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_KEY_COLUMN_DOES_NOT_EXITS
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (c) references t1 (b);
show warnings;

drop table t2, t1;

--echo # test: child column no index
--echo # no error - server creates KEY

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a),
  unique key using hash (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

drop table t2;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a)
) engine ndb;

alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2, t1;

--echo # test: child vs parent column count

create table t1 (
  a int not null,
  b int not null,
  c int not null,
  primary key using hash (a),
  unique key using hash (b, c)
) engine ndb;

--error ER_FK_NO_INDEX_PARENT
create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (b)
) engine ndb;

--error ER_FK_NO_INDEX_PARENT
alter table t2 algorithm=inplace,
  add constraint fk1 foreign key (b) references t1 (b);

drop table t2, t1;

# Bug 16246874 - MYSQLD CRASH WHEN QUERYING KEY_COLUMN_USAGE IF NDB FOREIGN KEYS DEFINED
--echo # bug#16246874
create table t1 (
  id int primary key
) engine=ndb;

create table t2 (
  id int,
  foreign key(id) references t1(id)
) engine=ndb;

# Run failing query, discard result since it will vary
--disable_result_log
select * from information_schema.key_column_usage;
--enable_result_log

# Show that t2 references test.t1.id
select REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
  from information_schema.key_column_usage
    where TABLE_SCHEMA = 'test' and TABLE_NAME = 't2';

drop table t2, t1;

# Bug#16275509 IMPLEMENT HA_NDBCLUSTER::CAN_SWITCH_ENGINES

--echo # bug#16275509 - case: alter engine from ndb

create table t1 (
  a int not null,
  primary key using hash (a)
) engine=ndb;

create table t2 (
  a int not null,
  primary key using hash (a),
  foreign key fk1 (a) references t1 (a)
) engine=ndb;

--error ER_FK_CANNOT_CHANGE_ENGINE
alter table t1 engine=innodb;

--error ER_FK_CANNOT_CHANGE_ENGINE
alter table t2 engine=innodb;

drop table t2, t1;

create table t1 (
  a int primary key,
  b int,
  key (b),
  foreign key fk1 (b) references t1 (a)
) engine=ndb;

--error ER_FK_CANNOT_CHANGE_ENGINE
alter table t1 engine=innodb;

drop table t1;

# unlike innodb
--echo # bug#16275509 - case: allow FKs on partitioned

set new=on;

# partition parent

create table t1 (
  a int primary key
) engine=ndb
  partition by hash (a) partitions 5;

create table t2 (
  a int primary key,
  foreign key fk1 (a) references t1 (a)
) engine=ndb;

drop table t2, t1;

create table t1 (
  a int primary key
) engine=ndb;

create table t2 (
  a int primary key,
  foreign key fk1 (a) references t1 (a)
) engine=ndb;

alter table t1
  partition by hash (a) partitions 5;

drop table t2, t1;

# partition child

create table t1 (
  a int primary key
) engine=ndb;

create table t2 (
  a int primary key,
  foreign key fk1 (a) references t1 (a)
) engine=ndb
  partition by hash (a) partitions 5;

drop table t2;

create table t2 (
  a int primary key,
  foreign key fk1 (a) references t1 (a)
) engine=ndb;

alter table t2
  partition by hash (a) partitions 5;

drop table t2, t1;

# Bug#16275684 - IMPLEMENT HA_NDBCLUSTER::GET_PARENT_FOREIGN_KEY_LIST

# truncate of NDB table does not hit get_parent_foreign_key_list().
# alter child FK to not null is tested in ndb_fk_alter.test.

--echo # bug#16275684 - case: alter FK parent column

create table t1 (
  a int primary key,
  b enum('a','b','c') not null,
  unique key (b)
) engine=ndb;

create table t2 (
  a int primary key,
  b enum('a','b','c') not null,
  key (b),
  constraint fk1 foreign key (b) references t1 (b)
) engine=ndb;

insert into t1 values (1,'a'),(2,'b');
insert into t2 values (1,'a'),(2,'a'),(3,'b');

--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
alter table t1 modify column b enum('a','b') not null;
alter table t2 drop foreign key fk1;
alter table t1 modify column b enum('a','b') not null;
--disable_warnings
show create table t1;
--enable_warnings

select * from t1 order by a;
select * from t2 order by a;

drop table t2, t1;

# Bug#16286164 - DROP/ALTER TABLE NOT PROTECTED FROM BREAKING NDB FK CONSTRAINTS

--echo # bug#16286164 - case: drop when referenced by different table
let $i=2;
while ($i)
{
	eval set @i=$i;
	let $engine=`select if(@i=1,'ndb','innodb')`;

	eval create table t1 (
  	     a int not null,
	     primary key using hash (a)
	) engine=$engine;

	eval create table t2 (
	     a int not null,
	     b int,
	     primary key using hash (a),
	     key (b),
	     constraint fk1 foreign key (b) references t1 (a)
	) engine=$engine;

	--error ER_FK_CANNOT_DROP_PARENT
	drop table t1;

	drop table t2, t1;
	dec $i;
}

--echo # bug#16286164 - case: drop when referenced by same table
let $i=2;
while ($i)
{
	eval set @i=$i;
	let $engine=`select if(@i=1,'ndb','innodb')`;

	eval create table t1 (
	     a int not null,
	     b int,
	     primary key using hash (a),
	     key (b),
	     constraint fk1 foreign key (b) references t1 (a)
	) engine=$engine;

	drop table t1;

	dec $i;
}

-- echo # BugXXZ - multi part unique index parent

create table t1 (
  a int not null,
  b int not null,
  primary key using hash (a),
  unique key using hash (a,b)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  primary key using hash (a),
  key (a,b),
  foreign key fk1 (a,b) references t1 (a,b)
) engine ndb;

drop table t2, t1;

--echo # child-part-key

create table t1 (
  a int not null,
  primary key using hash (a)
) engine ndb;

insert into t1 values (1),(2);

create table t2 (
  a int not null,
  b int not null,
  c int not null,
  primary key using hash (a),
  key xbc (b,c),
  constraint fk1 foreign key (b) references t1 (a)
) engine ndb;

# uses index xbc
--disable_warnings
show create table t2;
--enable_warnings

insert into t2 (a,b,c) values (1,1,11),(2,2,21),(3,2,22);
--error ER_NO_REFERENCED_ROW_2
insert into t2 (a,b,c) values (4,3,33);
--error ER_ROW_IS_REFERENCED_2
delete from t1 where a = 1;

drop table t2;

create table t2 (
  a int not null,
  b int not null,
  c int not null,
  primary key using hash (a),
  key xbc (b,c),
  constraint fk1 foreign key (c) references t1 (a)
) engine ndb;

# creates index fk1 (c)
--disable_warnings
show create table t2;
--enable_warnings

insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
--error ER_NO_REFERENCED_ROW_2
insert into t2 (a,c,b) values (4,3,33);
--error ER_ROW_IS_REFERENCED_2
delete from t1 where a = 1;

drop table t2;

# partial hash key does not work
# XXX maybe should create index fk1 (b)
--error ER_FK_NO_INDEX_CHILD
create table t2 (
  a int not null,
  b int not null,
  c int not null,
  primary key using hash (a),
  unique key xbc (b,c) using hash,
  constraint fk1 foreign key (b) references t1 (a)
) engine ndb;

create table t2 (
  a int not null,
  b int not null,
  c int not null,
  primary key using hash (a),
  unique key xbc (b,c) using hash,
  constraint fk1 foreign key (c) references t1 (a)
) engine ndb;

# creates index fk1 (c)
--disable_warnings
show create table t2;
--enable_warnings

insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
--error ER_NO_REFERENCED_ROW_2
insert into t2 (a,c,b) values (4,3,33);
--error ER_ROW_IS_REFERENCED_2
delete from t1 where a = 1;

drop table t2, t1;

#
# failed create table pollutes dict cache
#
create table t1_parent (pk1 char(255) primary key) charset=latin1, engine = ndb;

--error ER_CANT_CREATE_TABLE
create table t1 (
  c50 char(255) column_format fixed, c49 char(255) column_format fixed, c48 char(255) column_format fixed, c47 char(255) column_format fixed, c46 char(255) column_format fixed,
  c45 char(255) column_format fixed, c44 char(255) column_format fixed, c43 char(255) column_format fixed, c42 char(255) column_format fixed, c41 char(255) column_format fixed,
  c40 char(255) column_format fixed, c39 char(255) column_format fixed, c38 char(255) column_format fixed, c37 char(255) column_format fixed, c36 char(255) column_format fixed,
  c35 char(255) column_format fixed, c34 char(255) column_format fixed, c33 char(255) column_format fixed, c32 char(255) column_format fixed, c31 char(255) column_format fixed,
  c30 char(255) column_format fixed, c29 char(255) column_format fixed, c28 char(255) column_format fixed, c27 char(255) column_format fixed, c26 char(255) column_format fixed,
  c25 char(255) column_format fixed, c24 char(255) column_format fixed, c23 char(255) column_format fixed, c22 char(255) column_format fixed, c21 char(255) column_format fixed,
  c20 char(255) column_format fixed, c19 char(255) column_format fixed, c18 char(255) column_format fixed, c17 char(255) column_format fixed, c16 char(255) column_format fixed,
  c15 char(255) column_format fixed, c14 char(255) column_format fixed, c13 char(255) column_format fixed, c12 char(255) column_format fixed, c11 char(255) column_format fixed,
  c10 char(255) column_format fixed, c9 char(255) column_format fixed, c8 char(255) column_format fixed, c7 char(255) column_format fixed, c6 char(255) column_format fixed,
  c5 char(255) column_format fixed, c4 char(255) column_format fixed, c3 char(255) column_format fixed, c2 char(255) column_format fixed, c1 char(255) column_format fixed,
  primary key using hash(c1)
  ,foreign key(c1) references t1_parent(pk1)
) charset=latin1, engine=ndb;
SHOW WARNINGS;

# Fails here with "Table 'test.t1' doesn't exist"
create table t1(a int primary key) engine = ndb;
drop table t1, t1_parent;

--echo # bug#17591531 - ERROR ON DROP INDEX WHEN REFERENCING FOREIGN KEYS

create table t1 (
  a int not null,
  b int not null,
  unique key t1x1 (b) using hash,
  primary key using hash (a)
) engine=ndb;

create table t2 (
  a int not null,
  b int not null,
  c int not null,
  key (c),
  key t2x1 (b),
  primary key using hash (a),
  constraint t2fk1 foreign key (b) references t1 (b)
) engine=ndb;

--echo # try to drop indexes
--error ER_DROP_INDEX_FK
alter table t1 drop index t1x1;
--error ER_DROP_INDEX_FK
alter table t2 drop index t2x1;

insert into t1 values (1,10),(2,20),(3,30);
insert into t2 values (1,10,100),(2,20,200),(3,30,300);
--error ER_NO_REFERENCED_ROW_2
insert into t2 values (4,40,400);
--error ER_ROW_IS_REFERENCED_2
delete from t1 where a=1;

drop table t2, t1;

--echo # bug#17232212 - NDB DROP NON-EXISTENT FK SUCCEEDS
# succeeds silently before fixes

create table t1 (
  a int primary key
) engine=ndb;

--echo # algorithm=inplace
# innodb gives error 1091

create table t2 (
  a int primary key,
  b int,
  c int,
  key xb (b),
  key xc (c),
  constraint fkb foreign key (b) references t1 (a),
  constraint fkc foreign key (c) references t1 (a)
) engine=ndb;

--error ER_CANT_DROP_FIELD_OR_KEY
alter table t2
  drop foreign key nosuchfk,
  algorithm=inplace;

--disable_warnings
show create table t2;
--enable_warnings

--error ER_CANT_DROP_FIELD_OR_KEY
alter table t2
  drop foreign key fkb,
  drop foreign key nosuchfk,
  drop foreign key fkc,
  algorithm=inplace;

--disable_warnings
show create table t2;
--enable_warnings

drop table t2;

--echo # algorithm=copy
# innodb gives non-sense errors 1025 / 152

create table t2 (
  a int primary key,
  b int,
  c int,
  key xb (b),
  key xc (c),
  constraint fkb foreign key (b) references t1 (a),
  constraint fkc foreign key (c) references t1 (a)
) engine=ndb;

--error ER_CANT_DROP_FIELD_OR_KEY
alter table t2
  drop foreign key nosuchfk,
  algorithm=copy;

--disable_warnings
show create table t2;
--enable_warnings

--error ER_CANT_DROP_FIELD_OR_KEY
alter table t2
  drop foreign key fkb,
  drop foreign key nosuchfk,
  drop foreign key fkc,
  algorithm=copy;

--disable_warnings
show create table t2;
--enable_warnings

drop table t2, t1;

--echo # bug#18662582 - NDB_FK_DISABLE.TEST FAILS TO DROP FOREIGN KEY

create table t1 (
  a int, primary key using hash (a)
) engine = ndb;

create table t2 (
  a int, primary key using hash (a)
) engine = ndb;

alter table t2 add constraint fkname2 foreign key (a) references t1 (a);

create table t3 (
  a int, primary key using hash (a)
) engine = ndb;

alter table t3 add constraint fkname3 foreign key (a) references t2 (a);

--error ER_CANT_DROP_FIELD_OR_KEY
alter table t2 algorithm=inplace, drop foreign key nosuchfk;

alter table t2 algorithm=inplace, drop foreign key fkname2;

alter table t3 algorithm=inplace, drop foreign key fkname3;

drop table t1, t2, t3;

create table t1 (
  a int, primary key using hash (a)
) engine = ndb;

create table t2 (
  a int, primary key using hash (a)
) engine = ndb;

alter table t2 add constraint fkname2 foreign key (a) references t1 (a);

create table t3 (
  a int, primary key using hash (a)
) engine = ndb;

alter table t3 add constraint fkname3 foreign key (a) references t2 (a);

--error ER_CANT_DROP_FIELD_OR_KEY
alter table t2 algorithm=copy, drop foreign key nosuchfk;

alter table t2 algorithm=copy, drop foreign key fkname2;

alter table t3 algorithm=copy, drop foreign key fkname3;

drop table t1, t2, t3;

--echo # bug#18824753 - NDB_RESTORE FAILS WITH FKS WITH SAME NAME

create table t1 (a int primary key) engine=ndb;
create table t2 (a int primary key) engine=ndb;
create table t3 (a int primary key) engine=ndb;
create table t4 (a int primary key) engine=ndb;

alter table t2
  add constraint myfk2 foreign key (a) references t1 (a);
alter table t4
  add constraint myfk4 foreign key (a) references t3 (a);

--error ER_CANNOT_ADD_FOREIGN
alter table t4
  add constraint `11/22/myfk4` foreign key (a) references t3 (a);
show warnings;

--echo # run backup
--source include/ndb_backup.inc

drop table t2, t1;
drop table t4, t3;

--echo # run restore meta
--exec $NDB_RESTORE -b $the_backup_id -n 1 -m $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT

--disable_warnings
show create table t2;
show create table t4;
--enable_warnings

drop table t2, t1;
drop table t4, t3;
--source suite/ndb/include/backup_restore_cleanup.inc

--echo # bug18825966 - ADDING FK TO TABLE WITH DATA GIVES INTERNAL ERROR 208

create table t1 (
  c0 int,
  c1 int,
  primary key using hash (c1)
) engine=ndb;

create table t2 (
  c0 int,
  primary key using hash (c0)
) engine=ndb;

insert into t1 (c1) values (1);
insert into t2 (c0) values (1);

alter table t2 add constraint foreign key fk (c0) references t1 (c1);

drop table t2, t1;

create table t1 (
  c0 int,
  c1 int,
  c2 int,
  c3 int,
  c4 int,
  c5 int,
  primary key (c2,c5)
) engine=ndb;

create table t2 (
  c0 int,
  c1 int,
  c2 int,
  c3 int,
  c4 int,
  c5 int,
  primary key (c1,c4)
) engine=ndb;

insert into t1 (c2,c5) values (1,10),(2,20),(3,30);
insert into t2 (c1,c4) values (1,10),(2,20),(3,30);

alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);

--error ER_NO_REFERENCED_ROW_2
insert into t2 (c1,c4) values (4,40);

drop table t2, t1;

create table t1 (
  c0 int,
  c1 int,
  c2 int not null,
  c3 int,
  c4 int,
  c5 int not null,
  primary key (c0),
  unique key using hash (c2,c5)
) engine=ndb;

create table t2 (
  c0 int,
  c1 int,
  c2 int,
  c3 int,
  c4 int,
  c5 int,
  primary key (c1,c4)
) engine=ndb;

insert into t1 (c0,c2,c5) values (1,1,10),(2,2,20),(3,3,30);
insert into t2 (c1,c4) values (1,10),(2,20),(3,30);

alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);

--error ER_NO_REFERENCED_ROW_2
insert into t2 (c1,c4) values (4,40);

drop table t2, t1;

--echo #
--echo # BUG#21308928 :  CANNOT ADD UNIQUE CONSTRAINT ON SET OF FIELDS WITH EXISTING FOREIGN KEY
--echo #

--echo #Create the tables
create table t1 (
  a int not null primary key
) engine ndb;

create table t2 (
  a int primary key,
  b int not null,
  c int,
  constraint fk1 foreign key (a) references t1(a),
  constraint fk2 foreign key (b) references t1(a),
  constraint fk3 foreign key (c) references t1(a)
) engine ndb;

--echo #Add a unique constraint to the foreign key column
alter table t2 add constraint uniquet2a unique (a);
alter table t2 add constraint uniquet2b unique (b);
alter table t2 add constraint uniquet2c unique (c);

--disable_warnings
show create table t2;
--enable_warnings

--echo #Drop tables
drop table t2, t1;

--echo #
--echo # Bug#27930382 : FOREIGN KEY CONSTRAINT VIOLATIONS INCORRECTLY REPORTED
--echo

# Foreign key constraint which self referrence its own table,
# incorrectly reported constraint violation. Root cause was
# that the key constraints were checked before the (unique) indexes
# had been updated:
#
# (Test case based on the API test 'testFK')


create table t1 (
  kol1 int primary key,
  kol2 int not null,
  kol3 int not null,
  kol4 int not null,
  kol5 int not null,
  unique key uq1(kol2),
  foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
) engine = ndb;

insert into t1 values (1,1,1,1,1);
delete from t1 where kol1=1;
drop table t1;

create table t1 (
  kol1 int primary key,
  kol2 int not null,
  kol3 int not null,
  kol4 int not null,
  kol5 int not null,
  foreign key fk1(kol3) references t1(kol1) on update restrict on delete restrict
) engine = ndb;

insert into t1 values (1,1,1,1,1);
delete from t1 where kol1=1;
drop table t1;

#
# There were also a race condition due to local backup replicas
# being read when verifying that there were no references to
# a deleted row. This allowed the read to race the delete of the
# index row, such that the deleted row appered to still being referred.
# FIX: Always read primary replicas when verifying a constraint.
create table t1 (
  kol1 int primary key,
  kol2 int not null,
  kol3 int not null,
  kol4 int not null,
  kol5 int not null,
  unique key uq1(kol2),
  unique key uq2(kol3),
  foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
) engine = ndb;

insert into t1 values (1,1,1,1,1);
delete from t1 where kol1=1;

insert into t1 values (2,1,1,1,1);
delete from t1 where kol1=2;

insert into t1 values (3,1,1,1,1);
delete from t1 where kol1=3;

insert into t1 values (4,1,1,1,1);
delete from t1 where kol1=4;


insert into t1 values (1,0,0,0,0);
insert into t1 values (2,1,1,1,1);
insert into t1 values (3,2,2,2,2);
insert into t1 values (4,3,3,3,3);
delete from t1;

drop table t1;
--remove_file $NDB_TOOLS_OUTPUT