Search code examples
sqldb2restrictalter

On delete restrict SQL help

I have the two following tables:

    Owner:

    O_ID P_ID
    Bob   Sam
    Steve Rex


    Pets

    P_ID O_ID
    Sam  Bob
    Rex  Steve

The second column of owners (P_ID) is a foreign key to the first column of pets (P_ID).

In DB2, I'm trying to add an ON_DELETE RESTRICT constraint, such that if someone were to try and delete an Owner in the owner table, if that owner were the owner of a pet, the delete operation would be rejected. I know I have to use the ON_DELETE RESTRICT command, but I'm at a loss of how to do so.

I've tried this:

ALTER TABLE OWNERS
ADD CONSTRAINT no_delete
FOREIGN KEY (P_ID)
REFERENCES PETS(P_ID)
ON DELETE RESTRICT

To no avail.

Solution

  • Your syntax is correct. Your logic is wrong.

    You want to prevent deleting an owner if it has a pet in the table "pets". To do that, you need to alter the table "pets", and add a foreign key constraint referencing the table "owners".