Create Table Foreign Key Clause Syntax Diagram Issue?

(1) By anonymous on 2024-03-14 20:48:08 [link] [source]

https://www.sqlite.org/lang_createtable.html

Create-table-stmt / column-def / column-constraint / foreign-key-clause:

The diagram for foreign key clause (column) shows that the syntax allows a comma separated list of column-names which doesn't seem to make sense at the column level, rather it does make sense that it is allowed at the table level.

(2) By Gunter Hick (gunter_hick) on 2024-03-18 12:03:51 in reply to 1 [source]

Not all tracks on a railroad may be used for all kinds of train movement.

Similarly, foreign key constraints need matching tuples on both ends, which forces a column's foreign key constraint to reference exactly one field in the foreign table; whereas a table level foreign key constraint allows a tuple of fields from the local table to refer to a same size and type tuple of the foreign table.

(3) By anonymous on 2024-04-01 21:04:53 in reply to 2 [link] [source]

The train may be on the wrong track.

As was pointed out by the original poster, the documentation issue is related only to column-level FK constraints, which as you said are limited to only one field. But the docs imply a comma-delimited list of columns are allowed.

(4) By Richard Hipp (drh) on 2024-04-01 22:21:37 in reply to 3 [link] [source]

Let's make a deal: If you will create an account on the forum, and stop posting as anonymous, I'll create a whole new rail-road diagram that works just for column foreign key constraints.

(5) By anonymous on 2024-04-01 22:40:05 in reply to 3 [link] [source]

There are always additional constraints; the railroad diagrams can't specify everything (unless you want to complicate them with lots of dashed arrows meaning "⇠ this list must have the same number of elements as that list ⇢").

(6) By Gunter Hick (gunter_hick) on 2024-04-02 05:30:22 in reply to 3 [link] [source]

The raildroad diagrams provide a quick, simplified, visual overview of the syntax. Nothing more.

One has to actually read the documentation. Just looking at the pictures may cause your mind to go off on the wrong track and collide head on with reality.

A foreign key is a tuple within one table that refers to a unique row of another table. While the tuple may consist of only one field - in which case the foreign key may be specified while declaring said field - it is also possible that more than one field ist required; the latter case necessitating a separate foreign key declaration.

(7) By Jack (JackLite) on 2024-04-02 08:30:26 in reply to 3 [link] [source]

Then in create-table-stmt / table-constraint / FOREIGN KEY [column-names] foreign-key-clause, the diagram also imply the count of FOREIGN KEY column-names can be different from the REFERENCES foreign-table column-names. How do you argue that? 😄

(8) By Larry Brasfield (larrybr) on 2024-04-02 15:27:58 in reply to 7 [link] [source]

... How do you argue that?

The syntax diagrams capture what the parser accepts. The parse result is then subject to semantic analysis and VDBE code generation, rules of which are not conveyed by the syntax constraints. If those rules were so captured, I doubt the diagrams would be useful to human beings.