数据库笔记07：实施数据完整性
【摘要】
/***************************
第七单元：实施数据完整性
***************************/
/* 预备任务：创建数据库“选课”，包含一个数据文件与一个日志文件 */
CREATE
DATABASE
选...
/***************************
第七单元：实施数据完整性
***************************/
/* 预备任务：创建数据库“选课”，包含一个数据文件与一个日志文件 */
CREATE DATABASE 选课
ON PRIMARY
(
NAME = 选课 _data ,
FILENAME = 'D:\选课_data.mdf' ,
SIZE = 5 MB ,
MAXSIZE = 50 MB ,
FILEGROWTH = 10 %
)
LOG ON
(
NAME = 选课 _log ,
FILENAME = 'D:\选课_log.ldf' ,
SIZE = 2 MB ,
MAXSIZE = 20 MB ,
FILEGROWTH = 2 MB
);
USE 选课
GO
/* 任务：创建数据表（CREATE TABLE） */
--1. 创建Department表（表设计参看P127表6-1）
CREATE TABLE Department
(
DepartNo nvarchar ( 2 ) NOT NULL ,
DepartName nvarchar ( 20 ) NOT NULL
);
--2. 创建Course表（表设计参看P127表6-2）
CREATE TABLE Course
(
CouNo nvarchar ( 3 ) NOT NULL ,
CouName nvarchar ( 30 ) NOT NULL ,
Kind nvarchar ( 8 ) NOT NULL ,
Credit decimal ( 2 , 1 ) NOT NULL ,
Teacher nvarchar ( 20 ) NOT NULL ,
DepartNo nvarchar ( 2 ) NOT NULL ,
SchoolTime nvarchar ( 10 ) NOT NULL ,
LimitNum decimal ( 5 , 0 ) NOT NULL ,
WillNum decimal ( 5 , 0 ) NOT NULL ,
ChooseNum decimal ( 5 , 0 ) NOT NULL
);
--3. 创建Class表（表设计参看P127表6-3）
CREATE TABLE Class
(
ClassNo nvarchar ( 8 ) NOT NULL ,
ClassName nvarchar ( 20 ) NOT NULL ,
DepartNo nvarchar ( 2 ) NOT NULL
);
--4. 创建Student表（表设计参看P127表6-4）
CREATE TABLE Student
(
StuNo nvarchar ( 8 ) NOT NULL ,
StuName nvarchar ( 10 ) NOT NULL ,
Pwd nvarchar ( 8 ) NOT NULL ,
ClassNo nvarchar ( 8 ) NOT NULL
);
--5. 创建StuCou表（表设计参看P127表6-5）
CREATE TABLE StuCou
(
StuNo nvarchar ( 8 ) NOT NULL ,
CouNo nvarchar ( 3 ) NOT NULL ,
WillOrder tinyint NOT NULL ,
State nvarchar ( 2 ) NOT NULL ,
RandomNum nvarchar ( 50 ) NULL
);
/******************************
第七单元有四项任务：
1、创建约束（删除约束）
（1）主键约束（PRIMARY KEY）
（2）外键约束（FOREIGN KEY...REFRENCES...）
（3）唯一约束（UNIQUE）
（4）检查约束（CHECK）
（5）默认约束（DEFAULT）
2、创建默认值（删除默认值）
3、创建规则
4、创建标识列
******************************/
/* 任务：创建主键约束 */
/*
语法格式：ADD CONSTRAINT <约束名> <约束类型> (约束列)
*/
--1. 给Department表的DepartNo字段创建主键约束
ALTER TABLE Department
ADD CONSTRAINT pk_department PRIMARY KEY ( DepartNo );
-- 大家可以打开Department表，输入两条记录，系部编号相同，单击保存按钮，会弹出错误提示框。
--2. 给Course表的CouNo字段创建主键约束
ALTER TABLE Course
ADD CONSTRAINT pk_course PRIMARY KEY ( CouNo );
--3. 给Class表的ClassNo字段创建主键约束
ALTER TABLE Class
ADD CONSTRAINT pk_class PRIMARY KEY ( ClassNo );
--4. 给Student表的StuNo字段创建主键约束
ALTER TABLE Student
ADD CONSTRAINT pk_student PRIMARY KEY ( StuNo );
--5. 给StuCou表的StuNo与CouNo字段创建主键约束
ALTER TABLE StuCou
ADD CONSTRAINT pk_stucou PRIMARY KEY ( StuNo , CouNo );
--6. 删除StuCou表的主键约束，然后再次创建
ALTER TABLE StuCou
DROP CONSTRAINT pk_stucou ;
ALTER TABLE StuCou
ADD CONSTRAINT pk_stucou PRIMARY KEY ( StuNo , CouNo );
/* 任务：创建外键约束 */
--1. 给Student表的ClassNo创建外键约束
ALTER TABLE Student
ADD CONSTRAINT fk_student FOREIGN KEY ( ClassNo )
REFERENCES Class ( ClassNo );
-- 在数据库关系图里创建Class表与Student表之间的永久性联系
--2. 给Course表的DepartNo创建外键约束
ALTER TABLE Course
ADD CONSTRAINT fk_course FOREIGN KEY ( DepartNo );
REFERENCES Department ( DepartNo );
-- 在数据库关系图里创建Department表与Course表之间的永久性联系
--3. 给StuCou表的StuNo与CouNo创建外键约束
ALTER TABLE StuCou
ADD CONSTRAINT fk_stucou_stuno FOREIGN KEY ( StuNo )
REFERENCES Student ( StuNo );
ALTER TABLE StuCou
ADD CONSTRAINT fk_stucou_couno FOREIGN KEY ( CouNo )
REFERENCES Course ( CouNo );
-- 在数据库关系图里创建StuCou表与Student表和Course之间的永久性联系
--4. 给Class表的DepartNo创建外键约束
ALTER TABLE Class
ADD CONSTRAINT fk_class FOREIGN KEY ( DepartNo )
REFERENCES Department ( DepartNo );
-- 在数据库关系图里创建Department表与Class表之间的永久性联系
/* 任务：创建唯一约束 */
--1. 给Course表的CouName创建一个唯一索引
ALTER TABLE Course
ADD CONSTRAINT un_couname UNIQUE ( CouName );
--2. 给Course表插入两条记录，故意让课程名相同
/* 因为Course表的DepartNo是外键，参照Department表的主键DepartNo，必须先给主键设置值 */
INSERT INTO Department VALUES ( '01' , '信息工程系' );
INSERT INTO Department VALUES ( '02' , '建筑工程系' );
INSERT INTO Course VALUES ( '001' , '大型数据库开发' , '信息技术' , 3 , '李小平' , '01' , '周二5-6节' , 20 , 43 , 20 );
INSERT INTO Course VALUES ( '002' , '大型数据库开发' , '工程技术' , 2 , '王彦荣' , '02' , '周三3-4节' , 25 , 30 , 25 );
/* 任务：创建检查约束 */
--1. 约束Student表的StuNo字段，只允许8位数字，并且不可以为8个0
ALTER TABLE Student
ADD CONSTRAINT ck_stuno CHECK ( StuNo LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AND StuNo <> '00000000' );
--2. 给Student表输入一条记录，学号为'00000000'
/* 因为Student表的ClassNo是外键，参照Class表的主键ClassNo，必须先给主键设置值 */
INSERT INTO Class VALUES ( '20150001' , '15软件1班' , '01' );
INSERT INTO Student VALUES ( '00000000' , '黄飞鸿' , 'FAFFGS02' , '20150001' );
--3. 给Student表插入一条记录，学号为'2015x001'
INSERT INTO Student VALUES ( '2015x001' , '张三丰' , 'GHET56KO' , '20150001' );
--4. 删除Student表对于StuNo的检查约束
ALTER TABLE Student
DROP CONSTRAINT ck_stuno ;
--5. 选课表StuCou的报名志愿最多只允许5个
ALTER TABLE StuCou
ADD CONSTRAINT ck_willorder CHECK ( WillOrder IN ( 1 , 2 , 3 , 4 , 5 ));
--6. 给Student表插入一条记录
INSERT INTO Student VALUES ( '20150001' , '李晓红' , 'QW45FG57' , '20150001' );
--7. 给选课表StuCou插入一条记录，故意让WillOrder为6
INSERT INTO StuCou ( StuNo , CouNo , WillOrder , State ) VALUES ( '20150001' , '001' , 6 , '报名' );
--8. 给Course表的CouNo字段设置检查约束，只能由数字构成，并且不能全为0
ALTER TABLE Course
ADD CONSTRAINT ck_couno CHECK ( CouNo LIKE '[0-9][0-9][0-9]' AND CouNo <> '000' );
--9. 给Course表插入一条记录，故意设置CouNo为'000'
INSERT INTO Course VALUES ( '000' , 'Java程序设计' , '信息技术' , 2 , '李晓云' , '01' , '周三3-4节' , 25 , 35 , 25 );
/* 任务：创建默认约束 */
--1. 给选课表StuCou的State字段设置默认约束，默认值为“待定”
ALTER TABLE StuCou
ADD CONSTRAINT df_state DEFAULT ( '待定' ) FOR State ;
--2. 给选课表StuCou插入一条记录，不设置State字段的值
INSERT INTO StuCou ( StuNo , CouNo , WillOrder ) VALUES ( '20150001' , '001' , 2 );
--3. 显示StuCou表的全部记录
SELECT * FROM StuCou ;
--4. 删除StuCou表对State字段的默认约束
ALTER TABLE StuCou
DROP CONSTRAINT df_state ;
/* 任务：创建默认值（在SQL Server后续版本里已经删除此功能，建议使用默认约束） */
--1. 给Course表的Teacher字段创建一个默认值对象
CREATE DEFAULT df_teacher AS '待定' ;
--2. 将默认值对象df_teacher绑定到Course表的Teacher字段
sp_bindefault df_teacher , 'Course.Teacher'
--3. 验证：给Course表插入一条记录，不设置Teacher字段的值
INSERT INTO Course ( CouNo , CouName , Kind , Credit , DepartNo , SchoolTime , LimitNum , WillNum , ChooseNum )
VALUES ( '002' , 'Java程序设计' , '信息技术' , 4 , '01' , '周四3-4节' , 30 , 50 , 30 );
--4. 查看Course表的全部记录
SELECT * FROM Course ;
--5. 给Course表的Teacher字段解除默认值绑定
sp_unbindefault 'Course.Teacher'
--6. 删除默认值对象df_teacher
DROP DEFAULT df_teacher ;
/* 任务：创建规则（删除规则） */
--1. 针对Course表创建规则，保证Credit字段只能取1.5、2、2.5、3、3.5、4、4.5、5
CREATE RULE credit_rule
AS @ Credit IN ( 1.5 , 2 , 2.5 , 3 , 3.5 , 4 , 4.5 , 5 );
--2. 将规则对象credit_rule绑定到Course表的Credit字段
sp_bindrule credit_rule , 'Course.Credit'
--3. 验证：给Course表插入一条记录，故意设置学分为2.2
INSERT INTO Course VALUES ( '003' , 'Web前端开发技术' , '信息技术' , 2.2 , '李小龙' , '01' , '周四3-4节' , 30 , 50 , 30 );
--4. 将Course表的Credit解除规则绑定
sp_unbindrule 'Course.Credit'
--5. 删除规则credit_rule
DROP RULE credit_rule ;
/* 任务：使用标识列 */
--1. 创建Test表（id是标识列，seed是201501，increment是1）
CREATE TABLE Test
(
id int NOT NULL IDENTITY ( 201501 , 1 ),
name nvarchar ( 8 ) NOT NULL ,
gender nvarchar ( 1 ) NOT NULL ,
age smallint NOT NULL
);
--2. 给Test表插入几条记录，不用管标识列
INSERT INTO Test ( name , gender , age ) VALUES ( '张三丰' , '男' , 18 );
INSERT INTO Test ( name , gender , age ) VALUES ( '李晓霞' , '女' , 19 );
INSERT INTO Test ( name , gender , age ) VALUES ( '甄云文' , '女' , 18 );
INSERT INTO Test ( name , gender , age ) VALUES ( '杨臣刚' , '男' , 20 );
INSERT INTO Test ( name , gender , age ) VALUES ( '王丽霞' , '女' , 18 );
--3. 查看Test表的全部记录
SELECT *
FROM Test ;
文章来源: howard2005.blog.csdn.net，作者：howard2005，版权归原作者所有，如需转载，请联系作者。
原文链接：howard2005.blog.csdn.net/article/details/79447992
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）