mysql完整性约束取值范围 sql中的完整性约束机制

　　

　　完整性约束的作用就是改变相应变量的值域，以满足自己的要求。完整性约束主要包括三个部分：单一关系上的约束（即一个表的约束），参照完整性（即表与表之间的约束）和断言。

（1）单一关系上的约束

　　单一关系上的完整性约束主要包括：

　　·not null

　　变量取值不能为空

　　·unique

　　独特的，限定属性不可以出现重复，形式为unique<A1,A2,...,An>

　　·check<谓语>

　　check子句可以用于关系声明和域声明。用于关系说明时，check子句指定的关系中的所有元组都必须满足check的谓语

　　令，check常可以与in搭配表示枚举。

　　注：check子句在MySQL中依然可以使用，但是没有意义，因为MySQL会自动忽略check的子句。

　　　　在MySQL中代替check功能的方式有两种：第一种是使用enum进行枚举，见下例2；第二种是使用触发器（trigger）的方法来实现，见下例3.

 

CREATE TABLE student(
id int not null,
student_name varchar(20),
age int,
degree char(15),
PRIMARY KEY(id),
CHECK(degree in ('Bachelors','Masters','Doctorate')),
CHECK(age > 18 AND age < 40)
);

执行创建的关系：

　　

例2

　　创建一个学位为枚举类型的关系

CREATE TABLE student (
student_id INT,
student_name VARCHAR(20),
student_birthday DATE,
student_degree ENUM('Masters','Bachelors','Doctorate'),
PRIMARY KEY(student_id)
);

创建的关系

　　

插入一个在枚举范围内和一个不在枚举范围内的值时：

INSERT INTO student 
VALUES(1, 'Jisoo', '1995-01-03', 'Masters');

INSERT INTO student 
VALUES(2, 'Lisa', '1997-03-27', 'Senior');

插入的结果为：

　　

　　 

 例3

　　创建关系和触发器

　　依旧令student_degree在三个枚举范围内，否则默认设置为‘Bachelors’

CREATE TABLE student (
student_id INT,
student_name VARCHAR(20),
student_birthday DATE,
student_degree VARCHAR(15),
PRIMARY KEY(student_id)
);

CREATE TRIGGER student_degree_trigger BEFORE INSERT ON student FOR EACH ROW 
BEGIN
        IF new.student_degree NOT IN ('Masters','Bachelors','Doctorate')
        THEN SET new.student_degree = 'Bachelors';
        END IF;
END

 　　插入一个在枚举范围内和一个不在枚举范围内的值时：

INSERT INTO student 
VALUES(1, 'Jisoo', '1995-01-03', 'Masters');

INSERT INTO student 
VALUES(2, 'Lisa', '1997-03-27', 'Senior');

插入的结果为

　　

（2）参照完整性

　　参照完整性的解释时一个关系中的属性的取值可能在别的关系中也出现，这就是参照完整性，又称为外码。

　　外码的表示方法是foreign key r references p。我们将正在创建的关系称为参照关系，p称为被参照关系。

　　创建一个带外码的关系：

CREATE TABLE score(
student_id INT NOT NULL,
chinese TINYINT,
math TINYINT,
FOREIGN KEY (student_id) REFERENCES student(student_id)
);

创建结果

　　

关联的是例3的关系表，我们插入两个新的元组

INSERT INTO score 
VALUES(1, 92, 93);

INSERT INTO student 
VALUES(2, 90, 90);

插入的结果为

　　

　　 

　违反参照完整性

　　当被参照的关系中的元组修改了自己的属性值，导致了约束的破坏，，那么参照关系也会做相应的修改

　　我们重新创建一个关系score,

CREATE TABLE score(
student_id INT NOT NULL,
chinese TINYINT,
math TINYINT,
FOREIGN KEY (student_id) 
REFERENCES student(student_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

ON DELETE CASCADE表示如果student中删除元组的操作会破坏参照完整性，那么score中也会删除相应的元组，这称为“级联”删除。

　　ON UPDATE CASCAD表示如果student中更新元组会导致破坏参照完整性，则，score中也会进行相应更新。（不再举例）

　　除了CASCADE以外我们还可以使用SET NULL和SET DEFAULT代替CASCADE,表示被参照关系的元组操作破坏了参照完整性时，参照关系会将相应属性设置为空或者默认值。

　添加完整性约束

　　我们在创建关系时没有创建相应的完整性约束时，后续可以通过alter table操作进行添加

　　添加完整性约束的命令为ALTER TABLE r ADD constraint

ALTER TABLE score ADD FOREIGN KEY(student_id) REFERENCES student(student_id);