实验二 SQLSERVER数据表的设计与完整性约束

SQL语言基础：SQL中的数据完整性约束用法前言数据库的完整性是指数据库正确性和相容性，是防止合法用户使用数据库时向数据库加入不符合语义的数据，从而保证了数据库中的数据是正确的，避免非法的更新操作。

1、主键约束1.1 完整性约束条件完整性约束条件作用的对象有关系、元组、列三种。

1.2 完整性控制主要有三个方面的功能：定义功能、检测功能、处理功能。

检查是否违背完整性约束的时机有立即执行约束、延时执行约束。

最重要的完整性约束条件是实体完整性、参照完整性。

1.3 实体完整性 Primary Key在关系中只能有一个主键。

声明主键调入方法：•1、将 Primary Key 保留字加在属性类型之后。

比如 Sno char(10) Primary Key•2、在属性列表中引入一个新元素，该元素包含保留字Primary Key 和圆括号括起来的形成该键的属性或属性组列表。

比如：Primary Key (Sno)1.4 外键（Foreign Key）约束语法格式：Foreign Key (属性名) references 表名(属性名)[ON DELETE][CASCADE][SET NULL]说明：•Foreign Key 定义那些列为外码；•references 指明外码属于哪个表的主码；•ON DELETE CASCADE 指明删除记录同时删除关系中的记录；•SET NULL 表示置为空值方式。

比如：Foreign Key Sno references Student (Sno)1.5 属性值上的约束主要包括：•not null 不为空•unique 唯一约束•check 设定属性值需要满足的条件。

比如：age Number(3) check (age>0 and age <180)1.6 全局约束全局约束是值一些比较复杂的完整性约束，这些约束涉及多个属性间的联系或多个不同关系间的联系。

有基于元组的检查子句、断言。

我本来以为这一部分对于有编程基础的人来说很容易就没有详细的研究。

后来，在设计数据库表的时候，用varchar类型写入中文会乱码，之后我将varchar类型改为nvarchar类型时才解决。

我意识到，这部分很简单，但也要仔细区分。

数据类型定义要精确，这样不容易出现意想不到的问题，也节省了内存。

首先解决一个问题：字符与字节（一）“字节”的定义字节（Byte）是一种计量单位，表示数据量多少，它是计算机信息技术用于计量存储容量的一种计量单位。

（二）“字符”的定义字符是指计算机中使用的文字和符号，比如1、2、3、A、B、C、~！·#￥%……—*（）——+、等等。

（三）“字节”与“字符”不同编码里，字符和字节的对应关系不同：①ASCII码中，一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。

一个二进制数字序列，在计算机中作为一个数字单元，一般为8位二进制数，换算为十进制。

最小值0，最大值255。

②UTF-8编码中，一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。

③Unicode编码中，一个英文等于两个字节，一个中文（含繁体）等于两个字节。

符号：英文标点占一个字节，中文标点占两个字节。

举例：英文句号“.”占1个字节的大小，中文句号“。

”占2个字节的大小。

④UTF-16编码中，一个英文字母字符或一个汉字字符存储都需要2个字节（Unicode扩展区的一些汉字存储需要4个字节）。

⑤UTF-32编码中，世界上任何字符的存储都需要4个字节。

一：定义列的数据类型字符串1：char [(n)]固定长度，非Unicode字符数据。

（在列数据项的大小一致时使用）n的范围1-8000字节2：varchar [(n|max)]可变长度，非Unicode字符数据。

（在列的数据项的大小差距很大时使用）Max指示最大存储大小2的31次幂-1字节。

3：text服务器代码页中长度可变的非Unicode字符数据，最大长度为2的31次幂-1个字符。

数据库SQLServer2012笔记（五）——维护数据的完整性——约束1、概念1）约束⽤于确保数据库数据满⾜特定的商业规则。

2）在sql server中，约束包括：not null,unique,primary key,foreigh key,check五种。

2、not null(⾮空)如果在列上定义了not null，那么当插⼊数据时，必须为列提供数据。

3、unique(唯⼀)当定义了唯⼀约束后，该列值是不能重复的，可以为null，但最多只能有⼀个是null。

4、primary key不能重复且不能为null。

⼀张表最多只能有⼀个主键，但是可以有多个unique约束。

表可以有复合主键多个咧构成⼀个主键放在最后：primary key(字段1，字段2)同时相同，才为重复。

5、⾏级定义和表级定义create table test(Id int,name nvarchar(30),age int not null,——>⾏级定义primary key(Id,name)——>表级定义)6、foreign key:定义主表（被引⽤的表）和从表（引⽤外键的表）之间的关系。

1）外键约束要定义在从表。

2）主表必须有主键约束或unique约束。

3）外键列数据必须在主表的主键列存在或为null。

7、check：⽤于强制⾏数据必须满⾜的条件。

8、default：若不赋值，则给默认值create table mes (Id int primary key identiry(1,1), mesDate datatime default getdate())插⼊数据时，若不给出时间，则赋本地时间。

SQL SERVER数据完整性及约束【摘要】所谓数据完整性，实际上是衡量数据库中数据质量好坏的一种标志，是确保数据库中数据一致，正确以及符合企业规则的一种思想，本文以详细的文字来介绍了什么是数据完整性及约束，以及如何在数据库建设中应用和使用这些约束。

【关键词】SQL Server；约束；数据完整性；一致性在数据库管理系统中，保证数据库中的数据完整性是非常重要的。

所谓数据完整性，就是指存储在数据库的表中数据的一致性和正确性。

约束定义关于列中允许值的规则，是强制完整性的标准机制，对输入数据的取值范围和格式的限制称为约束。

约束是用来保证数据完整性的。

在SQL Server中有6种常设约束：空置约束（NULL）、唯一性约束（unique constraint）、主键约束（primary key constraint）、外键约束（foreign key constraint）、检查约束（check constraint）、缺省约束（default constraint）。

一、SQL Server的数据完整性存储在数据库中的所有数据值均属正确的状态。

如果数据库中存储有不正确的数据值，则该数据库称为已丧失数据完整性。

强制数据完整性可确保数据库中的数据质量。

例如，如果有一“教学管理数据库”，学生表中学号一项输入了值为20110101的学生，那么该数据库不应允许其他学生使用同一学号值。

如果计划将选修表中成绩的值范围设定为0到100，则数据库不应接受121。

如果学生表有一列为班级编码，该列存储的班级编码为数据库中允许的有效班级编码。

二、数据完整性分类在SQL Server中，根据数据完整性所作用的数据库对象和范围不同，可以将数据完整性分为实体完整性、域完整性、引用完整性、用户定义完整性四种。

1、实体完整性实体完整性，简单来说，就是将表中的每一行看作一个实体。

实体完整性要求表的标示符列或主键的完整性（主键不能为空）。

第3章实验数据完整性实验(SQL Server) 实验目的：加深对数据完整性的理解。

实验内容：数据库的完整性设置。

实验步骤：可视化界面的操作方法：一、实体完整性1．将student表的sno字段设为主键，如图1所示。

图12．将sc表的sno和cno设置为主键，如图2所示：图2二、域完整性3．将ssex字段设置为只能取“男”，“女”两值，如图3所示。

图3三、参照完整性4．将student表和sc表中的sno字段设为参照，见图6.4。

图4命令方式操作方法：一、实体完整性1．将student表的sno字段设为主键：当student表已存在则执行：alter table student add constraint pk_sno primary key (sno) 当student表不存在则执行：Create table student(sno CHAR(5) primary key ,sname CHAR(10),ssex CHAR(2) ,sage int,sdept CHAR(4))2．添加一身份证号字段，设置其惟一性。

Alter table student add id char(18) unique (id)3．将sc表的sno和cno设置为主键：当sc表已存在则执行：alter table sc add constraint PK_SnoCno primary key (sno,cno)当sc表不存在则执行：Create table sc(sno CHAR(5),cno CHAR(2),grade INT NULL,constraint PK_SnoCno primary key (sno,cno))二、域完整性4．将ssex字段设置为只能取“男”，“女”两值：当student表已存在则执行：alter table student add constraint CK_Sex check (ssex in ('男' ,'女'))当student表不存在则执行：Create table student(sno CHAR(5) primary key ,sname CHAR(10),ssex CHAR(2) check (ssex in ('男' ,'女')) ,sage int, sdept CHAR(4))5．设置学号字段只能输入数字：alter table student add constraint CK_Sno_Format check (sno like '[0-9][0-9][0-9][0-9][0-9]') 6．设置身份证号的输入格式：alter table student add constraint CK_ID_Format check((id like '[0-9][0-9][0-9][0-9][0-9][0-9] [1-2][0-9][0-9][0-9][0-1] [0-9][0-3][0-9] 0-9][0-9] [ [0-9]_') or (id like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]')) 7．设置18位身份证号的第7位到第10位为合法的年份(1900-2050)alter table student add constraint CK_ID_Format2 check( not len(id)=18or ( (convert(smallint,substring(id,7,4) )>=1900) and(convert(smallint,substring(id,7,4) )<=2050)) )8．设置男生的年龄必须大于22，女生的年龄必须大于20。

广州XX学院数据库管理及应用实验报告专业班级计算机181 实验日期2020.4.18 姓名李XX 学号2018132 实验名称数据完整性约束指导教师王琢（报告内容包括实验目的、实验设备及器材、实验内容、实验步骤、实验结果、实验小结等）一、实验目的（1）理解数据完整性约束概念。

（2）掌握SQL Server的数据完整性约束技术。

（3）了解SQL Server的违反数据完整性约束的处理措施。

二、实验设备及器材Windows平台、SQL Server 2012。

三、实验内容4.1在数据库EDUC中创建下列完整性约束。

1）为数据库Course_info创建CHECK约束：当插入或修改一个记录时确保此记录的授课学时在10-80。

2）为教师信息表Teacher_info创建CHECK约束：男教师的出生日期在1960年1月1日后，女教师的出生日期在1965年1月1日后。

3）为教师上课信息表TC_info创建CHECK约束：学生成绩在0-100。

4）为教师上课信息表TC_info创建外键约束：tcid、tno、classno和cno。

4.2在数据库TSGL中创建下列完整性约束。

1）创建CHECK约束：readers表的RederType在0-9。

2）利用SSMS删除readers表的约束。

3）创建主键约束：为readers表的字段ReaderID添加主键。

4）创建多个字段的主键：为borrowinf表的ReaderID、BookID字段定义主键。

四、实验步骤4.11）为数据库Course_info创建CHECK约束：当插入或修改一个记录时确保此记录的授课学时在10-80。

USE EDUCGOALTER TABLE Course_infoWITH NOCHECK ADD CONSTRAINT CK_lecture CHECK(lecture>=10 AND lecture <=80)GO2）为教师信息表Teacher_info创建CHECK约束：男教师的出生日期在1960年1月1日后，女教师的出生日期在1965年1月1日后。

[SQL]基本表的定义及其完整性约束在使⽤数据库时，绝⼤多数时间都是在使⽤基本表。

SQL Server数据类型创建基本表基本格式如下：create table <表名>(<列名1> <数据类型> [<列级完整性约束条件>],……<列名n> <数据类型> [<列级完整性约束条件>][, <表级完整性约束条件>])如果完整性约束涉及到多个属性列，则必须定义在表级上，否则可以定义在列级或表级。

如下创建⼀个Persons基本表：use test -- 在该数据库中创建基本表gocreate table Persons(Id_P int primary key, -- Id_P为主键LastName nvarchar(20),FirstName nvarchar(20),Address nvarchar(50),City nvarchar(10))go完整性约束完整性约束的⽤途是限制输⼊到基本表中的值的范围，SQL的完整性约束可分为列级完整性约束和表级完整性约束：列级完整性约束：针对关系属性值的限定条件，只能应⽤在⼀列上表级完整性约束：涉及关系中的多个属性的限制条件，可以应⽤在⼀个基本表中的多个列上。

如果完整性约束涉及到多个属性列，则必须定义在表级上，否则可以定义在列级或表级。

当创建完整性约束之后，它作为基本表定义的⼀部分，存⼊数据字典中。

pri m ary key约束主键（primary key）约束是实体完整性约束，primary key⽤于定义主键，它保证主键的唯⼀性与⾮空性。

⼀个基本表的主键由⼀列或者⼏列构成，可以定义在列级或者表级上，但是不能在两个级别上进⾏定义。

定义主键约束的⽅法如下：1. primary key之间写在列名及其数据类型之后。

例如：create table test(col_name int primary key,……)2. 按照语法在相应的列名及其数据类型后单独列出：constraint <约束名> primary key约束名就是主键的名字。

T-SQL：SQLServer定义数据完整性6⼤约束（三）1.创建⼀客户张表1IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL2DROP TABLE dbo.Employees;34CREATE TABLE dbo.Employees5(6 empid INT NOT NULL,7 firstname VARCHAR(30) NOT NULL,8 lastname VARCHAR(30) NOT NULL,9 hiredate DATE NOT NULL,10 mgrid INT NULL,11 ssn VARCHAR(20) NOT NULL,12 salary MONEY NOT NULL13 );2.主键约束-- Primary keyALTER TABLE dbo.EmployeesADD CONSTRAINT PK_EmployeesPRIMARY KEY(empid);对于主键约束后台将创建⼀个唯⼀索引，以物理机制强制逻辑的唯⼀性约束3.唯⼀约束-- UniqueALTER TABLE dbo.EmployeesADD CONSTRAINT UNQ_Employees_ssnUNIQUE(ssn);4.外键约束创建⼀张订单表插⼊外键IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULLDROP TABLE dbo.Orders;CREATE TABLE dbo.Orders(orderid INT NOT NULL,empid INT NOT NULL,custid VARCHAR(10) NOT NULL,orderts DATETIME2 NOT NULL,qty INT NOT NULL,CONSTRAINT PK_OrdersPRIMARY KEY(orderid));-- Foreign keysALTER TABLE dbo.OrdersADD CONSTRAINT FK_Orders_EmployeesFOREIGN KEY(empid)REFERENCES dbo.Employees(empid);5.CHECK约束-- CheckALTER TABLE dbo.EmployeesADD CONSTRAINT CHK_Employees_salaryCHECK(salary >0.00);6.默认约束-- DefaultALTER TABLE dbo.OrdersADD CONSTRAINT DFT_Orders_ordertsDEFAULT(SYSDATETIME()) FOR orderts;-- CleanupDROP TABLE dbo.Orders, dbo.Employees;。

六，SQLServer完整性约束条件1. 空值约束（NULL或NOT NULL） 空值约束决定属性值是否运⾏为空值（NULL）。

NULL表⽰没有输⼊任何内容，它不是零和空⽩。

不允许为空值⽤NOT NULL表⽰。

eg：新建学⽣表，设置姓名不允许为空，性别允许为空。

CREATE TABLE stu (name CHAR(10) NOT NULL,sex CHAR(2) NULL)2. 主键约束（PRIMARY KEY CONSTRAINT） 主键约束要求主键属性取值必须唯⼀，⼀个表只能包含⼀个主键约束。

如果没有在主健约束中指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该主健约束⽤CLUSTERED。

语法：[CONSTRAINT 约束名] PRIMARY KEY [CLUSTERED | NONCLUSTERED] (列名 [,...n])eg1：新建学⽣表，将id设置为主键　CREATE TABLE stu (id CHAR(12) NOT NULL,name CHAR(10) NOT NULL,sex CHAR(2) NULL,CONSTRAINT pk_stu PRIMARY KEY CLUSTERED (id))eg2：新建学⽣表，将id和name设置为联合主键CREATE TABLE stu (id CHAR(12) NOT NULL,name CHAR(10) NOT NULL,sex CHAR(2) NULL,CONSTRAINT pk_stu PRIMARY KEY CLUSTERED (id,name))3. 唯⼀性约束（UNIQUE CONSTRAINT） 唯⼀性约束⽤于指定⼀个或者多个列的组合的值具有唯⼀性，以防⽌在列中输⼊重复的值。

使⽤唯⼀性约束时，需考虑⼀下因素：使⽤唯⼀性约束的字段允许为空值⼀个表中可以允许有多个唯⼀性约束可以把唯⼀性约束定义在多个字段上唯⼀性约束⽤于强制在指定字段上创建⼀个唯⼀性索引在默认情况下，创建的索引类型为⾮聚簇索引 语法：[CONSTRAINT 约束名] UNIQUE [CLUSTERED | NONCLUSTERED] (列名 [,...n])eg：创建学⽣表，设置id和phone唯⼀　CREATE TABLE stu (id CHAR(12) NOT NULL,name CHAR(10) NOT NULL,phone CHAR(11) NULL,CONSTRAINT uniq_stu UNIQUE (id, phone))4. 检查约束（CHECK CONSTRAINT） 使⽤检查约束时，应该注意：⼀个列级检查约束只能与限制的字段有关，⼀个表级检查约束只能与限制的表中字段有关⼀个表可以定义多个检查约束每个CREATE TABLE语句中每个字段只能定义⼀个检查约束在多个字段上定义检查约束，则必须将检查约束定义为表级约束检查约束中不能包含⼦查询 语法：CONSTRAINT CONSTRAINT_name CHECK [NOT FOR REPLICATION] (logical_expression)eg：新建学⽣表，设置id只接受⾸位⾮0的四位数，性别字段只能设为F或MCREATE TABLE stu (id int NOT NULL,name VARCHAR(10) NOT NULL,sex CHAR(1) NULL,CONSTRAINT chk_id CHECK (id LIKE'[1-9][0-9][0-9][0-9]'),CONSTRAINT chk_sex CHECK (sex IN ('F','M')))5. 默认约束（DEFAULT CONSTRAINT） 使⽤默认约束时，应该注意：每个字段只能定义⼀个默认约束如果定义的默认值长于其字段的最⼤长度，该默认值输⼊到表中将被截断不能加⼊到带有IDENTITY属性或数据类型为timestamp的字段上如果字段定义为⽤户定义的数据类型，并且该数据类型绑定到这个字段上，则不允该字段由默认约束6. 外部键约束（FOREIGN KEY CONSTRAINT） 外部键约束⽤于强制参照完整性，提供单个字段或者多个字段的参照完整性。

实验创建数据库及关系表数据完整性约束CKBOOD was revised in the early morning of December 17, 2020.实验一创建数据库及关系表、数据完整性约束网工1203 熊健羲 38一、实验目的1. 掌握SQL Server数据库管理系统的使用，能够在该环境中进行日常数据库操作；2. 掌握在SQL Server中使用图形化工具创建数据库的方法；3．掌握建立关系表的语句，掌握定义主码约束及外码约束的语句；4．掌握修改表结构的语句。

5．掌握数据完整性约束的功能。

二、实验内容和步骤1．创建符合如下条件的数据库：数据库的名字为：Students数据文件的逻辑文件名为：Students_dat，存放在用户盘某目录下；文件的初始大小为：5MB；增长方式为自动增长，每次增加1MB。

实验结果：2．在已建立的Students数据库中，确定出各表中的数据类型，写出创建满足下述条件的四张表的SQL语句，并查看执行结果。

实验结果：3．写出实现如下操作的SQL语句，并查看执行结果：（1）在选课表中添加一个新的修课类别列：列名为：选课类别，类型为char(4)。

（2）将课程表中的学分列的类型改为：tinyint(微整型，取值范围在0～255)。

（3）删除学生表的专业列。

（4）为教师表添加主码约束，其主码列为：教师号。

实验结果：4．在Students数据库中，编写建立满足完整性要求的定义表的SQL语句，执行并观察执行结果。

（1）图书表，结构如下：书号：统一字符编码定长类型，长度为6，主码，书名：统一字符编码可变长类型，长度为30，非空，第一作者：普通编码定长字符类型，长度为10，非空，出版日期：小日期时间型，小于等于当前系统日期，（用getdate()实现）印刷数量：小整型，取值范围：1000～5000，默认为4000，价格：定点小数，小数部分一位，整数部分3位。

实验结果：（2）书店表，结构如下：书店编号：统一字符编码定长类型，长度为6，主码，店名：统一字符编码可变长类型，长度为30，非空，电话：普通编码定长字符类型，12位长，取值形式：010-8位数字地址：普通编码可变长字符类型，40位长。

实验二：建立表和数据完整性一、实验目的与要求：1．实验目的（1）掌握建立表、修改表结构等基本操作。

（2）掌握数据完整性的功能，加深对数据完整性的理解。

2．实验要求（1）使用创建数据库技术建立名称为“学生管理”数据库。

（2）建立“院系”、“学生”、“教师”、“课程”、“选课”等5张表，具体要求见实验指导书。

（3）修改表的结构，具体要求如下：①为学生表增加一个“平均成绩”字段，类型为短整型，默认是空值。

②为课程表的“学时”字段重新定义约束：取值为8的倍数，不允许为空值。

③将院系表的“名称”字段的类型修改为varchar(30)。

④为教师表增加一个“工资”字段，类型为5位整数、2位小数的数值型。

二、实验内容1、实验原理基本表是SQL Server的数据库对象，包含了表中的列，计算列和表级约束。

对它的操作有创建、修改和删除。

（1）建立基本表结构的命令：CREATE TABLE [schema_name].table_name（{<column_definition>|<computed_ column_definition >}[ < table_constraint> ][,…n])其中列定义（column_definition）的基本格式是：column_name<data_type>[NULL|NOT NULL][[CONSTRAINT constraint_name] PRIMARY KEY|UNIQUE|[FOREIGN KEY]REFERENCES [schema_name.] referenced_table_name [(ref_column)][ON DELETE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}][ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]|CHECK (logical_expression)][DEFAULT constraint_expression]如果需要还可以定义计算列（computed_column_definition）和表级约束(table_ constraint)●空值约束NOT NULL和NULL●主关键字约束PRIMARY KEY●惟一性约束UNIQUE●参照完整性约束FOREGIN KEY●默认值定义DEFAULT●取值范围约束CHECK（2）修改表结构的命令：ALTER TABLE schema_name.table_name{ ALTER COLUMN column_name <data_type>[NULL|NOT NULL]| ADD< column_definition > | < computed_column_definition >| < table_constraint >| DROP[ CONSTRAINT ] constraint_name COLUMN column_name }（3）删除数据表的命令格式：DROP TABLE table-name2、实验步骤与结果（1）调出SQL Server2005软件的用户界面，进入SQL SERVER MANAGEMENT STUDIO。