mirror of
https://github.com/MariaDB/server.git
synced 2025-07-26 05:04:59 +02:00
cffbb17480
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
131 lines
8.2 KiB
Text
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;
|