mirror of
https://github.com/MariaDB/server.git
synced 2025-07-14 07:18:12 +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
128 lines
4.4 KiB
Text
128 lines
4.4 KiB
Text
--source include/have_innodb.inc
|
|
|
|
#
|
|
# MDEV-8524: Improve error messaging when there is duplicate key or foreign key names
|
|
#
|
|
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;
|
|
|
|
drop table t2,t1;
|
|
|
|
#
|
|
# MDEV-6697: Improve foreign keys warnings/errors
|
|
#
|
|
|
|
#
|
|
# No index for referenced columns
|
|
#
|
|
create table t1(a int) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2(a int, constraint a foreign key a (a) references t1(a)) engine=innodb;
|
|
show warnings;
|
|
drop table t1;
|
|
|
|
create table t1(a int unique, b int) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2(a int, b int, foreign key (a) references t1(a), foreign key (b) references t1(b)) engine=innodb;
|
|
show warnings;
|
|
drop table t1;
|
|
|
|
create table t1(a int not null primary key, b int) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
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;
|
|
show warnings;
|
|
create table t2(a int, b int, constraint a foreign key a (a) references t1(a)) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
alter table t2 add constraint b foreign key (b) references t2(b);
|
|
show warnings;
|
|
drop table t2, t1;
|
|
|
|
#
|
|
# Referenced table does not exists
|
|
#
|
|
|
|
create table t1 (f1 integer primary key) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
alter table t1 add constraint c1 foreign key (f1) references t11(f1);
|
|
show warnings;
|
|
drop table t1;
|
|
|
|
#
|
|
# Foreign key on temporal tables
|
|
#
|
|
|
|
create temporary table t1(a int not null primary key, b int, key(b)) engine=innodb;
|
|
# remove echoes and uncomment the commented when MDEV-8569 is fixed
|
|
--echo create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
|
|
--echo ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
--echo show warnings;
|
|
--echo Level Code Message
|
|
--echo 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.
|
|
--echo Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
--echo Warning 1215 Cannot add foreign key constraint
|
|
--echo alter table t1 add foreign key(b) references t1(a);
|
|
--echo ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
--echo show warnings;
|
|
--echo Level Code Message
|
|
--echo 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).
|
|
--echo Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
--echo Warning 1215 Cannot add foreign key constraint
|
|
--error ER_CANT_CREATE_TABLE
|
|
create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
|
|
show warnings;
|
|
--error ER_CANT_CREATE_TABLE
|
|
alter table t1 add foreign key(b) references t1(a);
|
|
show warnings;
|
|
drop table t1;
|
|
|
|
#
|
|
# Column numbers do not match
|
|
#
|
|
create table t1(a int not null primary key, b int, key(b)) engine=innodb;
|
|
--error 1239
|
|
alter table t1 add foreign key(a,b) references t1(a);
|
|
show warnings;
|
|
drop table t1;
|
|
create table t1(a int not null primary key, b int, key(b)) engine=innodb;
|
|
--error 1239
|
|
alter table t1 add foreign key(a) references t1(a,b);
|
|
show warnings;
|
|
drop table t1;
|
|
|
|
#
|
|
# ON UPDATE/DELETE SET NULL on NOT NULL column
|
|
#
|
|
create table t1 (f1 integer not null primary key) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
alter table t1 add constraint c1 foreign key (f1) references t1(f1) on update set null;
|
|
show warnings;
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2(a int not null, foreign key(a) references t1(f1) on delete set null) engine=innodb;
|
|
show warnings;
|
|
drop table t1;
|
|
|
|
#
|
|
# Incorrect types
|
|
#
|
|
create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb;
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
|
|
show warnings;
|
|
drop table t1;
|