Another error that can be encountered in a database management system is the failure to drop a table due to foreign key constraints. The error message looks something like this:
Error description: The primary key 'pk_athlete_code' referred by a foreign key 'fk_game_athlete_code' is not supposed to be dropped.
This error happens when you try to drop a table that is referred to by a foreign key. In this case, the name fk_game_athlete_code is pretty obvious and you can find out from it that the table in question is named "game". However, the name of the foreign key can be anything, so if you want to find the table that contains the foreign key, run the command:
SELECT class_name FROM db_index WHERE index_name = 'fk_game_athlete_code';
In order to drop the constraint, use the DROP FOREIGN KEY clause:
ALTER TABLE game DROP FOREIGN KEY fk_game_athlete_code;
Repeat the procedure for all the foreign keys that you know have "ON DELETE RESTRICT" (which is the default setting). Then, simply try to delete the table again. If it fails due to another foreign key, repeat the above procedure.
To avoid this problem, use ON DELETE CASCADE option when creating the foreign keys.
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