Foreign key syntax improvements #999
Description
Currently, the persistent quasiquoter entity definition syntax allows you to define a foreign key with the Foreign keyword. The QQ definition and associated table are given here:
User
otherField String
Foreign OtherTable fkothertable otherField
==>
CREATE TABLE user (
id PRIMARY AUTOINCREMENT,
other_field TEXT NOT NULL,
FOREIGN KEY (other_field) REFERENCES other_table(id)
);
This syntax has some important restrictions:
- You can't specify a
CASCADEbehavior - You can only make a foreign key to the primary key of another table, while most SQL databases only require a
Uniquekey.
Proposed Solution:
Immediately, documentation should be updated with these restrictions, and also suggest a means to write these things in raw SQL to work-around the behavior.
Add a References keyword to the syntax. After this keyword, accept a non-empty list of field name identifies. If the keyword is missing, assume a reference to the primary key column to retain backwards compatibility.
Add a pair of keywords OnDelete and OnUpdate which accept another argument, one of NoAction, Restrict, or Cascade. These will translate to the expected migrations.
A user would then be able to write:
User
otherField String
Foreign OtherTable fkothertable otherField References name OnDelete Cascade OnUpdate Cascade
===>
CREATE TABLE user (
id PRIMARY AUTOINCREMENT,
other_field TEXT NOT NULL,
FOREIGN KEY (other_field) REFERENCES other_table(name) ON DELETE CASCADE ON UPDATE CASCADE
);