Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Foreign Key No Action

 Post Reply Post Reply
Author
Doctor Scott View Drop Down
New Member
New Member
Avatar

Joined: 01 Mar 2015
Posts: 16
Post Options Post Options   Quote Doctor Scott Quote  Post ReplyReply Direct Link To This Post 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.

Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2362
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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. 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down