Foreign Key No Action
Joined: 01 Mar 2015
Topic: Foreign Key No Action
Posted: 01 May 2015 at 9:49pm
Regarding using Data Modeler on a SQLite database, but probably pertinent to any of the other databases.
If I create a relationship between two tables, and set the action on update or on delete to NO ACTION, the SQL generated to create the table contains the following:
CONSTRAINT Businesses_People FOREIGN KEY (People_Biz_ID) REFERENCES Businesses (Biz_PK) ON DELETE RESTRICT ON UPDATE RESTRICT
That is not correct. RESTRICT should be an option in the list of actions for ON DELETE and ON UPDATE, but not for NO ACTION. Effectively, NO ACTION should simple not include the ON DELETE and ON UPDATE qualifiers so the SQL should be:
CONSTRAINT Businesses_People FOREIGN KEY (People_Biz_ID) REFERENCES Businesses (Biz_PK)
Although RESTRICT is most commonly used, I need to be able to set a foreign key that does a true NO ACTION.
Easiest fix would be to simply make NO ACTION not use the ON DELETE and ON UPDATE in the SQL and add a RESTRICT option to the actions radio buttons on the relationship setup window.
Wagner R. Landgraf
Joined: 18 May 2010
|Post Options Quote Reply Posted: 07 May 2015 at 4:44pm|
We will look to fix it. Different is very subtle and many of major database vendors doesn't make a difference of both.
|Forum Jump||Forum Permissions
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum