MySQL Bugs: #89014: Foreign keys must match unique key degree exactly

Bug #89014	Foreign keys must match unique key degree exactly
Submitted:	21 Dec 2017 12:58	Modified:	22 Dec 2017 18:45
Reporter:	Lukas Eder	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	8.0.2	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, SQL Standard, unique key

Description:
In MySQL, it is possible for a foreign key that contains M columns to match a referenced primary key of N columns with N > M. This is against the SQL standard and completely voids the concept of a foreign key in such situations.

A foreign key should guarantee that a value from the referencing table is contained exactly once in the referenced table.

How to repeat:
-- T1 has a composite primary key
CREATE TABLE t1 (
  a BIGINT NOT NULL,
  b BIGINT NOT NULL,
  
  CONSTRAINT pk_t1 PRIMARY KEY (a, b)
);

-- T2's foreign key references only one column of T1's primary key
CREATE TABLE t2 (
  a BIGINT NOT NULL,
  
  CONSTRAINT fk_t2_t1 FOREIGN KEY (a) REFERENCES t1 (a)
);

-- These are correct inserts into t1, as they are not duplicates:
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (1, 2);

-- This now references two rows in T1 (!)
INSERT INTO t2 VALUES (1);

----
Combine the above with an ON DELETE CASCADE rule and you will see that any of the referenced rows will trigger the cascade.

Suggested fix:
Raise an error when such a foreign key is specified - at least in MySQL's strict mode, this should not be permitted.

For the record, the relevant clause in the latest ISO/IEC 9075-2:2016(E) chapter 11.8 <referential constraint definition> is

Syntax Rules

11) The <referencing column list> shall contain the same number of <column name>s as the <referenced column list>. The i-th column identified in the <referencing column list> corresponds to the i-th column identified in the <referenced column list>...

Hi!

Thank you for your report. Currently, we are supporting the only valid SQL standard, which is SQL:2011.

However, I agree that we should prepare for the future standards. Current behaviour will not be changed soon as it would break many existing applications.

Still, this is a fully valid feature request, hence it is verified as such.

I just quoted the latest version of the standard that I have in my file system. This part of the standard has always been exactly this way, see e.g. SQL-92: https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt