mirror of
https://github.com/MariaDB/server.git
synced 2025-07-21 18:58:29 +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
119 lines
5.7 KiB
Text
119 lines
5.7 KiB
Text
drop table if exists t1,t2;
|
|
create table t1 (
|
|
a int not null references t2,
|
|
b int not null constraint t2_c references t2 (c),
|
|
primary key (a,b),
|
|
foreign key (a) references t3 match full,
|
|
foreign key (a) references t3 match partial,
|
|
foreign key (a,b) references t3 (c,d) on delete no action
|
|
on update no action,
|
|
foreign key (a,b) references t3 (c,d) on update cascade,
|
|
foreign key (a,b) references t3 (c,d) on delete set default,
|
|
foreign key (a,b) references t3 (c,d) on update set null);
|
|
create index a on t1 (a);
|
|
create unique index b on t1 (a,b);
|
|
drop table t1;
|
|
create table t1 (id int primary key) engine = innodb;
|
|
create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb;
|
|
insert into t1 values (1), (2), (3), (4), (5), (6);
|
|
insert into t2 values (3), (5);
|
|
delete from t1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
|
|
select * from t1;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
delete ignore from t1;
|
|
Warnings:
|
|
Warning 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
|
|
Warning 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))
|
|
select row_count();
|
|
row_count()
|
|
-1
|
|
select * from t1;
|
|
id
|
|
3
|
|
5
|
|
drop table t2;
|
|
drop table t1;
|
|
drop table if exists t_34455;
|
|
create table t_34455 (
|
|
a int not null,
|
|
foreign key (a) references t3 (a) match full match partial);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'match partial)' at line 3
|
|
create table t_34455 (
|
|
a int not null,
|
|
foreign key (a) references t3 (a) on delete set default match full);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'match full)' at line 3
|
|
create table t_34455 (
|
|
a int not null,
|
|
foreign key (a) references t3 (a) on update set default match full);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'match full)' at line 3
|
|
create table t_34455 (
|
|
a int not null,
|
|
foreign key (a) references t3 (a)
|
|
on delete set default on delete set default);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'delete set default)' at line 4
|
|
create table t_34455 (
|
|
a int not null,
|
|
foreign key (a) references t3 (a)
|
|
on update set default on update set default);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'update set default)' at line 4
|
|
create table t_34455 (a int not null);
|
|
alter table t_34455
|
|
add foreign key (a) references t3 (a) match full match partial);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'match partial)' at line 2
|
|
alter table t_34455
|
|
add foreign key (a) references t3 (a) on delete set default match full);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'match full)' at line 2
|
|
alter table t_34455
|
|
add foreign key (a) references t3 (a) on update set default match full);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'match full)' at line 2
|
|
alter table t_34455
|
|
add foreign key (a) references t3 (a)
|
|
on delete set default on delete set default);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'delete set default)' at line 3
|
|
alter table t_34455
|
|
add foreign key (a) references t3 (a)
|
|
on update set default on update set default);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'update set default)' at line 3
|
|
drop table t_34455;
|
|
#
|
|
# MDEV-18460 Don't allow multiple table CONSTRAINTs with the same name.
|
|
#
|
|
CREATE TABLE tpk (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL) ENGINE=Innodb;
|
|
CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1), CONSTRAINT sid CHECK (c2>15));
|
|
ERROR HY000: Duplicate CHECK constraint name 'sid'
|
|
CREATE TABLE tfk (c1 INT, c2 INT, CONSTRAINT sid UNIQUE (c1));
|
|
ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15);
|
|
ERROR HY000: Duplicate CHECK constraint name 'sid'
|
|
DROP TABLE tfk;
|
|
CREATE TABLE tfk (c1 INT, c2 INT,
|
|
CONSTRAINT sid FOREIGN KEY (c1) REFERENCES tpk (id)) ENGINE=Innodb;
|
|
show create table tfk;
|
|
Table Create Table
|
|
tfk CREATE TABLE `tfk` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
`c2` int(11) DEFAULT NULL,
|
|
KEY `sid` (`c1`),
|
|
CONSTRAINT `sid` FOREIGN KEY (`c1`) REFERENCES `tpk` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE tfk ADD CONSTRAINT sid CHECK (c2>15);
|
|
ERROR HY000: Duplicate CHECK constraint name 'sid'
|
|
ALTER TABLE tfk ADD CONSTRAINT sid UNIQUE(c2);
|
|
ERROR 42000: Duplicate key name 'sid'
|
|
DROP TABLE tfk;
|
|
DROP TABLE tpk;
|
|
#
|
|
# MDEV-33223 Assertion `dst_size > 4' failed in size_t Identifier_chain2::make_sep_name_opt_casedn(char*, size_t, int, bool) const
|
|
#
|
|
CREATE DATABASE x;
|
|
USE x;
|
|
CREATE TABLE t (i INT, j INT, CONSTRAINT fk2 FOREIGN KEY(i) REFERENCES p (i)) ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `x`.`t` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
DROP DATABASE x;
|
|
USE test;
|