3.修改约束

3.1给约束命名

按如下格式命名：

name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY

gender CHAR(1) CONSTRAINT NoAndro

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

CONSTRAINT RightTitle

    CHECK (gender = 'F' or name NOT LIKE 'Ms.%')

3.2 修改表上的约束

①修改约束的检查时间

SET CONSTRAINT MyConstraint DEFERRED;

SET CONSTRAINT MyConstraint IMMEDIATE;

②用ALTER TABLE语句

删除约束 DROP

ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;

ALTER TABLE MovieStar DROP CONSTRAINT NoAndro;

ALTER TABLE MovieStar DROP CONSTRAINT RightTitle;

添加约束 ADD（注意，约束必须是要整个表遵守时才加）

ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey

    PRIMARY KEY(name);

ALTER TABLE MovieStar ADD CONSTRAINT NoAndro

    CHECK (gender IN ('F','M'));

ALTER TABLE MovieStar ADD CONSTRAINT RightTitle

    CHECK (gender = 'F' OR name NOT LIKE 'Ms.%');

注意：SQL不会记住已经删除的约束，所以如果要添加之前删除的约束必须重新写一遍。

四、断言

1.创建断言

CREATE ASSERTION 断言名 CHECK (条件)

2.使用断言

CREATE ASSERTION RichPres CHECK

    (NOT EXISTS

        (SELECT Studio.name

         FROM Studio, MovieExec

         WHERE presC# = cert# AND netWorth < 1000000

        )

    );

上面的断言不允许资金小于1000000的经理。

CREATE ASSERTION SumLength CHECK (10000 >= ALL

    (SELECT SUM(length) FROM Movies GROUP BY studioName)

);

上面的断言不允许电影公司的电影总长度超过10000分钟。

如果这个语句用CHECK约束写，是

CHECK ( 10000 >= ALL

    (SELECT SUM(length) FROM Movies GROUP BY studioName));

断言和CHECK的区别：

CHECK只在插入和修改时有效，但对删除不做检查，在有些情况下会导致约束不成立。

断言则在任何提及的关系改变时都会检查，确保条件成立。

3.删除断言

DROP ASSERTION 断言名

五、触发器

当特定事件发生时，触发器被激活。如果状态满足触发器中的条件，则该触发器相连的动作被执行。

例子：

1.不允许降低电影制作人的净资产

CREATE TRIGGER NetWorthTrigger

AFTER UPDATE OF netWorth ON MovieExec

REFERENCING

    OLD ROW AS OldTuple,

    NEW ROW AS NewTuple

FOR EACH ROW

WHEN (OldTuple.netWorth > NewTuple.netWorth)

    UPDATE MovieExec

    SET netWorth = OldTuple.netWorth

    WHERE cert# = NewTuple.cert#;

2.阻止电影制作人的平均资产降到500000.要对插入、删除、修改分别设置触发器。这里列出修改的：

CREATE TRIGGER AvgNetWorthTrigger

AFTER UPDATE OF netWorth ON MovieExec

REFERENCING

    OLD TABLE AS OldStuff,

    NEW TABLE AS NewStuff

FOR EACH STATEMENT

WHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec))

BEGIN

    DELETE FROM MovieExec

    WHERE (name, address, cert#, netWorth) IN NewStuff;

    INSERT INTO MovieExec

        (SELECT * FROM OldStuff);

END;

3.以year为主键的一部分，防止主键为空。在插入前如果发现year为空则插入默认值1915

CREATE TRIGGER FixYearTrigger

BEFORE INSERT ON Movies

REFERENCING

    NEW ROW AS NewRow

    NEW TABLE AS NewStuff

FOR EACH ROW

WHEN NewRow.year IS NULL

UPDATE NewStuff SET year = 1915;

触发

【SQL】约束与触发器2

3.修改约束

3.1给约束命名

3.2 修改表上的约束

四、断言

五、触发器