tsql icon indicating copy to clipboard operation
tsql copied to clipboard

Add foreign key information to `SchemaMeta` and check it with `validateSchema/Table/Column()`

Open AnyhowStep opened this issue 5 years ago • 1 comments
trafficstars

We might have the following declaration on application code,

const myTable = tsql.table("myTable")
  .addColumns({
    myTableId : tsql.dtBIgIntSigned(),
  })
  .addMutable(columns => [columns.myTableId]);

But we might have a foreign key on the database that references myTable.myTableId... with ON UPDATE RESTRICT.

In this case, when validateSchema/Table/Column() is called, we should get a warning (not error) that says myTable.myTableId should not be mutable because of a foreign key that has ON UPDATE RESTRICT.

For MySQL 5.7, it should say its ON UPDATE is RESTRICT if it sees NO ACTION, because NO ACTION is the same as RESTRICT on MySQL 5.7.


We want it to be a warning and not an error because it's possible for no rows to be referencing a particular myTable.myTableId. In such a case, updating the value is fine.

AnyhowStep avatar Dec 13 '19 21:12 AnyhowStep

https://www.postgresql.org/docs/9.5/ddl-constraints.html

https://sqlite.org/foreignkeys.html

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

AnyhowStep avatar Dec 13 '19 21:12 AnyhowStep