Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 13.0.1, 13.1.1
-
ubuntu 22.04
Description
Hi, MariaDB developer. Thanks for reading my report. I find a bug.
MariaDB reports the non-unique index i0 as UNIQUE_CONSTRAINT_NAME for a foreign key referencing parent(c1), even though parent(c1) has a UNIQUE constraint uk0.
MySQL reports uk0.
Reproduction
CREATE TABLE parent (c0 INT, c1 INT, PRIMARY KEY (c0)); |
CREATE TABLE child (c0 INT, c1 INT, PRIMARY KEY (c0)); |
INSERT INTO parent VALUES (1, 1), (2, 2); |
INSERT INTO child VALUES (1, 1); |
|
|
ALTER TABLE parent ADD INDEX i0 (c1); |
ALTER TABLE parent ADD UNIQUE KEY uk0 (c1); |
|
|
ALTER TABLE child |
ADD CONSTRAINT fk0 FOREIGN KEY (c1) REFERENCES parent (c1); |
|
|
SELECT CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME |
FROM information_schema.REFERENTIAL_CONSTRAINTS |
WHERE CONSTRAINT_SCHEMA = DATABASE() |
AND CONSTRAINT_NAME = 'fk0'; |
Actual behavior
MariaDB returns:
CONSTRAINT_NAME | UNIQUE_CONSTRAINT_NAME
|
----------------+-----------------------
|
fk0 | i0
|
However, i0 is only a non-unique index.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE |
FROM information_schema.TABLE_CONSTRAINTS |
WHERE TABLE_SCHEMA = DATABASE() |
AND TABLE_NAME = 'parent'; |
returns:
PRIMARY | PRIMARY KEY |
uk0 | UNIQUE |
Expected behavior
UNIQUE_CONSTRAINT_NAME should report:
fk0 | uk0
|
because uk0 is the UNIQUE constraint on parent(c1).
Attachments
Issue Links
- relates to
-
MDEV-24552 ALTER causes discrepancy in FK information between server and engine, unexpected ER_CANNOT_ADD_FOREIGN
-
- Open
-