基本表的操作及数据完整性约束的操作.ppt
广州市数字天堂软件科技有限公司,1,实验五 基本表的操作及数据完整性约束操作2,一、目的与要求 掌握SQL Server 2005基本表的创建、修改、删除，以及约束、索引的创建二、实验内容 1、使用T-SQL语句创建、修改、删除表 2、使用T-SQL语句增加、删除约束,广州市数字天堂软件科技有限公司,2,1、约束是SQL Server提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQL SERVER中，对于基本表的约束分为列约束和表约束。2、列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用,分隔，定义表约束时必须指出要约束的那些列的名称。3、完整性约束的基本语法格式为：CONSTRAINT constraint_name（约束名）约束不指定名称时，系统会给定一个名称。4、SQL Server提供6种约束：主键约束（primary key constraint）、惟一性约束（unique constraint）、检查约束（check constraint）、默认约束（default constraint）、外键约束（foreign key constraint）和空值（NULL）约束。,数据完整性,广州市数字天堂软件科技有限公司,3,创建约束,1、主键（PRIMARY KEY）约束 PRIMARY KEY约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性。注意：PRIMARY KEY既可用于列约束，也可用于表约束。,广州市数字天堂软件科技有限公司,4,创建约束,1、主键（PRIMARY KEY）约束 主键的创建操作方法有两种：SQL Server管理平台操作法和Transact-SQL语句操作法。（1）SQL Server管理平台操作法，如图5-1所示。,图5-1 选择多个字段共同做为主键对话框,广州市数字天堂软件科技有限公司,5,创建约束,1、主键（PRIMARY KEY）约束（2）使用Transact-SQL语句操作法设置主键约束，其语法形式如下：CONSTRAINT constraint_name PRIMARY KEY（column_name）PRIMARY KEY用于定义表约束时，即将某些列的组合定义为主键，其语法格式如下：CONSTRAINT constraint_name PRIMARY KEY（column_name,n）,广州市数字天堂软件科技有限公司,6,创建约束,1、主键（PRIMARY KEY）约束 例 建立一个SC表，定义SNO，CNO共同组成SC的主键程序清单如下：CREATE TABLE SC(SNO CHAR(8)NOT NULL,CNO CHAR(8)NOT NULL,SCORE NUMERIC(3),CONSTRAINT PK_SC PRIMARY KEY(SNO,CNO),广州市数字天堂软件科技有限公司,7,创建约束,2.惟一性约束 惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值。创建惟一性约束的方法有两种：通过SQL Server管理平台可以完成创建和修改惟一性约束的操作；使用Transact-SQL语句完成惟一性约束的操作。（1）通过SQL Server管理平台可以完成创建和修改惟一性约束的操作，如图5-2所示。,图5-2 创建惟一性约束对话框,广州市数字天堂软件科技有限公司,8,创建约束,2.惟一性约束（2）使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下：CONSTRAINT constraint_name UNIQUE（column_name,n）例创建一个学生信息表，其中name字段具有惟一性。程序清单如下：Create table s(sno char(8),sname char(10),ssex char(2),constraint pk_id primary key(sno),constraint uk_identity unique(sname),广州市数字天堂软件科技有限公司,9,创建约束,3.检查约束 检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。创建检查约束常用的操作方法有如下两种：使用SQL Server管理平台创建检查约束；用Transact-SQL语句创建检查约束。（1）使用SQL Server管理平台创建检查约束，如图5-3所示。,图5-3 创建检查约束对话框,点击此处可进行CHECK约束编辑,广州市数字天堂软件科技有限公司,10,创建约束,3.检查约束（2）用Transact-SQL语句创建检查约束。用Transact-SQL语句创建检查约束。其语法形式如下：CONSTRAINT constraint_name CHECK(logical_expression)例 建立一个SC表，定义SCORE 的取值范围为0到100之间。程序清单如下：CREATE TABLE SC(SNO CHAR(8),CNO CHAR(8),SCORE NUMERIC(3)CONSTRAINT SCORE_CHK CHECK(SCORE=0 AND SCORE=100),广州市数字天堂软件科技有限公司,11,创建约束,4.默认（DEFAULT）约束 默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。创建默认约束常用的操作方法有如下两种：使用SQL Server管理平台创建默认约束；创建默认约束的Transact-SQL语句操作法。（1）使用SQL Server管理平台创建默认约束，如图5-4所示。,图5-4 创建默认约束对话框,广州市数字天堂软件科技有限公司,12,创建约束,4.默认（DEFAULT）约束（2）创建默认约束的Transact-SQL语句操作法。其语法形式如下：CONSTRAINT constraint_name DEFAULT constraint_expression例为 dept字段创建默认约束。程序清单如下：constraint df_ssex default 男,广州市数字天堂软件科技有限公司,13,创建约束,5.外键约束 外键(FOREIGN KEY)是用于建立和加强两个表数据之间的链接的一列或多列。外部键约束用于强制参照完整性。创建外部键约束常用的操作方法有如下两种：在SQL Server管理平台中添加外部键约束；使用Transact-SQL语句设置外部键约束。（1）在SQL Server管理平台中添加外部键约束，在SQL Server管理平台中添加外部键约束。如图5-5，5-6所示。,图5-5 选择创建外键约束的字段,图3-6 创建外键约束对话框,点击此处对外键进行编辑,选择主键表、字段，外键表、字段,广州市数字天堂软件科技有限公司,14,创建约束,5.外键约束（2）使用Transact-SQL语句设置外部键约束，其语法形式如下：CONSTRAINT constraint_name FOREIGN KEY（column_name,n）REFERENCES ref_table（ref_column,n）例 建立一个SC表，定义SNO,CNO为SC的外部键。程序清单如下：CREATE TABLE SC(SNO CHAR(8)NOT NULL CONSTRAINT FK_SNO FOREIGN KEY REFERENCES S(SNO),CNO CHAR(8)NOT NULL CONSTRAINT FK_CNO FOREIGN KEY REFERENCES C(CNO),SCORE NUMERIC(3),CONSTRAINT PK_SC PRIMARY KEY(SNO,CNO),广州市数字天堂软件科技有限公司,15,创建约束,6.空值（NULL）约束（1）空值（NULL）约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串的“NULL”字符串，而是表示“没有数据”的意思。在SQL Server管理平台中添加空值（NULL）约束。如图5-7所示。,图5-7设置空值（NULL）约束对话框,广州市数字天堂软件科技有限公司,16,6.空值（NULL）约束（2）使用Transact-SQL语句设置空值（NULL）约束，其语法形式如下：CONSTRAINT NULL|NOT NULL 例5-13 建立一个S表，对SNO字段进行NOT NULL约束。程序清单如下：CREATE TABLE S(SNO CHAR(8)CONSTRAINT S_CONS NOT NULL,SNAME VARCHAR(20),AGE SMALLINT,SEX CHAR(2)DEFAULT 男,DEPT VARCHAR(20),创建约束,方括号里的内容通常省略,广州市数字天堂软件科技有限公司,17,表管理,1、创建表CREATE TABLE(列名1 数据类型 列级完整性约束,n,n,表级完整性约束,n)2、修改表结构ALTER TABLE ALTER COLUMN(修改字段数据类型或长度)|ADD(增加新的一列)|DROP COLUMN(删除一列)|ADD CONSTRAINT(增加新的一约束)|DROP CONSTRAINT(删除约束)3、删除表DROP TABLE,广州市数字天堂软件科技有限公司,18,试验1,分别用SQL SERVER的T-SQL语句创建学生表S(SNO,SNAME,SSEX)和学生课程关系表SC(SNO,CNO,SCORE)。要求如下：,广州市数字天堂软件科技有限公司,19,试验1,第一种方式：直接建表过程中加约束,删除表时需注意删除顺序，应该先删除有外键的表。CREATE TABLE S(SNO CHAR(8)NOT NULL CONSTRAINT PK_SNO PRIMARY KEY,SNAME VARCHAR(10),SSEX CHAR(2)DEFAULT 男,CONSTRAINT UK_SNAME UNIQUE(SNAME)CREATE TABLE SC(SNO CHAR(8)NOT NULL CONSTRAINT FK_SNO FOREIGN KEY REFERENCES S(SNO),CNO CHAR(5)NOT NULL,SCORE TINYINT CONSTRAINT CHK_SCORE CHECK(SCORE=0 AND SCORE=100),CONSTRAINT PK_SC PRIMARY KEY(SNO,CNO),广州市数字天堂软件科技有限公司,20,试验1,第二种方式：先建表，再加约束CREATE TABLE S(SNO CHAR(8)NOT NULL,SNAME CHAR(10),SSEX CHAR(2)DEFAULT 男)ALTER TABLE S ADD CONSTRAINT PK_SNO PRIMARY KEY(SNO);ALTER TABLE S ADD CONSTRAINT UK_SNAME UNIQUE(SNAME);CREATE TABLE SC(SNO CHAR(8)NOT NULL,CNO CHAR(5)NOT NULL,SCORE NUMERIC(3),CONSTRAINT PK_SC PRIMARY KEY(SNO,CNO)ALTER TABLE SC ADD CONSTRAINT FK_SNO FOREIGN KEY(SNO)REFERENCES S(SNO);ALTER TABLE SC ADD CONSTRAINT CHK_SCORE CHECK(SCORE=0 AND SCORE=100);,广州市数字天堂软件科技有限公司,21,练习,例子：创建部门信息表部门信息表（DepartmentInfo）,广州市数字天堂软件科技有限公司,22,练习,以下由学生完成,用SQL创建下面信息表,与上面例子部门表中的departmentCode建立Foreign key,广州市数字天堂软件科技有限公司,23,2 请用SQL语句完成下列操作：(A)增加新列 向studentinfo(学生信息表)增加“业余爱好”(hobby)，其数据类型为字符型，长度为20：可以为null；(B)增加完整性约束 向studentinfo(学生信息表)增加一个名为date_check的约束，用于说明入学日期大于出生日期；(注：日期是“先小后大”，如今天日期大于昨天日期),练习,以下由学生完成,广州市数字天堂软件科技有限公司,24,(C)修改列定义 将studentinfo(学生信息表)中学生姓名列(studentname)长度改为10字节；(D)删除完整性约束 请删除在studentinfo(学生信息表)建立的date_check约束;(E)删除列定义 删除在studentinfo(学生信息表)增加的业余爱好(hobby)列定义;(F)删除表 思考：若要删除departmentinfo表和Studentinfo表,其顺序应该是什么?(G)分别在以上两张表中插入数据，注意约束,以下由学生完成,广州市数字天堂软件科技有限公司,25,下面请大家自己动手体会！,
