T N T

美食博主

0%

$6 数据完整性约束

Posted on Edited on In

本节记录数据完整性约束

外键

  1. FORIGN KEY 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    (root@localhost) [book]> create table borrow(
        -> borrow_id int not null auto_increment,
        -> stu_id int not null,
        -> b_id int not null,
        -> t_time datetime not null,
        -> b_time datetime,
        -> primary key(borrow_id),
        -> foreign key(stu_id) references student(stu_id) on delete cascade on update cascade,
        -> foreign key(b_id) references book(b_id) on delete restrict on update restrict);
    Query OK, 0 rows affected (0.15 sec)



    RESTIRICT : 限制策略，违反即拒绝，默认
    CASCADE   : 级联策略，根据参照表自动更新本表
    SET NULL  : 置空策略
    NO ACTION : 不采取实施策略

约束

  1. CHECK约束 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    (root@localhost) [book]> create table test_create(
        -> id int not null auto_increment primary key,
            -> name char(10),
                -> sex char(1) check(sex='男' or sex='女'));
    Query OK, 0 rows affected (0.29 sec)

    (root@localhost) [book]> insert into test_create values
        -> (null, 'name', '中');
        ERROR 3819 (HY000): Check constraint 'test_create_chk_1' is violated.

  2. ENUM枚举约束 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    (root@localhost) [book]> create table test_copy(
        -> id int not null auto_increment primary key,
        -> name char(10),
        -> sex enum('男', '女'));
    Query OK, 0 rows affected (0.09 sec)

    (root@localhost) [book]> insert into test_copy values
        -> (null, 'test', '中');
    ERROR 1265 (01000): Data truncated for column 'sex' at row 1

  3. ALTER TABLE table ADD CONSTRAINT constraint 增加约束 

    1
    2
    3
    4
    (root@localhost) [book]> alter table test_create
        -> add constraint c_sex check(sex='男' or sex='女');
    Query OK, 0 rows affected (0.11 sec)
    Records: 0  Duplicates: 0  Warnings: 0