Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Update / Delete Operations Restriction Error


If you try to update or delete values from a field that is referred by a foreign key that is set to ON UPDATE RESTRICT and/or ON DELETE RESTRICT, then you will encounter a restriction error.

The error looks like this:
Error description: Update/Delete operations are restricted by the foreign key 'fk_game_athlete_code'.

This is a normal behavior for all database management systems. There are 2 ways to handle this error if you actually want to update or delete the values. 

First option is to use ALTER TABLE CHANGE statement to change the restrictions to ON DELETE CASCADE (or SET NULL or NO ACTION instead of CASCADE) and ON UPDATE NO ACTION (or SET NULL). More details can be found in the Constraint Definition manual page.

Second approach implies removing the foreign key constraint completely using DROP FOREIGN KEY.

Regardless of the case though, you still need to find the table which contains the fk_game_athlete_code foreign key.

SELECT class_name FROM db_index WHERE index_name = 'fk_game_athlete_code';

get_table.png

If you want to drop the constraint, use the DROP FOREIGN KEY clause:

ALTER TABLE game DROP FOREIGN KEY fk_game_athlete_code;

If you want to just modify the ON UPDATE or ON DELETE constraint values, the simplest way is to drop the foreign key using the DROP FOREIGN KEY command and then add it again using the ADD CONSTRAINT clause. Here is an example for the game table:

ALTER TABLE game ADD CONSTRAINT FOREIGN KEY ("athlete_code") REFERENCES "athlete"("code") ON DELETE CASCADE ON UPDATE NO ACTION
comments powered by Disqus
Page info
viewed 1347 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article