--source include/have_ndb.inc

--echo #
--echo # This file contains basic tests of the SQL layer handling of
--echo # foreign keys in the NDB storage engine.
--echo #

--echo # A non-unique index cannot serve as parent index.
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
                      INDEX ix(i),
                      FOREIGN KEY (j) REFERENCES self_ref(i)) ENGINE=NDB;

--echo # But a unique index is fine.
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
                      UNIQUE INDEX ix(i),
                      CONSTRAINT fk_j_unique_i FOREIGN KEY (j)
                        REFERENCES self_ref(i)
) ENGINE=NDB;
SHOW CREATE TABLE self_ref;
DROP TABLE self_ref;

--echo # A self referencing FK can use the supporting index as parent index
--echo # if it is unique.
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT,
                      UNIQUE INDEX ix(i),
                      CONSTRAINT fk_i_unique_i FOREIGN KEY (i)
                        REFERENCES self_ref(i)
) ENGINE=NDB;
SHOW CREATE TABLE self_ref;
DROP TABLE self_ref;

--echo # But not if it's not unique.
--error ER_FK_NO_INDEX_PARENT
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT,
                      INDEX ix(i),
                      FOREIGN KEY (i) REFERENCES self_ref(i)) ENGINE=NDB;

--echo # If there is an index with the same columns as the referenced ones,
--echo # but in different order, the index may be used as parent index. This
--echo # index may also serve as supporting index.
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
                      UNIQUE INDEX ijx(i, j),
                      CONSTRAINT fk_ij_unique_ji FOREIGN KEY (i, j)
                        REFERENCES self_ref(j, i)
) ENGINE=NDB;
SHOW CREATE TABLE self_ref;

CREATE TABLE self_ref_hash(pk INT PRIMARY KEY, i INT, j INT,
                           UNIQUE INDEX ijx(i, j) USING HASH,
                           CONSTRAINT fk_ij_unique_hash_ji FOREIGN KEY (i, j)
                             REFERENCES self_ref_hash(j, i)
) ENGINE=NDB;
SHOW CREATE TABLE self_ref_hash;

CREATE TABLE self_ref_btree(pk INT PRIMARY KEY, i INT, j INT,
                            UNIQUE INDEX ijx(i, j) USING BTREE,
                            CONSTRAINT fk_ij_unique_btree_ji FOREIGN KEY (i, j)
                              REFERENCES self_ref_btree(j, i)
) ENGINE=NDB;
SHOW CREATE TABLE self_ref_btree;
DROP TABLES self_ref, self_ref_hash, self_ref_btree;

--echo # If there is an index with the same columns as the FK, but in
--echo # different order, a supporting key will be created.
CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
                      UNIQUE INDEX ijx(j, i),
                      CONSTRAINT fk_ij_diff_order_ji FOREIGN KEY (i, j)
                        REFERENCES self_ref(j, i)
) ENGINE=NDB;
SHOW CREATE TABLE self_ref;
DROP TABLE self_ref;


--echo #
--echo # Additional coverage for bug#25722927 "NEWDD FK: ALTER TABLE CHANGE
--echo # COLUMN TYPE SHOULD CHECK FK CONSTRAINT".
--echo #
--echo # Test how foreign key column compatibility checks work for NDB SE.
--echo #

SET @save_storage_engine= @@default_storage_engine;
SET default_storage_engine=ndbcluster;

--echo #
--echo # 1) Some basic tests that compatibility checks are performed during
--echo #    various foreign key operations.
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=0;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE child (fk CHAR(10));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
DROP TABLE child;

CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk CHAR(10);
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE parent MODIFY pk CHAR(10);
DROP TABLES child, parent;

--echo #
--echo # 2) Test how compatibility rules work for various types.
--echo #    Different engines have different rules, so the below
--echo #    part of test is specific to NDB.
--echo #
--echo # We don't check compatibility for BLOB based and BIT types as
--echo # they are not supported in foreign keys by NDB. Also we don't
--echo # provide coverage for legacy types.

--echo #
--echo # 2.a) For integer types both exact type and signedness should match.
CREATE TABLE parent (pk INT PRIMARY KEY);
--echo # Foreign keys over integer types are supported.
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BIGINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk INT UNSIGNED;
CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
ALTER TABLE child MODIFY fk INT;
DROP TABLE child, parent;
--echo # Integer types are not compatible with other types.
CREATE TABLE parent (pk INT PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.b) For floating point types only the exact type matters.
CREATE TABLE parent (pk DOUBLE PRIMARY KEY);
--echo # Though using such types in foreign key is EXTREMELY bad idea they
--echo # are supported.
CREATE TABLE child (fk DOUBLE, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Signedness doesn't matter.
CREATE TABLE child (fk DOUBLE UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
--echo # Floating point types are not compatible with other types.
CREATE TABLE parent (pk FLOAT PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.c) DECIMAL type is compatible with other DECIMAL columns with
--echo #      the same signedness, precision and scale.
--echo #          
CREATE TABLE parent (pk DECIMAL(8,2) PRIMARY KEY);
--echo # Foreign keys over DECIMAL columns are supported.
CREATE TABLE child (fk DECIMAL(8,2), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Signedness, precision and scale do matter.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,2) UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(7,2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(6,1), FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # DECIMAL types are not compatible with other types.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.d) Unlike InnoDB, NDB considers string types compatible if
--echo #      exact type, length and charset match.
CREATE TABLE parent (pk CHAR(10) PRIMARY KEY);
--echo # Foreign keys are supported for both CHAR and VARCHAR.
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
CREATE TABLE child (fk VARCHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Difference in size matters (even for VARCHAR!).
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(11), FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Difference in exact type matters too.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES parent;
--echo # Both columns must use the same collation.
CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(10) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARCHAR(10) COLLATE utf8mb4_bin, FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Binary strings are not compatible with non-binary strings.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(40), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo # VARBINARY is not compatible with BINARY
CREATE TABLE parent (pk VARBINARY(10) PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(10), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES parent;
--echo # Non-binary string types are incompatible with other types.
CREATE TABLE parent (pk CHAR(4) CHARACTER SET latin1 PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;
--echo # Binary string types are incompatible with many other.
CREATE TABLE parent (pk BINARY(4) PRIMARY KEY);
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.e) YEAR and DATE are compatible with themselves and nothing else.
--echo #          
CREATE TABLE parent (pk YEAR PRIMARY KEY);
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
CREATE TABLE parent (pk DATE PRIMARY KEY);
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # DATE and YEAR types are not compatible with other types.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk MEDIUMINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.f) Temporals with fractional part are compatible with
--echo #      themselves, provided that scale stays the same.
--echo #          
CREATE TABLE parent (pk TIMESTAMP(6) PRIMARY KEY);
CREATE TABLE child (fk TIMESTAMP(6), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLES child, parent;
CREATE TABLE parent (pk DATETIME PRIMARY KEY);
CREATE TABLE child (fk DATETIME, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child, parent;
CREATE TABLE parent (pk TIME(2) PRIMARY KEY);
CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Scale matters.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--echo # Temporals with fractional part are not compatible with other types.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk MEDIUMINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP(2), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.g) Columns of ENUM type are compatible with themselves.
--echo #
--echo # They are also compatible with CHAR(1..2) and SET types, but
--echo # it is not clear if it is a bug or feature.
CREATE TABLE parent(pk ENUM('a') PRIMARY KEY);
--echo # Foreign key over ENUMs are supported, element names and count do
--echo # not matter provided that storage size is the same.
CREATE TABLE child (fk ENUM('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Storage size should match.
--let $x100enum =`WITH RECURSIVE gen (num, el) AS (SELECT 1, CAST('\'a1\'' AS CHAR(2048)) UNION SELECT num+1, CONCAT(el,',\'a',num+1,'\'') FROM gen WHERE num <= 256) SELECT el FROM gen WHERE num = 256`
--error ER_FK_INCOMPATIBLE_COLUMNS
--eval CREATE TABLE child (fk ENUM($x100enum), FOREIGN KEY (fk) REFERENCES parent(pk))
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

--echo #
--echo # 2.h) Columns of SET type are compatible if storage size is
--echo #      the same.
--echo #
--echo # They are also compatible with CHAR(1..4) and ENUM types, but
--echo # it is not clear if it is a bug or feature.
CREATE TABLE parent(pk SET('a') PRIMARY KEY);
--echo # Foreign key over SETs are supported, element names and count do
--echo # not matter provided that storage size is the same.
CREATE TABLE child (fk SET('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE child;
--echo # Storage size should match.
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk SET('a1','a2','a3','a4','a5','a6','a7','a8','a9'), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
--error ER_FK_INCOMPATIBLE_COLUMNS
CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
DROP TABLE parent;

SET default_storage_engine = @save_storage_engine;


--echo #
--echo # Additional test for bug#29173134 "FOREIGN KEY CONSTRAINT NAMES TAKING
--echo #                                   INDEX NAME".
--echo #
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=NDB;
CREATE TABLE t2 (fk1 INT, fk2 INT, fk3 INT, fk4 INT,
                 FOREIGN KEY (fk1) REFERENCES t1 (id),
                 CONSTRAINT c FOREIGN KEY (fk2) REFERENCES t1 (id),
                 FOREIGN KEY d (fk3) REFERENCES t1 (id),
                 CONSTRAINT e FOREIGN KEY f (fk4) REFERENCES t1 (id)
                ) ENGINE=NDB;
SHOW CREATE TABLE t2;
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
  WHERE TABLE_NAME = 't2' ORDER BY CONSTRAINT_NAME;
DROP TABLES t2, t1;


--echo #
--echo # Bug#30171959 "INCONSISTENT NAMING OF FOREIGN KEYS OVER NDB TABLES".
--echo #

--echo # Basic check of oreign key name generation by CREATE TABLE.
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDB;
CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, fk4 INT, fk5 INT, fk6 INT,
                    fk7 INT, fk8 INT, fk9 INT, fk10 INT,
                    CONSTRAINT c1 FOREIGN KEY (fk1) REFERENCES parent (pk),
                    FOREIGN KEY (fk2) REFERENCES parent (pk),
                    FOREIGN KEY (fk3) REFERENCES parent (pk)) ENGINE=NDB;
--let $ndb_table_name=child
--source verify_foreign_keys.inc

--echo # Similar check for ALTER TABLE.
ALTER TABLE child ADD FOREIGN KEY (fk4) REFERENCES parent (pk),
                  ADD CONSTRAINT c2 FOREIGN KEY (fk5) REFERENCES parent (pk),
                  ADD FOREIGN KEY (fk6) REFERENCES parent (pk);
--let $ndb_table_name=child
--source verify_foreign_keys.inc

--echo # Adding foreign key with explicit name matching pattern of generated
--echo # names should be correctly handled by increasing generated name counter.
ALTER TABLE child ADD CONSTRAINT child_fk_10 FOREIGN KEY (fk7) REFERENCES parent (pk);
ALTER TABLE child ADD FOREIGN KEY (fk8) REFERENCES parent (pk);
--let $ndb_table_name=child
--source verify_foreign_keys.inc

--echo # Dropping foreign key with generated name doesn't cause name changes for
--echo # other foreign keys with generated names.
ALTER TABLE child DROP FOREIGN KEY child_fk_4;
--let $ndb_table_name=child
--source verify_foreign_keys.inc

--echo # Generation of new names is not affected by presence of holes
--echo # in the sequence of generated names.
ALTER TABLE child ADD FOREIGN KEY (fk9) REFERENCES parent (pk);
--let $ndb_table_name=child
--source verify_foreign_keys.inc

--echo # However, if foreign key with last generated name is dropped,
--echo # name can be reused later.
ALTER TABLE child DROP FOREIGN KEY child_fk_12;
ALTER TABLE child ADD FOREIGN KEY (fk10) REFERENCES parent (pk);
--let $ndb_table_name=child
--source verify_foreign_keys.inc

--echo # Renaming of table updates generated names to preserve the pattern.
RENAME TABLE child TO child1;
--let $ndb_table_name=child1
--source verify_foreign_keys.inc

--echo # Similar test for ALTER TABLE RENAME.
ALTER TABLE child1 RENAME TO child2;
--let $ndb_table_name=child2
--source verify_foreign_keys.inc

--echo # Currently changing SE of table with foreign key is not supported.
--echo # If it is ever supported we need to handle somehow difference in
--echo # suffixes for generated foreign key names.
--error ER_FK_CANNOT_CHANGE_ENGINE
ALTER TABLE child2 ENGINE=InnoDB;
SHOW CREATE TABLE child2;
SELECT constraint_name FROM information_schema.referential_constraints
  WHERE constraint_schema='test' AND table_name='child2' ORDER BY constraint_name ASC;
DROP TABLES child2, parent;


--echo #
--echo # Additional coverage for bug#30267236 "REPLACE DICT_FOREIGN_PARSE...
--echo #                                       WITH SQL-LAYER CHECK."
--echo #
CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDB;
CREATE TABLE child (fk1 INT, b INT, CONSTRAINT c FOREIGN KEY (fk1) REFERENCES parent (pk)) ENGINE=NDB;
CREATE TABLE unrelated (a INT) ENGINE=NDB;

--echo #
--echo # Dropping non-existing foreign key should fail with consistent error.
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE child DROP FOREIGN KEY no_such_fk;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=INPLACE;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=COPY;

--echo #
--echo # Dropping existing foreign key using wrong table name should fail
--echo # with consistent error as well.
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE unrelated DROP FOREIGN KEY c;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=INPLACE;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=COPY;

--echo #
--echo # The same should happen if one tries to drop foreign key using
--echo # parent table.
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE parent DROP FOREIGN KEY c;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=INPLACE;
--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=COPY;
DROP TABLES unrelated, child, parent;