SQL:如何在删除某些内容时更改数据库？

Question

所以我的作业中有以下问题，我已经坚持了一段时间:

当员工S被删除

我。将 Employee 和 Department 中的 FK 设置为 null

二。删除 Works_On 和 Dependent 中引用的行

我一直在考虑为此使用 CASE 语句，但我不确定应该如何构建 SQL。我正在使用 Postgresql。

以下链接是我的数据库结构截图: http://puu.sh/rMLjp/886c6e8a5b.png

Answer 1

你读过PostgreSQL manual on FOREIGN KEY constraints吗？ ？

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted.

例子:

CREATE TABLE works_on (
    essn ... REFERENCES employee(ssn) ON DELETE SET NULL,
    pno ...,
    hours ...
);

我无法从您的屏幕截图中猜出您的列的数据类型。但是手册的摘录和链接以及部分代码示例应该会为您提供您需要了解的信息。