mariadb/mysql-test/suite/innodb/r/fk_drop_alter.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

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;