【SQL】约束与触发器1

一.外键

1.1特点

表A的外键，一定是其他某个表B的主键或有UNIQUE声明的属性。

A的外键的值，一定是对应表B中相应的属性值。（空值除外）

1.2声明方法

方法1：属性名类型 REFERENCES 表名（属性名）

方法2：FOREIGN KEY (属性名) REFERENCE 表名（属性名）

CREATE TABLE Studio(
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    persC# INT REFERENCES MovieExec(cert#)
);

CREATE TABLE Studio(
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    presC# INT,
    FOREIGN KEY (persC#) REFERENCES MovieExec(cert#)
);

1.3 引用完整性

设表A的外键，是表B的主键或UNIQUE。则，下面的四种情况出错：

1.A中插入的新元组（非空值），其外键不是B中的值

2.A中修改的新元组（非空值），其外键不是B中的值

3.删除B中的元组，其主键非空，且是A的外键

4.修改B中的元组的主键，且是A的外键

其中1,2一定不能违反。3,4可以有3种处理方法：

1.缺省原则：拒绝违反更新。

2.级联原则：如果把B中的主键删除，则删除对应A中的元组；如果修改B中的主键，也修改A中相应的外键

3.置空值原则：如果对B的主键的修改，影响到了A中的外键，则把A中的相应外键置空。

声明方法：

ON DELETE或ON UPDATE后面加上SET NULL或CASCADE

CREATE TABLE Studio(
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    presC# INT REFERENCES MovieExec(cert#)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

上面语句表示，删除时采用置空值原则，更新时采用级联原则。

1.4延迟约束检查

如果有循环约束，比如表A{属性a unique}, B{属性b unique}。a是外键，引用b；且b是外键，引用a。则这是一个循环约束。不管以怎样的顺序插入都会违反外键约束。解决方法：

1.将两个插入动作组成一个事务

2.通知DBMS不用检查其约束，直到整个事务完成执行并要提交为止。

为做到第2点，可以使用如下语句：

约束后面加： DEFERRABLE(延迟)或NOT DEFERRABLE(非延迟默认)

DEFERRBLE后面可以有INITIALLY DEFERRED(检查推迟到事务提交前执行)，INITIALLY IMMEDIATE(立即执行检查？？跟NOT DEFERRABLE区别？？)

如果约束有名字，如MyConstraint可以用

SET CONSTRAINT MyConstraint DEFERRED; 设为延迟检查

SET CONSTRAINT MyConstraint IMMEDIATE; 设为立即检查

CREATE TABLE Studio(
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    persC# INT UNIQUE
        REFERENCES MovieExec(cert#)
        DEFERRABLE INITIALLY DEFERRED
);

二、属性和元组上的约束

2.1 属性上的约束

①非空值约束：不允许该属性为空值。方法：在CREATE TABLE的属性声明后面用NOT NULL

persC# INT REFERENCES MovieExec(cert#) NOT NULL

注意：会导致外键约束中的置空原则不可用。

②基于属性的CHECK约束

presC# INT REFERENCES MovieExec(cert#)
        CHECK (presC# >= 100000)

上述语句保证presC#至少有6位

gender CHAR(1) CHECK (gender IN ('F', 'M'))

上述语句保证gender只能取‘F’，‘M’两种情况。

presC# INT CHECK 
        (presC# IN (SELECT cert# FROM MovieExec))

上述语句跟前面的外键约束很像，但是有不同。

不同点：1.该语句使得presC#不能为空，而外键约束可以（置空值原则） 2.该检查只在插入时起作用，但是后面对presC#修改导致违反该约束时，该约束无法检查出来。（这样的效率高，如果要严谨则不要这么写）

2.2基于元组的CHECK约束

涉及元组的多个属性的约束，要用基于元组的约束。

例，禁止把一个男性Male的名称以Ms.开头。

则CHECK中的应该是非（Male && Ms.） = 非（Male） or 非（Ms.）

CREATE TABLE MovieStar(
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1),
    birthdate DATE,
    CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
);

与基于属性的CHECK约束相比，基于元组的CHECK约束检查的更频繁，只要该元组的任一个属性被改变，都要检查。而基于属性的CHECK约束只有在相应的属性变化时才检查。