关于删除限制 SQL 帮助
我有以下两个表：
Owner:
O_ID P_ID
Bob Sam
Steve Rex
Pets
P_ID O_ID
Sam Bob
Rex Steve
所有者的第二列 (P_ID) 是宠物的第一列 (P_ID) 的外键。
在 DB2 中，我尝试添加 ON_DELETE RESTRICT 约束，这样如果有人尝试删除所有者表中的所有者，如果该所有者是宠物的所有者，则删除操作将被拒绝。我知道我必须使用 ON_DELETE RESTRICT 命令，但我不知道如何执行此操作。
我已经尝试过：
ALTER TABLE OWNERS
ADD CONSTRAINT no_delete
FOREIGN KEY (P_ID)
REFERENCES PETS(P_ID)
ON DELETE RESTRICT
无济于事。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你的语法是正确的。你的逻辑是错误的。
如果所有者在“pets”表中拥有宠物，您希望防止删除该所有者。为此，您需要更改表“pets”，并添加引用表“owners”的外键约束。
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".
Catcall 是正确的，这是正确的 ALTER TABLE 命令（在 DB2 LUW v9.7 中测试）：
然后，当我尝试使用以下命令从所有者表中删除 Steve 时：
正如预期的那样，我收到了：
然后为了确保它完全按预期工作，我删除了 Steve 的狗：
并重新尝试从所有者表中删除 Steve，结果成功了！
Catcall is correct, here's the correct ALTER TABLE command (tested in DB2 LUW v9.7):
Then when I tried to remove Steve from the owner table with the following command:
I received, as expected:
Then to make sure this is working completely as expected, I erased Steve's dog:
And re-ran the attempt to remove Steve from the owner table, and it worked!