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.
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".