Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6.15, 10.6.17, 10.4(EOL), 10.5
-
None
Description
Consider this test case:
MariaDB [test]> create table A
|
-> (
|
-> IDA int(19) primary key,
|
-> someTextA varchar(1000)
|
-> ) engine = innodb;
|
Query OK, 0 rows affected (0.095 sec)
|
|
|
MariaDB [test]> create table B ( IDB int primary key, someTextB varchar(1000), someInt1B int, someInt2B int, fk_IDA int(19)
|
) engine = innodb default charset utf8mb4;
|
Query OK, 0 rows affected (0.085 sec)
|
|
|
MariaDB [test]> alter table B add constraint unique_B unique key (fk_IDA, someTextB);
|
Query OK, 0 rows affected (0.196 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table B\G
|
*************************** 1. row ***************************
|
Table: B
|
Create Table: CREATE TABLE `B` (
|
`IDB` int(11) NOT NULL,
|
`someTextB` varchar(1000) DEFAULT NULL,
|
`someInt1B` int(11) DEFAULT NULL,
|
`someInt2B` int(11) DEFAULT NULL,
|
`fk_IDA` int(19) DEFAULT NULL,
|
PRIMARY KEY (`IDB`),
|
UNIQUE KEY `unique_B` (`fk_IDA`,`someTextB`) USING HASH
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
1 row in set (0.003 sec)
|
|
|
MariaDB [test]> alter table B add constraint B_fk_ida foreign key (fk_IDA) references A (IDA);
|
ERROR 1005 (HY000): Can't create table `test`.`b` (errno: 150 "Foreign key constraint is incorrectly formed")
|
If we check INNODB STATUS we'll see the "reason":
MariaDB [test]> show engine innodb status\G
|
*************************** 1. row ***************************
|
Type: InnoDB
|
Name:
|
Status:
|
=====================================
|
2024-03-12 15:12:21 0x700001255000 INNODB MONITOR OUTPUT
|
=====================================
|
Per second averages calculated from the last 34 seconds
|
-----------------
|
BACKGROUND THREAD
|
-----------------
|
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 388039 srv_idle
|
srv_master_thread log flush and writes: 388039
|
----------
|
SEMAPHORES
|
----------
|
------------------------
|
LATEST FOREIGN KEY ERROR
|
------------------------
|
2024-03-12 15:12:11 0x700001255000 Error in foreign key constraint of table `test`.`b`:
|
Alter table `test`.`b` with foreign key `B_fk_ida` constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.------------
|
TRANSACTIONS
|
------------
|
Trx id counter 906470
|
Purge done for trx's n:o < 906468 undo n:o < 0 state: running but idle
|
History list length 0
|
...
|
and it's weird as the referenced table obviously has referenced column as a primary key:
MariaDB [test]> show create table A\G
|
*************************** 1. row ***************************
|
Table: A
|
Create Table: CREATE TABLE `A` (
|
`IDA` int(19) NOT NULL,
|
`someTextA` varchar(1000) DEFAULT NULL,
|
PRIMARY KEY (`IDA`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
1 row in set (0.000 sec)
|
Moreover, if we drop UNIQUE key we can add the foreign key constraint:
MariaDB [test]> alter table B drop key `unique_B`;
|
Query OK, 0 rows affected (0.182 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table B add constraint B_fk_ida foreign key (fk_IDA) references A (IDA);
|
Query OK, 0 rows affected (0.203 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table B\G
|
*************************** 1. row ***************************
|
Table: B
|
Create Table: CREATE TABLE `B` (
|
`IDB` int(11) NOT NULL,
|
`someTextB` varchar(1000) DEFAULT NULL,
|
`someInt1B` int(11) DEFAULT NULL,
|
`someInt2B` int(11) DEFAULT NULL,
|
`fk_IDA` int(19) DEFAULT NULL,
|
PRIMARY KEY (`IDB`),
|
KEY `B_fk_ida` (`fk_IDA`),
|
CONSTRAINT `B_fk_ida` FOREIGN KEY (`fk_IDA`) REFERENCES `A` (`IDA`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
1 row in set (0.003 sec)
|
The real reason has something to do with the unique key with long enough varchar(N) column, either due to large N or character set. This works, for example:
MariaDB [test]> drop table B;
|
Query OK, 0 rows affected (0.102 sec)
|
|
|
MariaDB [test]> create table B ( IDB int primary key, someTextB varchar(1000), someInt1B int, someInt2B int, fk_IDA int(19)
|
) engine = innodb default charset utf8;
|
Query OK, 0 rows affected (0.073 sec)
|
|
|
MariaDB [test]> alter table B add constraint unique_B unique key (fk_IDA, someTextB);
|
Query OK, 0 rows affected (0.177 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table B add constraint B_fk_ida foreign key (fk_IDA) references A (IDA);
|
Query OK, 0 rows affected (0.180 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table B\G
|
*************************** 1. row ***************************
|
Table: B
|
Create Table: CREATE TABLE `B` (
|
`IDB` int(11) NOT NULL,
|
`someTextB` varchar(1000) DEFAULT NULL,
|
`someInt1B` int(11) DEFAULT NULL,
|
`someInt2B` int(11) DEFAULT NULL,
|
`fk_IDA` int(19) DEFAULT NULL,
|
PRIMARY KEY (`IDB`),
|
UNIQUE KEY `unique_B` (`fk_IDA`,`someTextB`),
|
CONSTRAINT `B_fk_ida` FOREIGN KEY (`fk_IDA`) REFERENCES `A` (`IDA`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
1 row in set (0.003 sec)
|
Something clearly wrong goes on here, with checking the existing UNIQUE key as the one to support FOREIGN KEY constraint.
In any case, what prevents the creation of proper single column index for the foreign key if UNIQUE is not suitable for the purpose, or presenting proper reason of the error at least?
Attachments
Issue Links
- is caused by
-
MDEV-371 Unique indexes for blobs
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Stalled
-