Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
13.0.1, 13.1.1
-
None
Description
- Description
Hi, MariaDB developer.
Thanks for reading my report.
Creating a FULLTEXT index on a foreign-key column appears to disable foreign-key enforcement in MariaDB, while information_schema.KEY_COLUMN_USAGE still reports the constraint as active. As a result, MariaDB allows data that violates the foreign key even though the system catalog still claims the constraint exists. - Steps to reproduce
DROP DATABASE IF EXISTS test_fkft;
CREATE DATABASE test_fkft;
USE test_fkft;
CREATE TABLE parent (
c0 varchar(16) NOT NULL,
PRIMARY KEY (c0)
) ENGINE=InnoDB;CREATE TABLE child (
id int NOT NULL,
c0 varchar(16) DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT fk0 FOREIGN KEY (c0) REFERENCES parent (c0)
) ENGINE=InnoDB;INSERT INTO parent (c0) VALUES ('a');
INSERT INTO child (id, c0) VALUES (1, 'a');
CREATE FULLTEXT INDEX ft ON child (c0);
-- Must fail with a foreign-key error. MariaDB accepts it.-- MySQL fails with "Cannot add or update a child row: a foreign key constraint fails (`test_fkft`.`child`, CONSTRAINT `fk0` FOREIGN KEY (`c0`) REFERENCES `parent` (`c0`))"UPDATE child SET c0 = 'b';
- Expected behavior
The UPDATE should fail because 'b' does not exist in parent(c0).
MySQL rejects the same statement with a foreign-key constraint error. - Actual behavior
MariaDB accepts the UPDATE.
However, the foreign key is still reported in KEY_COLUMN_USAGE:-- Catalog still claims fk0. Expected: no such row, or SHOW without CONSTRAINT.SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAMEFROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'test_fkft'
AND REFERENCED_TABLE_NAME IS NOT NULL;
+------------+-----------------+-------------+-----------------------+------------------------+
| TABLE_NAME | CONSTRAINT_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |+------------+-----------------+-------------+-----------------------+------------------------+
| child | fk0 | c0 | parent | c0 |+------------+-----------------+-------------+-----------------------+------------------------+
The table now contains an orphaned foreign-key value:
-- Violation of foreign key constraintsSELECT child.c0
FROM child
WHERE NOT EXISTS (SELECT 1 FROM parent WHERE parent.c0 = child.c0);
+------+
| c0 |+------+
| b |+------+
Therefore, the catalog reports fk0 as present, but the corresponding referential-integrity constraint is no longer enforced.
Creating a FULLTEXT index should either preserve foreign-key enforcement or be rejected if the combination is unsupported