mariadb/mysql-test/suite/innodb/r/innodb-fk-warnings.result
Marko Mäkelä cffbb17480 MDEV-28933: Per-table unique FOREIGN KEY constraint names
Before MySQL 4.0.18, user-specified constraint names were ignored.
Starting with MySQL 4.0.18, the specified constraint name was
prepended with the schema name and '/'.  Now we are transforming
into a format where the constraint name is prepended with the
dict_table_t::name and the impossible UTF-8 sequence 0xff.
Generated constraint names will be ASCII decimal numbers.

On upgrade, old FOREIGN KEY constraint names will be displayed
without any schema name prefix. They will be updated to the new
format on DDL operations.

dict_foreign_t::sql_id(): Return the SQL constraint name
without any schemaname/tablename\377 or schemaname/ prefix.

row_rename_table_for_mysql(), dict_table_rename_in_cache():
Simplify the logic: Just rename constraints to the new format.

dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id().

innobase_get_foreign_key_info(): Let my_error() refer to erroneous
anonymous constraints as "(null)".

row_delete_constraint(): Try to drop all 3 constraint name variants.

Reviewed by: Thirunarayanan Balathandayuthapani
Tested by: Matthias Leich
2025-07-08 12:30:27 +03:00

131 lines
8.2 KiB
Text

CREATE TABLE t1 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT test FOREIGN KEY (b) REFERENCES t1 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE t2 (
id int(11) NOT NULL PRIMARY KEY,
a int(11) NOT NULL,
b int(11) NOT NULL,
c int not null,
CONSTRAINT mytest FOREIGN KEY (c) REFERENCES t1(id),
CONSTRAINT test FOREIGN KEY (b) REFERENCES t2 (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
show warnings;
Level Code Message
drop table t2,t1;
create table t1(a int) engine=innodb;
create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Create table `test`.`t2` with foreign key `a` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
drop table t1;
create table t1(a int unique, b int) engine=innodb;
create table t2(a int, b int, foreign key (a) references t1(a), foreign key (b) references t1(b)) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Create table `test`.`t2` with foreign key (b) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
drop table t1;
create table t1(a int not null primary key, b int) engine=innodb;
create table t2(a int, b int, constraint a foreign key a (a) references t1(a),
constraint a foreign key a (a) references t1(b)) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
create table t2(a int, b int, constraint a foreign key a (a) references t1(a)) engine=innodb;
alter table t2 add constraint b foreign key (b) references t2(b);
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Alter table `test`.`t2` with foreign key `b` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
drop table t2, t1;
create table t1 (f1 integer primary key) engine=innodb;
alter table t1 add constraint c1 foreign key (f1) references t11(f1);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Alter table `test`.`t1` with foreign key `c1` constraint failed. Referenced table `test`.`t11` not found in the data dictionary.
Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t1`
drop table t1;
create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb;
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Create table `mysqld.1`.`t2` with foreign key constraint failed. Referenced table `mysqld.1`.`t1` not found in the data dictionary close to foreign key(a) references t1(a)) engine=innodb.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint
alter table t1 add foreign key(b) references t1(a);
ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Alter table `mysqld.1`.`t1` with foreign key constraint failed. Referenced table `mysqld.1`.`t1` not found in the data dictionary close to foreign key(b) references t1(a).
Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Create table `test`.`t2` with foreign key constraint failed. Temporary tables can't have foreign key constraints.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
alter table t1 add foreign key(b) references t1(a);
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.
Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t1`
drop table t1;
create table t1(a int not null primary key, b int, key(b)) engine=innodb;
alter table t1 add foreign key(a,b) references t1(a);
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
show warnings;
Level Code Message
Error 1239 Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
drop table t1;
create table t1(a int not null primary key, b int, key(b)) engine=innodb;
alter table t1 add foreign key(a) references t1(a,b);
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
show warnings;
Level Code Message
Error 1239 Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
drop table t1;
create table t1 (f1 integer not null primary key) engine=innodb;
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null;
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Alter table `test`.`t1` with foreign key `c1` constraint failed. You have defined a SET NULL condition but column 'f1' is defined as NOT NULL.
Error 1005 Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t1`
create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Create table `test`.`t2` with foreign key (a) constraint failed. You have defined a SET NULL condition but column 'a' is defined as NOT NULL.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
drop table t1;
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb;
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
show warnings;
Level Code Message
Warning 150 Create table `test`.`t2` with foreign key (a) constraint failed. Field type or character set for column 'a' does not match referenced column 'f1'.
Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
Warning 1215 Cannot add foreign key constraint for `t2`
drop table t1;