数据库的完整性是一块很重要的知识，涉及到表、视图等的各种约束，是保证数据库或项目正常运行的根本保证，也是继SQL语句后的第二块重要的知识点。

完整性主要包括：实体完整性，参照完整性和用户自定义完整性。当然，在dbms下，用户接触最直接的就是用户自定义完整性和外键的参照完整性，所以下面的实验主要以后两者为主。

用户自定义完整性

check语句

使用check子句定义如下约束条件:
• 读者证件只能是“身份证”或“护照”；
• 读者手机号是以“1”打头的11位字符,后10位只能是数字。

解决方案

由于mysql不支持check方法，所以为了完成本题的要求，我对各自问题编写了一个触发器以实现所需功能。

Q. 读者证件只能是“身份证”或“护照”
A.

CREATE DEFINER=`root`@`localhost` TRIGGER `readers_BEFORE_INSERT` 
BEFORE INSERT ON `readers` FOR EACH ROW BEGIN
        if NEW.Card_name not in ('身份证' ,'护照') then 
            delete from readers where Card_name = new.Card_name;
        end if;
END

执行错误插入（Card_name=’其他’）如下：

Alt text

触发器功能启动，报错如下：

Alt text

执行正确插入（Card_name=’护照’）如下：

Alt text

语句成功执行：

Alt text

其实用check语句实现很容易，只需要在表的定义语句中加上
check Card_name in ('身份证' ,'护照')
即可实现。

Q. 读者手机号是以“1”打头的11位字符,后10位只能是数字
A. 由于mobile为varcher型，直接设置正则比较即可

CREATE DEFINER=`root`@`localhost` TRIGGER `readers_BEFORE_INSERT` BEFORE INSERT ON `readers` FOR EACH ROW BEGIN
        if NEW.mobile not  REGEXP '^[1][0-9]{10}$'  then 
            delete from readers where mobile = new.mobile;
        end if;END

执行错误插入（mobile=2…）如下：

Alt text

触发器功能启动，报错如下：

Alt text

触发器

编写触发器，实现以下约束
• 已挂失的借书证不能再借书；
• 向借阅表增加一个借书记录后，在图书表里该图书的出借数自动加1。

解决方案

Q. 已挂失的借书证不能再借书
A. 由题意可知，存在于loss_reporting表中的用户不能再向borrow表中插入数据，所以代码实现如下：

CREATE DEFINER=`root`@`localhost` TRIGGER `borrows_BEFORE_INSERT` BEFORE INSERT ON `borrows` FOR EACH ROW BEGIN
    if exists(select loss_reporting.reader_id
                                from loss_reporting
                                where(loss_reporting.reader_id = new.reader_id))
        then
        delete from borrows where reader_id = new.reader_id;
        end if;
END

Q. 向借阅表增加一个借书记录后，在图书表里该图书的出借数自动加1
A.

CREATE DEFINER=`root`@`localhost` TRIGGER `borrows_AFTER_INSERT` AFTER INSERT ON `borrows` FOR EACH ROW BEGIN
    if new.book_id in (select book_id from books)
    then
        update books
        set Quantity_out=Quantity_out+1
        where books.book_id=new.book_id;
    end if;
END

参照完整性

参照完整性决定了外键约束，即在引用表中外键的值只能设置为被引用表中的值或为null

自定义违约处理

将读者表的‘会员级别’外键的违约处理策略定义为置默认值“null”

解决方案

在删除或更新外键时，子表置空

ALTER TABLE `bookmis`.`readers` 
ADD CONSTRAINT `level`
  FOREIGN KEY (`level`)
  REFERENCES `bookmis`.`member_level` (`level`)
  ON DELETE SET NULL
  ON UPDATE SET NULL;