Description:
SQL standard states ( paragraph 11.8.4, cases a) and b). ):
a) If the <referenced table and columns> specifies a <referenced column list>, then there shall be a one- to-one correspondence between the set of <column name>s contained in that <referenced column list> and the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table such that corresponding <column name>s are equivalent. Let referenced columns be the column or columns identified by that <referenced column list> and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once.
b) Otherwise, the table descriptor of the referenced table shall include a unique constraint UC that specifies PRIMARY KEY. The table constraint descriptor of UC shall not include an application time period name. Let referenced columns be the column or columns identified by the unique columns in that unique constraint and let referenced column be one such column. The <referenced table and columns> shall be considered to implicitly specify a <referenced column list> that is identical to that <unique column list>.
MySQL currently allow for referenced side to be any key and not only unique one
How to repeat:
create table sup(id varchar(4), key(id));
create table sub(id varchar(4), key(id),foreign key (id) references sup(id));
Suggested fix:
similar to the option for ONLY_FULL_GROUP_BY but it would be something like --fk_only_full_unique.
It needs to be an option so that we can deprecate our current "loose" behavior over time and give our users the chance to test/migrate their systems before taking away our old behavior.
version A - option exists but defaults to off
next version - option defaults to on and is deprecated
next version - only unique FK references are allowed, option removed.
© 2025, Oracle Corporation and/or its affiliates
Description: SQL standard states ( paragraph 11.8.4, cases a) and b). ): a) If the <referenced table and columns> specifies a <referenced column list>, then there shall be a one- to-one correspondence between the set of <column name>s contained in that <referenced column list> and the set of <column name>s contained in the <unique column list> of a unique constraint of the referenced table such that corresponding <column name>s are equivalent. Let referenced columns be the column or columns identified by that <referenced column list> and let referenced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. b) Otherwise, the table descriptor of the referenced table shall include a unique constraint UC that specifies PRIMARY KEY. The table constraint descriptor of UC shall not include an application time period name. Let referenced columns be the column or columns identified by the unique columns in that unique constraint and let referenced column be one such column. The <referenced table and columns> shall be considered to implicitly specify a <referenced column list> that is identical to that <unique column list>. MySQL currently allow for referenced side to be any key and not only unique one How to repeat: create table sup(id varchar(4), key(id)); create table sub(id varchar(4), key(id),foreign key (id) references sup(id)); Suggested fix: similar to the option for ONLY_FULL_GROUP_BY but it would be something like --fk_only_full_unique. It needs to be an option so that we can deprecate our current "loose" behavior over time and give our users the chance to test/migrate their systems before taking away our old behavior. version A - option exists but defaults to off next version - option defaults to on and is deprecated next version - only unique FK references are allowed, option removed.