On Tue, 28 Feb 2017 11:42:23 -0500, "James K. Lowden"
<jklow...@schemamania.org> wrote:
> I have always thought af a foreign key as an existence test. Looking
> around, I see that other implementations also often require the
> referent to be unique. I'm not convinced that's justified
> theoretically, but at the moment I can't check against my usual
> resources.
Here you go:
Information Technology - Database Language SQL
(Proposed revised text of DIS 9075)
(Second Informal Review Draft) ISO/IEC 9075:1992,
Database Language SQL- July 30, 1992
which is pretty close to the final text.
X3H2-92-154/DBL CBR-002 (page 274 or thereabout)
11.8 <referential constraint definition>
Function
Specify a referential constraint.
Format
<referential constraint definition> ::=
FOREIGN KEY <left paren> <referencing columns> <right paren>
<references specification>
<references specification> ::=
REFERENCES <referenced table and columns>
[ MATCH <match type> ]
[ <referential triggered action> ]
<match type> ::=
FULL
| PARTIAL
<referencing columns> ::=
<reference column list>
<referenced table and columns> ::=
<table name> [ <left paren> <reference column list> <right paren> ]
<reference column list> ::= <column name list>
<referential triggered action> ::=
<update rule> [ <delete rule> ]
| <delete rule> [ <update rule> ]
<update rule> ::= ON UPDATE <referential action>
<delete rule> ::= ON DELETE <referential action>
<referential action> ::=
CASCADE
| SET NULL
| SET DEFAULT
| NO ACTION
Syntax Rules
1) Let referencing table be the table identified by the containing
<table definition> or <alter table statement>. Let referenced
table be the table identified by the <table name> in the <refer-
enced table and columns>. Let referencing columns be the column
or columns identified by the <reference column list> in the
<referencing columns> and let referencing column be one such
column.
2) Case:
a) If the <referenced table and columns> specifies a <reference
column list>, then the set of column names of that <refer-
ence column list> shall be equal to the set of column names
in the unique columns of a unique constraint of the refer-
enced table. Let referenced columns be the column or columns
identified by that <reference column list> and let refer-
enced 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) If the <referenced table and columns> does not specify a
<reference column list>, then the table descriptor of the
referenced table shall include a unique constraint that spec-
ifies PRIMARY KEY. Let referenced columns be the column or
columns identified by the unique columns in that unique con-
straint and let referenced column be one such column. The
<referenced table and columns> shall be considered to implic-
itly specify a <reference column list> that is identical to
that <unique column list>.
3) The table constraint descriptor describing the <unique con-
straint definition> whose <unique column list> identifies the
referenced columns shall indicate that the unique constraint is
not deferrable.
--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
