数据库完整性

实验三数据库完整性
1、实体完整性：定义表的主码。

关系模型的实体完整性在CREATE TABLE 中用PRIMARY KEY定义。

定义主码的方法分为列级约束条件和表级约束条件。

【1-1】定义表STUDENT，将其中的SNO属性定义为主码
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
【1-2】定义表SC，将其中的SNO、CNO定义为主码
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno,Cno)
);
2、参照完整性：定义表的外码
关系模型的参照完整性是在CREATE TABLE中用FOREIGN KEY 语句来定义，并用REFERENCES来指明外码参照的是哪些表的主码。

【2-1】定义表SC，其中SNO参照STUDENT的主码SNO，CNO参照表COURSE的主码CNO
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
3、用户定义完整性
【3-1】列值非空。

在定义SC表时，SNO、CNO和GRADE属性都不允许取空值。

在不特别声明的情况下，非码属性的值允许取空值的
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT NOT NULL,
PRIMARY KEY(Sno,Cno)
);
【3-2】列值唯一。

建立部门表DEPT，要求部门名称Dname取值唯一，部门编号Deptno属性为主码
CREATE TABLE DEPT
(Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL,
Location CHAR(10),
PRIMARY KEY (Deptno)
);
【3-3】CHECK短语指定列值应该满足的条件。

定义表Student，属性Ssex的值只允许取“男”或“女”；定义表SC，属性Grade的值定义在0～100之间
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK(Ssex IN('男','女')),
Sage SMALLINT,
Sdept CHAR (20)
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT CHECK (Grade>=0 AND Grade<=100),
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
【3-4】用户定义的元组上的约束条件。

定义表Student，要求当学生性别为“男”时，其名字不能以Ms.打头
CREATE TABLE STUDENT(
SNO CHAR(7) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20),
CHECK (SSEX = ‘女’OR SNAME NOT LIKE ‘Ms.%’));
/*定义了Sname和Ssex之间的约束条件*/
【4-1】定义表Student，要求学号在90000～99999之间，姓名不能取空值，年龄小于30，性别只能“男”或“女”，要求全部用约束命名子句实现
CREATE TABLE Student
(Sno NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK(Sage <30),
Ssex CHAR(2)
CONSTRAINT C4 CHECK (ssex IN ('男','女')),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
【4-2】修改表Student中的完整性限制，去掉对性别的限制，并将年龄的限制由小于30改为小于40
ALTER TABLE STUDENT /*去掉对性别的限制条件C4*/
DROP CONSTRAINT C4;
ALTER TABLE STUDENT /*先删掉原来的约束条件再增加一个新的约束条件*/
DROP CONSTRAINT C3;
ALTER TABLE STUDENT ；
ADD CONSTRAINT C3 CHECK(SAGE <40);
【5-1】定义表TAB，并在其上定义触发器TRI，在对TAB的插入或更新前检查，如果插入或更新的值在100～1000之间的话，将值置为50；如果值大于1000的话，则将新值插入表。

CREATE TABLE tab (col INT);
INSERT INTO tab V ALUES(10);
INSERT INTO tab V ALUES(20);
CREATE TRIGGER TRI BEFORE UPDA TE OR INSERT ON tab
FOR EACH ROW
AS
BEGIN
IF new.col > 100 AND new.col < 1000 THEN
new.col := 50;
END IF;
IF new.col > 1000 THEN
INSERT INTO tab V ALUES(new.col);
END IF;
END;
【5-2】检测、执行、结果
检测：1）向表TAB插入数据（150）。

执行：INSERT INTO TAB V ALUES(150);
结果：插入的数据为150。

值在100－1000之间，触发器TRI自动执行，将插入的值置为50。

2）对表中的数据进行更新，将20更新为1500。

执行：UPDA TE TAB SET COL = 1500 WHERE COL = 20;
结果：插入的数据为1500，插入值为1500
【5-3】删除触发器TRI
DROP TRIGGER TRI ON TAB;
删除触发器TRI之后，在对TAB进行插入和更新就不再有以上限制了。