数据库笔记（九）——对违反约束的处理

Posted on | Edited on

写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第四章的一些尾巴，然后开始讲第六章的关系代数
笔者接下来的代码示例会主要在SQL Server数据库中测试

在开始今天的摸鱼大业之前，让我们构造一些简单表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 执行下面的语句构造表
CREATE TABLE country(
  country_id INTEGER PRIMARY KEY ,
  country_name VARCHAR(20)
);

CREATE TABLE person(
  person_id INTEGER PRIMARY KEY ,
  name VARCHAR(20),
  country_id INTEGER FOREIGN KEY REFERENCES country(country_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

INSERT INTO country (country_id, country_name) VALUES (
    1, 'China'
);
INSERT INTO country (country_id, country_name) VALUES (
    2, 'English'
);
INSERT INTO country (country_id, country_name) VALUES (
    3, 'America'
);

INSERT INTO person (person_id, name, country_id) VALUES (
    1, 'Sunny', 1
);

INSERT INTO person (person_id, name, country_id) VALUES (
    2, 'Robbin', 2
);

INSERT INTO person (person_id, name, country_id) VALUES (
    3, 'Jane', 3
);

级联操作

在指定外键以后，由于存在完整性约束，所以在执行删除或更新的时候由于语句可能会破坏完整性约束而执行失败。因此可以在定义外键的时候声明为级联删除和级联更新(是一种对违反参照完整性约束时的处理方式)

  • 使用方式

    1
    2
    3
    4
    5
    6
    7
    CREATE table 表名(
      ...
      FOREGIN KEY (字段序列) REFERENCES 表名(字段序列)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
      ...
    )

  • 级联删除（ON DELETE CASCADE）

    • 级联删除是在定义外键时指定的，但是却会在执行删除语句时产生影响

    • 举个栗子

      • 我们先不指定级联

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        DROP TABLE person;
        DROP TABLE country;
        CREATE TABLE country(
          country_id INTEGER PRIMARY KEY ,
          country_name VARCHAR(20)
        );

        CREATE TABLE person(
          person_id INTEGER PRIMARY KEY ,
          name VARCHAR(20),
          country_id INTEGER FOREIGN KEY REFERENCES country(country_id)
        );

        INSERT INTO country (country_id, country_name) VALUES (
            1, 'China'
        );
        INSERT INTO country (country_id, country_name) VALUES (
            2, 'English'
        );
        INSERT INTO country (country_id, country_name) VALUES (
            3, 'America'
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            1, 'Sunny', 1
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            2, 'Robbin', 2
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            3, 'Jane', 3
        );

      • 然后执行下面的删除操作

        1
        2
        3
        -- 下面我们试图删除中国的信息，但是在person表里有一条数据引用了中国，所以因为参照完整性约束的存在，所以这条语句会执行失败
        DELETE country
        WHERE country_id = 1

      • 接下来我们重新构造一遍（当然直接用DDL语句更新也是可以的）–并在构造person表时指定了级联删除

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        DROP TABLE person;
        DROP TABLE country;
        CREATE TABLE country(
          country_id INTEGER PRIMARY KEY ,
          country_name VARCHAR(20)
        );

        CREATE TABLE person(
          person_id INTEGER PRIMARY KEY ,
          name VARCHAR(20),
          country_id INTEGER FOREIGN KEY REFERENCES country(country_id)
              ON DELETE CASCADE
        );

        INSERT INTO country (country_id, country_name) VALUES (
            1, 'China'
        );
        INSERT INTO country (country_id, country_name) VALUES (
            2, 'English'
        );
        INSERT INTO country (country_id, country_name) VALUES (
            3, 'America'
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            1, 'Sunny', 1
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            2, 'Robbin', 2
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            3, 'Jane', 3
        );

      • 然后再次执行下面的删除操作

        1
        2
        3
        -- 此时执行会发现语句成功执行了，不但删除了中国的信息，连带person表中引用了中国信息的所有数据都被删除了
        DELETE country
        WHERE country_id = 1

    • 上面的例子便很好的说明了级联删除的作用。如果我们视图删除外键参照键所在表（此处为country表）的某条数据A（此处是中国的信息），而这条数据又被外键所在表的一条或多条数据B所关联（此处person表中Sunny的country_id关联了country表中中国的id）。在指定了级联删除的情况下，删除A会连带着删除所有满足条件的B

    • 当然在实际使用的时候用的还是比较少的，因为参照完整性约束在一定程度上可以防止数据的误删除，对数据库的完整性起了一定的保护作用，如果指定了级联删除，这层保护就失效了。所以还是视情况而用

  • 级联更新（ON UPDATE CASCADE）

    • 类似的，级联更新和级联删除一样，如果我们更新时违反了完整性约束，同样更新操作不被拒绝，而是级联更新

    • 举个栗子（我们在上面操作的基础上执行，上面构造时指定了级联删除，但是没指定级联更新）

      1
      2
      3
      4
      -- 我们试图执行下面的更新操作，我们把修改English的country_id， 但是由于person表中还有数据的country_id=2,如果下面的更新成功执行，则会导致person表中存在country_id=2的数据，而country中却没有对应数了，违反参照完整性约束，故下面的语句执行失败
      UPDATE country
      SET country_id = 4
      WHERE country_id = 2

    • 同样的，我们重新构造一下，此时指定级联更新

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      DROP TABLE person;
      DROP TABLE country;
      CREATE TABLE country(
        country_id INTEGER PRIMARY KEY ,
        country_name VARCHAR(20)
      );

      CREATE TABLE person(
        person_id INTEGER PRIMARY KEY ,
        name VARCHAR(20),
        country_id INTEGER FOREIGN KEY REFERENCES country(country_id)
          ON DELETE CASCADE
          ON UPDATE CASCADE
      );

      INSERT INTO country (country_id, country_name) VALUES (
          1, 'China'
      );
      INSERT INTO country (country_id, country_name) VALUES (
          2, 'English'
      );
      INSERT INTO country (country_id, country_name) VALUES (
          3, 'America'
      );

      INSERT INTO person (person_id, name, country_id) VALUES (
          1, 'Sunny', 1
      );

      INSERT INTO person (person_id, name, country_id) VALUES (
          2, 'Robbin', 2
      );

      INSERT INTO person (person_id, name, country_id) VALUES (
          3, 'Jane', 3
      );

    • 此时再执行一下上面的更新语句

      1
      2
      3
      4
      -- 由于指定了级联更新，所以会发现下面的语句执行成功了，不但更改了country表中的数据，连带着person表中的数据也一并更新了
      UPDATE country
      SET country_id = 4
      WHERE country_id = 2

    • 上面就是级联更新的效果

  • 另一类对违反完整性约束的处理

    • SET DEFAULT
      • 一旦违反完整性约束，就将参照域（此处为country_id）设置为默认值
    • SET NULL
      • 一旦违反完整性约束，就将参照域（此处为country_id）设置为NULL

    • 举个栗子

      • 执行下面的构造

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        DROP TABLE person;
        DROP TABLE country;
        CREATE TABLE country(
          country_id INTEGER PRIMARY KEY ,
          country_name VARCHAR(20)
        );

        CREATE TABLE person(
          person_id INTEGER PRIMARY KEY ,
          name VARCHAR(20),
          country_id INTEGER FOREIGN KEY REFERENCES country(country_id)
            ON DELETE SET NULL
        );

        INSERT INTO country (country_id, country_name) VALUES (
            1, 'China'
        );
        INSERT INTO country (country_id, country_name) VALUES (
            2, 'English'
        );
        INSERT INTO country (country_id, country_name) VALUES (
            3, 'America'
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            1, 'Sunny', 1
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            2, 'Robbin', 2
        );

        INSERT INTO person (person_id, name, country_id) VALUES (
            3, 'Jane', 3
        );

      • 然后执行下面的删除操作

        1
        2
        3
        -- 下面的删除操作成功执行，但不是级联删除，而是把person表中原来country_id=1的数据的country_id都设成了NULL
        DELETE country
        WHERE country_id = 1

延迟检查

这是由于数据库默认是在执行每一条SQL语句的时候都进行完整性约束的检查，导致有些操作无法进行。延迟操作就将完整性约束的检查延迟到了事务结束的时候检查（大多数数据库不支持，比如SQL Server， 但Oracle数据支持）

  • 由于不常用，SQL Server也不支持，这里就讲一下概念，不举实际的栗子了。

  • 假设上面的例子表中没有指定延迟检查

    • 执行下面的语句
      1
      2
      3
      4
      5
      6
      7
      -- 执行下面两条语句是会出错的，因为插入第一条数据的时候，由于完整性约束的存在，要求country表中要有country_id=4的数据，但是这个数据目前还不存在（所以只要先执行第二条语句，这两个语句才能成功执行）
      INSERT INTO person (person_id, name, country_id) VALUES (
          4, 'Jerry', 4
      );
      INSERT INTO country (country_id, country_name) VALUES (
          4, 'France'
      );

  • 而如果指定了延迟检查呢

    • 执行下面语句（下面两个语句处于同一个事务中）
      1
      2
      3
      4
      5
      6
      7
      8
      -- 由于是延迟检查，所以两条数据都插入完，执行commit，事务结束时才进行完整性约束的检查，此时就不会出错，可以正常插入
      INSERT INTO person (person_id, name, country_id) VALUES (
          4, 'Jerry', 4
      );
      INSERT INTO country (country_id, country_name) VALUES (
          4, 'France'
      );
      COMMIT

  • 虽然SQL标准中有这个概念，但是大多数数据库没有提供支持，并且不常用

坚持原创技术分享，您的支持将鼓励我继续创作！