Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
N/A
-
None
Description
I'm setting it to Minor as I don't think it would be anyone's loss if a foreign key on a vector column couldn't be created, but it seems to be rejected on a wrong reason, maybe it could/should be improved.
|
bb-11.6-MDEV-32887-vector 764592a4da2a1b490471732fbefe2ce745ce1f32 |
MariaDB [test]> create or replace table t (a vector(1) not null, key(a)) engine=InnoDB; |
Query OK, 0 rows affected (0.094 sec) |
|
|
MariaDB [test]> create table t2 (b vector(1) not null, vector(b), foreign key(b) references t(a)) engine=InnoDB; |
ERROR 1005 (HY000): Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") |
MariaDB [test]> show warnings;
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 150 | Create table `test`.`t2` with foreign key (b) constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns. |
|
| Error | 1005 | Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") | |
| Warning | 1215 | Cannot add foreign key constraint for `t2` | |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
3 rows in set (0.000 sec) |
So, it claims that table t does not have a key on the vector column a, but of course it does.
It appears that the presence of vector index prevents it from automatically creating a normal key on the column b, because this works (the same without vector key):
MariaDB [test]> create or replace table t2 (b vector(1) not null, foreign key(b) references t(a)) engine=InnoDB; |
Query OK, 0 rows affected (0.078 sec) |
|
|
MariaDB [test]> show create table t2\G |
*************************** 1. row ***************************
|
Table: t2 |
Create Table: CREATE TABLE `t2` ( |
`b` vector(1) NOT NULL, |
KEY `b` (`b`), |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t` (`a`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
1 row in set (0.001 sec) |
and this works too (the same with vector key and explicit key on column b):
MariaDB [test]> create or replace table t2 (b vector(1) not null, vector(b), key(b), foreign key(b) references t(a)) engine=InnoDB; |
Query OK, 0 rows affected (0.167 sec) |
|
|
MariaDB [test]> show create table t2\G |
*************************** 1. row ***************************
|
Table: t2 |
Create Table: CREATE TABLE `t2` ( |
`b` vector(1) NOT NULL, |
KEY `b_2` (`b`), |
VECTOR KEY `b` (`b`), |
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t` (`a`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
It doesn't compare directly with spatial, because a normal key on a spatial column is converted into a prefix key and then the FK is rejected on the reason "There is only prefix index in the referenced table".
Attachments
Issue Links
- is caused by
-
MDEV-34939 vector search in 11.7
-
- Closed
-