数据库的完整性约束-包含答案

实验八：数据库的完整性约束 一、实验目的

1.掌握主键约束、外键约束及及check约束的用法； 2.掌握默认值约束的应用。 二、实验环境

已安装SQL Server 2008 企业版的计算机； 具有局域网环境,有固定IP; 三、实验学时 2学时 四、实验要求

1.熟悉数据库完整性相关概念及约束； 2.了解约束、默认值； 3.完成实验报告； 五、实验准备知识 1、创建primary key约束

（1）在创建数据表时创建primary key约束 CREATE TABLE table_name

(column_name data_type [PRIMARY KEY | unique | NOT NULL] [, column_name data_type [PRIMARY KEY | unique | NOT NULL]

...

[PRIMARY KEY(column [,...n])] )

（2）在修改表时同时创建primary key约束 ALTER TABLE table_name ADD primary key(column [,...n]) 2、创建Foreign key约束

（1）创建表时同时定义Foreign key约束

CREATE TABLE table_name

(column_name data_type [FOREIGN KEY] REFERNCES ref_table(ref_column)

[, column_name data_type [FOREIGN KEY] REFERNCES ref_table(ref_column)

...

[FOREIGN KEY] (column) REFERNCES ref_table(ref_column) )

（2）通过修改表定义Foreign key约束 ALTER TABLE table_name ADD [FOREIGN KEY] (column) REFERNCES ref_table(ref_column ) 3、创建check约束

（1）创建数据表时创建check约束 CREATE TABLE table_name (column_name

data_type

[NOT

NULL

|

CHECK

(logical_expression)] ...

CHECK (logical_expression)] ）

（2）在修改数据表时添加check约束 ALTER TABLE table_name ADD CHECK (logical_expression) 4、创建default约束

（1）创建数据表时创建default约束 Create table table_name ( )

（2）修改数据表时添加一个字段的同时创建default约束 Alter table table_name

column_name

datatype

[not

null

|

DEFAULT

(constraint_expression)] …

ADD column_name datatype [not null | DEFAULT (constraint_expression)] with values （3）对表中指定的列定义默认值约束 Alter table table_name

ADD [ DEFAULT (constraint_expression)] For column 5、完整性约束命名子句

CONSTRAINT <完整性约束条件名>[PRIMARY KEY 短语︱FOREIGN KEY 短语︱CHECK 短语]

6、删除primary key约束或unique约束 ALTER TABLE table_name

DROP CONSTRAINT constraint_name [, ...n] 六、实验内容及步骤

使用T-SQL语句实现以下操作；

1.请用至少2种方法定义stu数据库中student表的主键sno； 方法1：

CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20), Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ) 方法2：

CREATE TABLE Student (Sno CHAR(9), Sname CHAR(20), Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY(Sno)

)

2.将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;

Alter table course

Add constraint cno_pk primary key (cno) 3.为表course中的字段cname添加唯一值约束； Alter table course

Add constraint cname_pk unique (cname)

4.将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为

sc_pk; Alter table sc

Add constraint sc_pk primary key (sno,cno)

5.对于数据表sc的sno、cno字段定义为外码，使之与表student的主码

sno及表course的主码cno对应，实现如下参照完整性： 1)删除student表中记录的同时删除sc表中与该记录sno字段值相同

的记录；

2)修改student表某记录的sno时，若sc表中与该字段值对应的有若

干条记录，则拒绝修改；

3)修改course表cno字段值时，该字段在sc表中的对应值也应修改；

4)删除course表一条记录时，若该字段在在sc表中存在，则删除该

字段对应的记录； Use stu

Create table student (Sno CHAR(9),

Sname CHAR(20), Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20), PRIMARY KEY(Sno) )

Create table course (cno char(4), cname char(40), cpno char(4), ccedit smallint,

foreign key (cpno) references course(cno), primary key (cno) )

Create table sc (sno char(9), cno char(4), Grade smallint,

foreign key (sno) references student(sno) on delete cascade on update no action, foreign key (cno) references course (cno) on delete cascade on update cascade )

6.定义check约束，要求学生学号sno必须为9位数字字符，且不能以0

开头，第二三位皆为0； Alter table student Add CONSTRAINT sno_ck

Check (sno like ‘[1-9]00 [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]’) 7.定义stu数据库中student表中学生年龄值在16-25范围内；

CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20), Ssex CHAR(2),

Sage SMALLINT Check (sage>=16 and sage<=25), Sdept CHAR(20) )

8.定义stu数据库中student表中学生性别列中只能输入“男”或“女”；

CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20),

Ssex CHAR(2) Check(Ssex in (‘男’,’女’)), Sage SMALLINT, Sdept CHAR(20) )

9.定义stu数据库student表中学生年龄值默认值为20； CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20), Ssex CHAR(2),

Sage SMALLINT Default 20, Sdept CHAR(20) )

七、出现问题及解决办法

如：某些操作无法执行，如何解决？