548 lines
14 KiB
Plaintext
548 lines
14 KiB
Plaintext
--echo #
|
|
--echo # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
|
|
--echo # DICT_CREATE_FOREIGN_CONSTR
|
|
--echo #
|
|
|
|
create table t1 (f1 int primary key) engine=InnoDB;
|
|
--error ER_FK_DUP_NAME
|
|
create table t2 (f1 int primary key,
|
|
constraint c1 foreign key (f1) references t1(f1),
|
|
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
|
|
create table t2 (f1 int primary key,
|
|
constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
|
|
|
|
--error ER_FK_DUP_NAME
|
|
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
|
|
|
|
set foreign_key_checks = 0;
|
|
--error ER_FK_DUP_NAME
|
|
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo #
|
|
--echo # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
|
|
--echo # NULL/NOT NULL MISMATCH
|
|
--echo #
|
|
|
|
set foreign_key_checks = 1;
|
|
show variables like 'foreign_key_checks';
|
|
|
|
CREATE TABLE t1
|
|
(a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
INDEX idx(a)) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
show create table t1;
|
|
show create table t2;
|
|
|
|
INSERT INTO t1 VALUES (1, 80);
|
|
INSERT INTO t1 VALUES (2, 81);
|
|
INSERT INTO t1 VALUES (3, 82);
|
|
INSERT INTO t1 VALUES (4, 83);
|
|
INSERT INTO t1 VALUES (5, 84);
|
|
|
|
INSERT INTO t2 VALUES (51, 1);
|
|
INSERT INTO t2 VALUES (52, 2);
|
|
INSERT INTO t2 VALUES (53, 3);
|
|
INSERT INTO t2 VALUES (54, 4);
|
|
INSERT INTO t2 VALUES (55, 5);
|
|
|
|
SELECT a, b FROM t1 ORDER BY a;
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
INSERT INTO t2 VALUES (56, 6);
|
|
|
|
--echo NewDD TODO: Runtime Bug#25722221 - RENAME COLUMN DID NOT UPDATE MYSQL.FOREIGN_KEY_COLUMN_USAGE FOR FK CONSTRAINT
|
|
#ALTER TABLE t1 CHANGE a id INT;
|
|
|
|
# SELECT id, b FROM t1 ORDER BY id;
|
|
# SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
--echo # Operations on child table
|
|
--echo TODO: Waiting for Bug#24666169
|
|
#--error ER_NO_REFERENCED_ROW_2
|
|
#INSERT INTO t2 VALUES (56, 6);
|
|
#--error ER_NO_REFERENCED_ROW_2
|
|
#UPDATE t2 SET b = 99 WHERE a = 51;
|
|
#DELETE FROM t2 WHERE a = 53;
|
|
#SELECT id, b FROM t1 ORDER BY id;
|
|
#SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
#--echo # Operations on parent table
|
|
#DELETE FROM t1 WHERE id = 1;
|
|
#UPDATE t1 SET id = 50 WHERE id = 5;
|
|
#SELECT id, b FROM t1 ORDER BY id;
|
|
#SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
DROP TABLE t2, t1;
|
|
|
|
--echo #
|
|
--echo # Bug#20752436: INNODB: FAILING ASSERTION: 0 IN FILE HANDLER0ALTER.CC
|
|
--echo # LINE 6647
|
|
--echo #
|
|
--echo # Verify that index types that cannot be used as foreign keys are
|
|
--echo # ignored when creating foreign keys.
|
|
|
|
let $SAVED_foreign_key_checks= `SELECT @@foreign_key_checks`;
|
|
set @@foreign_key_checks=0;
|
|
|
|
CREATE TABLE t1(a CHAR(100), b GEOMETRY NOT NULL SRID 0) ENGINE InnoDB;
|
|
|
|
--echo # Creating a foreign key on a GEOMETRY column is not supported
|
|
--error ER_FK_NO_INDEX_CHILD
|
|
ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
|
|
|
|
--echo # Adds FULLTEXT and SPATAL indices which cannot be used as foreign keys
|
|
ALTER TABLE t1 ADD FULLTEXT INDEX(a), ADD SPATIAL INDEX(b);
|
|
|
|
--echo # Adds a foreign key on column with FULLTEXT index.
|
|
--echo # The FULLTEXT index cannot be used and the generated key must be kept
|
|
ALTER TABLE t1 ADD CONSTRAINT fi_a FOREIGN KEY(a) REFERENCES ti2(a);
|
|
ANALYZE TABLE t1;
|
|
SHOW INDEXES FROM t1;
|
|
|
|
--echo # Attempt to add a foreign key on column with SPATIAL index.
|
|
--echo # The SPATIAL index cannot be used so this becomes an attempt at
|
|
--echo # creating a foreign key on a GEOMETRY column which is not supported
|
|
--error ER_DROP_INDEX_FK
|
|
ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
|
|
|
|
DROP TABLE t1;
|
|
eval set @@foreign_key_checks= $SAVED_foreign_key_checks;
|
|
|
|
--echo # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
|
|
--echo # base bug#24818604 [GR]
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (c1 INT PRIMARY KEY);
|
|
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1);
|
|
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
|
|
SELECT * FROM t1;
|
|
|
|
SELECT unique_constraint_name FROM information_schema.referential_constraints
|
|
WHERE table_name = 't2';
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # BUG#25976199 GROUP_REPLICATION.GR_FOREIGN_CHECKS_DISABLED TEST FAILING
|
|
--echo # ON PB2
|
|
--echo #
|
|
|
|
SET foreign_key_checks=0;
|
|
|
|
CREATE TABLE child_table (c1 INT PRIMARY KEY,
|
|
FOREIGN KEY (c1) REFERENCES parent_table(c1));
|
|
|
|
CREATE TABLE parent_table (c1 INT PRIMARY KEY);
|
|
|
|
SET foreign_key_checks=1;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN WHERE
|
|
ID='test/child_table_ibfk_1';
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE
|
|
ID='test/child_table_ibfk_1';
|
|
|
|
--error ER_FK_CANNOT_DROP_PARENT
|
|
DROP TABLE parent_table;
|
|
DROP TABLE child_table;
|
|
DROP TABLE parent_table;
|
|
|
|
--echo #
|
|
--echo # Bug#26764604 - INNODB: FAILING ASSERTION: COL_NR < TABLE->N_DEF
|
|
--echo #
|
|
|
|
SELECT @@foreign_key_checks;
|
|
|
|
CREATE TABLE t1 (
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
unique1 int(11) DEFAULT NULL,
|
|
unique2 int(11) DEFAULT NULL,
|
|
two int(11) DEFAULT NULL,
|
|
four int(11) DEFAULT NULL,
|
|
ten int(11) DEFAULT NULL,
|
|
twenty int(11) DEFAULT NULL,
|
|
forty int(11) DEFAULT NULL,
|
|
sixty int(11) DEFAULT NULL,
|
|
eighty int(11) DEFAULT NULL,
|
|
tenPercent int(11) DEFAULT NULL,
|
|
twentyPercent int(11) DEFAULT NULL,
|
|
fiftyPercent int(11) DEFAULT NULL,
|
|
unique1gc int(11) GENERATED ALWAYS AS ((unique1 + unique2)) VIRTUAL,
|
|
unique2gc int(11) GENERATED ALWAYS AS ((unique1 * unique2)) VIRTUAL,
|
|
twogc int(11) GENERATED ALWAYS AS (((two * two) + 1)) VIRTUAL,
|
|
fourgc int(11) GENERATED ALWAYS AS (((four + four) - 1)) VIRTUAL,
|
|
tengc int(11) GENERATED ALWAYS AS ((twogc + ten)) VIRTUAL,
|
|
twentygc int(11) GENERATED ALWAYS AS ((((twenty * twogc) * unique1) % 20)) VIRTUAL,
|
|
fortygc int(11) GENERATED ALWAYS AS (forty) VIRTUAL,
|
|
sixtygc int(11) GENERATED ALWAYS AS (sixty) VIRTUAL,
|
|
eightygc int(11) GENERATED ALWAYS AS ((((eighty - twentygc) - ten) - two)) VIRTUAL,
|
|
PRIMARY KEY (pk),
|
|
KEY allkey(unique1,unique2,two,four,ten,twenty,forty,sixty,eighty,tenPercent,twentyPercent,fiftyPercent),
|
|
KEY kugc1 (unique1gc),
|
|
KEY kugc2 (unique2gc),
|
|
KEY kumixgc1 (unique1gc,unique2gc),
|
|
KEY kgc1 (twogc,fourgc),
|
|
KEY kgc2 (fourgc,tengc),
|
|
KEY kgc3 (tengc,twentygc),
|
|
KEY kgc4 (twentygc,fortygc),
|
|
KEY kgc5 (fortygc,sixtygc),
|
|
KEY kgc6 (sixtygc,eightygc),
|
|
KEY allkeygc(unique1gc,unique2gc,twogc,fourgc,tengc,twentygc,fortygc,sixtygc,eightygc)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
CREATE TABLE t2_fk (
|
|
pk int(11) NOT NULL,
|
|
sixty int(11) DEFAULT NULL,
|
|
PRIMARY KEY (pk),
|
|
CONSTRAINT fk6 FOREIGN KEY (sixty) REFERENCES t1(sixty)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#27014308 - UPGRADING TO 8.0.3 FAILS IF FOREIGN KEYS OF EXACTLY 64 CHARACTERS ARE PRESENT
|
|
--echo #
|
|
|
|
--echo # test with FK identifier of length exactly 64 characters;
|
|
CREATE TABLE parent (
|
|
id INT NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=INNODB;
|
|
|
|
CREATE TABLE child (
|
|
id INT,
|
|
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
INT,
|
|
INDEX par_ind
|
|
(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
|
|
CONSTRAINT
|
|
`xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
|
|
FOREIGN
|
|
KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
|
|
REFERENCES parent(id)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
DROP TABLE child;
|
|
DROP TABLE parent;
|
|
|
|
--echo # test with FK identifier of length exactly 65 characters,
|
|
--echo # child table creation should fail
|
|
|
|
CREATE TABLE parent (
|
|
id INT NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=INNODB;
|
|
|
|
--error ER_TOO_LONG_IDENT
|
|
CREATE TABLE child (
|
|
id INT,
|
|
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
|
INT,
|
|
INDEX par_ind
|
|
(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
|
|
CONSTRAINT
|
|
`xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
|
|
FOREIGN
|
|
KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
|
|
REFERENCES parent(id)
|
|
ON DELETE CASCADE
|
|
) ENGINE=INNODB;
|
|
|
|
DROP TABLE parent;
|
|
|
|
--echo # Test delete/update rules combination.
|
|
CREATE TABLE t1
|
|
(a INT NOT NULL,
|
|
b INT NOT NULL,
|
|
INDEX idx(a)) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a))
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE NO ACTION)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE SET NULL)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE NO ACTION)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE RESTRICT)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE SET NULL)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE NO ACTION)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE RESTRICT)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE SET NULL)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE RESTRICT)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE SET NULL)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE NO ACTION)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
|
|
CREATE TABLE t2
|
|
(a INT KEY,
|
|
b INT,
|
|
INDEX ind(b),
|
|
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE RESTRICT)
|
|
ENGINE=InnoDB;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
|
|
DROP TABLE t2;
|
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Bug#28581468 - RENAMING PARENT COLUMN IN A FOREIGN KEY FAILS WITH STRANGE ERROR.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 CHANGE a id INT;
|
|
CHECK TABLE t1;
|
|
CHECK TABLE t2;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 CHANGE a id INT NOT NULL;
|
|
CHECK TABLE t1;
|
|
CHECK TABLE t2;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo #
|
|
--echo # Bug #27020089 "I_S.INNODB_SYS_FOREIGN LOST FOREIGN KEY INFORMATION."
|
|
--echo #
|
|
CREATE TABLE t1(pk INT PRIMARY KEY);
|
|
CREATE TABLE `......................................`
|
|
(fk INT, FOREIGN KEY(fk) REFERENCES t1(pk));
|
|
SHOW CREATE TABLE `......................................`;
|
|
SELECT id, for_name, ref_name FROM information_schema.innodb_foreign WHERE id LIKE 'test%';
|
|
DROP TABLES `......................................`, t1;
|