mirror of
https://github.com/MariaDB/server.git
synced 2025-07-23 11:45:02 +02:00

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
44 lines
1.8 KiB
Text
44 lines
1.8 KiB
Text
#
|
|
# MDEV-22230 : Unexpected ER_ERROR_ON_RENAME upon DROP
|
|
# non-existing FOREIGN KEY
|
|
#
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 DROP FOREIGN KEY x, ALGORITHM=COPY;
|
|
ERROR 42000: Can't DROP FOREIGN KEY `x`; check that it exists
|
|
ALTER TABLE t1 DROP FOREIGN KEY x, ALGORITHM=INPLACE;
|
|
ERROR 42000: Can't DROP FOREIGN KEY `x`; check that it exists
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (a INT, FOREIGN KEY fk_id (a) REFERENCES t1(a))ENGINE=InnoDB;
|
|
CREATE TABLE t3 (a INT, FOREIGN KEY fk_1 (a) REFERENCES t1(a))ENGINE=InnoDB;
|
|
ALTER TABLE t3 DROP FOREIGN KEY IF EXISTS fk_id;
|
|
Warnings:
|
|
Note 1091 Can't DROP FOREIGN KEY `fk_id`; check that it exists
|
|
DROP TABLE t3, t2;
|
|
ALTER TABLE t1 MODIFY COLUMN a VARCHAR(2), DROP FOREIGN KEY IF EXISTS x;
|
|
Warnings:
|
|
Note 1091 Can't DROP FOREIGN KEY `x`; check that it exists
|
|
DROP TABLE t1;
|
|
CREATE DATABASE best;
|
|
CREATE TABLE best.t1(f1 INT, KEY(f1))ENGINE=InnoDB;
|
|
CREATE TABLE best.t2(f1 INT, FOREIGN KEY foo(f1) REFERENCES t1(f1))ENGINE=InnoDB;
|
|
CREATE TABLE t1(f1 INT, KEY(f1))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT, FOREIGN KEY foo(f1) REFERENCES t1(f1))ENGINE=InnoDB;
|
|
ALTER TABLE t2 DROP FOREIGN KEY foo;
|
|
ALTER TABLE t2 DROP FOREIGN KEY foo;
|
|
ERROR 42000: Can't DROP FOREIGN KEY `foo`; check that it exists
|
|
ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS foo;
|
|
Warnings:
|
|
Note 1091 Can't DROP FOREIGN KEY `foo`; check that it exists
|
|
SHOW CREATE TABLE best.t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`f1` int(11) DEFAULT NULL,
|
|
KEY `foo` (`f1`),
|
|
CONSTRAINT `foo` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
|
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
|
foo best/t2 best/t1 1 0
|
|
DROP TABLE best.t2, best.t1, t2, t1;
|
|
DROP DATABASE best;
|