跳转至

Lec4 SQL完整性约束

阅读信息

528 字  2 分钟  本页总访问量 加载中...

SQL中的数据类型

用户自定义类型：

  • type：将已有的数据类型重命名，e.g.CREATE TYPE student_name AS VARCHAR(20)
  • domain：将已有的数据类型重命名并可加约束，e.g.CREATE DOMAIN dollars AS numeric(12, 2) NOT null

大型数据可用blob（二进制形式）和clob（字符形式）存储。数据库中存储文件的路径；或存储文件的元数据（主要是文件名），在应用程序中配置路径。

完整性约束

完整性约束包括：

  • NOT null
  • PRIMARY KEY
  • UNIQUE
  • CHECK (P)

E.g.，在定义domain时加CHECK约束：

SQL
1
2
CREATE DOMAIN hourly_wage NUMERIC(5, 2)
CONSTRAINT value_test CHECK(value >= 4.00)

参照完整性约束：在一个关系中作为外键（foreign key）的属性值，必须在另一个关系的主键（primary key）中出现过。被参照关系中，可任意插入；但参照关系中插入时，要检查是否在被参照关系中。删除时检查，选择不删除或级联删除。

外键的SQL语句，外键account_number指向account的主键：

SQL
1
FOREIGN KEY (account_number) REFERENCES account

后面可加ON DELETE CASCADE或ON UPDATE CASCADE，表示可级联删除或更新；加ON DELETE SET NULL，表示删除时设为null；ON DELETE DEFAULT，表示删除时设为该属性的默认值。

断言 Assertion

创建断言：CREATE ASSERTION assertion_name CHECK predicate

触发器 Trigger

Trigger由condition和action两部分组成。创建触发器格式：CREATE TRIFFER name AFTER predicate

Trigger示例

银行存款为负时，设置存款为零、贷款为负数部分。

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
CREATE TRIGGER overdraft_trigger
AFTER UPDATE ON account
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN nrow.balance < 0
BEGIN ATOMIC
    INSERT INTO loan -- 根据外键约束，要先插入loan
    VALUES (nrow.account_number, nrow.branch_name, -nrow.balance);

    INSERT INTO borrower (
        SELECT customer_name, account_number
        FROM depositor
        WHERE nrow.account_number = depositor.account_number
    );

    UPDATE account
    SET balance = 0
    WHERE account.account_number = nrow.account_number;
END;

有时需要用触发器监测外部变化。由于trigger只能作用于表，需要新建表来表示这种外部关系。

权限管理

  • 数据方面权限：read，insert，update，delete
  • schema方面的权限：index，resources，alteration，drop

view用于隐藏部分数据，可和权限管理结合。有view的权限，不一定有view底层表的权限。

权限的传递用图表示。DBA（database administrater）有最高权限，所有通路从DBA出发；箭头从A指向B，表示A给B授权。权限回收时，下游的权限级联回收。

授权：GRANT privilege_list ON table_or_view TO user_list

  • privilege_list可以选insert, update, delete, reference等，或all privilege表示所有权限
  • user_list可以选部分id、public（慎用，后续权限都对所有用户有效）、角色role
  • 可加后缀with grant option，表示允许将权限传递给下层用户

回收权限：REVOKE privilege_list ON table_or_list FROM user_list [RESTRICT/CASCADE]

  • 如果user_list中写public，则所有用户失去通过public授权得到的权限，但不用public授权的权限不被回收
  • 如果权限有级联传递，回收时无法仅对上游权限restrict回收

审计Audit