[MDEV-20853] FOREIGN KEY can reference any KEY, not just PRIMARY KEY and UNIQUE Created: 2019-10-17  Updated: 2023-12-15

Status: Stalled
Project: MariaDB Server
Component/s: Data Definition - Create Table, Documentation
Affects Version/s: 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Anders Karlsson Assignee: Joe Cotellese
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux CentOS 7.2

Issue Links:
PartOf

 Description   

A foreign key should only be able to reference a UNIQUE or PRIMARY KEY constraint, but MariaDB allows any key/index to be referenced. To reproduce:

CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY,
 
  c2 INT NOT NULL,
 
  KEY(c2));
 
 
 
CREATE TABLE t2(c1 INT NOT NULL PRIMARY KEY,
 
  c2 INT NOT NULL,
 
  FOREIGN KEY (c2) REFERENCES t1(c2));

This is invalid in the SQL Standard as well as according to MariaDB Knowledge base: https://mariadb.com/kb/en/library/foreign-keys/ which states "The referenced columns must be a PRIMARY KEY or a UNIQUE index".

 Comments   
Comment by Elena Stepanova [ 2019-10-17 ]

It has never been the case either in MySQL or in MariaDB. Based on the history, the statement was (wrongly) added to the KB by a community member long time ago.
Given the legacy, I can't imagine that we could forbid non-uniqueness of referenced columns in any foreseeable future, so it will need to be fixed on the documentation side.
I will assign it to serg though, to confirm that we are not going to make this change.

Note: MySQL documentation specifically mentions that uniqueness is not required, but recommends to use unique keys.

Comment by Marko Mäkelä [ 2019-10-17 ]

If we fix MDEV-10393 by moving the FOREIGN KEY handling from InnoDB to the SQL layer (where it IMO belongs), then I think that the requirement to have any index on the columns in either the referencing or referenced table should be removed. It is of course recommended to have those indexes.

Comment by Anders Karlsson [ 2019-10-17 ]

It's less about the indexes and more about the constraints (that happen to be implemented as indexes in the case of MariaDB). That InnoDB can reference a non-unique key is true and is a noted Standard SQL extension, which I wasn't aware of. So I admit to being wrong.

Comment by Elena Stepanova [ 2019-10-18 ]

I'm re-opening it since there is still a documentation problem, the KB explicitly states "The referenced columns must be a PRIMARY KEY or a UNIQUE index". It needs to be adjusted.

Comment by Julien Fritsch [ 2021-09-22 ]

jacob.moorman from our discussion, I decided to reassign Kenneth's ticket to you?

Generated at Thu Feb 08 09:02:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.