chapter05-数据库与表的管理

Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,-,95,-,数据库与表的管理,本书提供一个销售管理系统的示例数据库,mySales,，其数据表结构如第,4,章所述。本书第,5,章后数据库技术与应用部分的绝大部分实例基于该数据库。该数据库生成安装方法如下：,打开,SQL Server,数据库管理系统，使用,SQL Server,身份验证，输入服务器名称、,sa,和密码。也可以使用,windows,身份验证，只需输入服务器名称。具体如下图所示：,实例数据库,mySales,的安装方法,实例数据库,mySales,的安装方法,实例数据库,mySales,的安装方法,在,SQL Server2008,中打开本书配套资料中的,mysales.sql,文件，然后按,F5,或“执行”按钮运行该文件。稍后（大约,10,秒），系统提示“,命令已成功完成,”,，,表明数据库已经生成完成，这时，在左边的下拉式菜单中可以找到,mysales,数据库，至此数据库已经生成完毕。具体如下图所示：,实例数据库,mySales,的安装方法,第,5,章 数据库与表的管理,数据库的创建与维护,表的创建与维护,表数据,的更新,创建数据库,第,五,章,修改数据,删除数据,创建表,修改表,删除表,表概述,数据类型,插入数据,数据完整性约束,唯一性约束,检验性约束,缺省约束,主键约束,外键约束,非空约束,5.1.2,创建数据库,基本语法：,CREATE DATABASE,database_name, ON ,.,n, LOG ON ,.,n, FOR LOAD | FOR ATTACH , := ( NAME =,logical_, = ,os_, , SIZE =,size, , MAXSIZE = ,max_size,| UNLIMITED , , =,growth_increment, ) ,.,n,主要参数：,NAME,：指定数据文件的逻辑名。在创建数据库后，可以引用文件逻辑名进行修改和删除操作。逻辑名在数据库中必须唯一。,5.1.2,创建数据库,：指定数据文件在操作系统中的路径和物理文件名。文件所在的路径必须是在创建数据库时已经存在的一个文件目录或磁盘符号。,SIZE,。指定文件的初始大小。文件大小可以使用,KB,、,MB,、,GB,或,TB,，默认值为,MB,。,MAXSIZE,。指定文件可以增长到的最大值，该参数是一个整数。如果没有指定最大值，那么文件将增长到磁盘变满为止，相当于使用,UNLIMITED,选项值。,。指定文件增长的增量，即为数据库文件每次需要新的空间时添加空间。该值可以,MB,、,KB,、,GB,、,TB,或百分比（,%,）为单位。如果没有指定，则默认值为,10%,。,5.1.2,创建数据库,实例,5-1,创建简单的数据库，只包含一个数据文件和日志文件。,本例使用两种方式创建数据库。第,1,个示例是一种最简单的数据库创建方式，由于没有指定任何参数项，因此数据文件和日志文件的名称、路径和大小都采用系统默认值（即,SQL Server,系统的安装路径）,。,USE masterCREATE DATABASE myDB1,GO,解析：,很多情况下，在上机实验过程中，可以使用这种简单的方式创建数据库。,5.1.2,创建数据库,实例,5-1,（续）,:,第,2,个示例是一种最常用的数据库创建方式，由于指定了数据文件的名称、路径和大小，其日志文件的名称、路径和大小将根据数据文件自动生成。,USE master,CREATE DATABASE myDB2,ON,( NAME = myDB2_Dat1, = c:myDBFmyDB2Dat1.mdf, SIZE = 5,MAXSIZE = 500, = 1 ),解析：,由于,SQL Server,不会自动创建文件路径，因此文件路径,c:myDBF,必须在创建数据库之前已经存在。由于没有具体指定事务日志文件的各项参数，其文件大小为主文件大小的,25%,和,512 KB,中的较大值。虽然这,2,个示例都没有明确指定事务日志文件，但系统会自动创建该文件，其文件名称、路径和大小与主数据文件相关。,5.1.2,创建数据库,实例,5-2,创建数据库，指定多个数据文件和事务日志文件的路径和大小。,本实例创建一个数据库,myDB,。该数据库包含多个数据文件和日志文件，不同的数据文件和日志文件之间使用西文逗号（,）分隔，数据文件与日志文件之间使用,LOG ON,分隔。在同一个数据库中不同数据文件的逻辑名（,Name,）不允许重名的。,实例,5-2,程序代码,运行实例,5-2.sql,5.1.2,创建数据库,实例,5-2,（续）,USE master,CREATE DATABASE myDB,ON,( NAME = myDB_dat1, = C:myDBFmyDBdat1.mdf, SIZE = 10, MAXSIZE = 500, = 5 ),( NAME = myDB_dat2, = C:myDBFmyDBdat2.ndf, SIZE = 5MB, MAXSIZE =UNLIMITED, = 10% ),LOG ON,( NAME = myDB_log1, = C:myDBFmyDBlog1.ldf, SIZE = 5MB, MAXSIZE = 250MB, = 5MB ),( NAME = myDB_log2, = C:myDBFmyDBlog2.ldf, SIZE = 5MB, MAXSIZE = UNLIMITED, = 1MB ),表（,Table,）是存储数据库中所有数据的一个对象。表定义为列的集合。与电子表格（,Spreadsheet,）相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。,例如，在包含公司员工数据的表中，每一行代表一名员工，各个列分别表示雇员的详细资料，如员工编号、姓名、地址、职位以及电话号码等。,5.2,表的创建与维护,5.2.1,表概述,SQL Server,中的表由两个主要组成部分：,列（,Column,）,。每一列描述表所建模的对象的一些属性。列也称为,字段（,Field,）。,行（,Row,）,。每一行描述表所建模的对象的一个个体的存在。行也称为,记录,（,Record,）。,在很多情况下，行和记录、列和字段这两对术语是可以互相替代的。,创建一张表的最有效方法是一次性全部定义好表中所需要的一切内容（字段），包括它的数据限定和其他要素。当然，也可以在创建后再对表进行修改，例如，添加一些约束、索引、缺省值、规则和其它对象。,5.2.2,数据类型,SQL Server,系统提供的主要数据类型见表,5-2,。,表,5-2 SQL Server,的常用数据类型,整型数据（,Integer,）,bit,取值为,0,或,1,int,取值范围从,-231(-2,147,483,648),到,231-1(2,147,483,647),。存储大小是,4,个字节。,.,smallint,取值范围从,215 (-32,768),到,215-1 (32,767),。存储大小是,2,个字节。,tinyint,取值范围从,0,到,255,。存储大小是,1,个字节。,bigint,取值范围从,-263,到,263-1,范围内的数字。存储大小为,8,个字节。,小数数据（,Decimal,和,Numeric,）,decimal,(p,s),取值范围从,-1038 +1,到,1038 1,的固定精度和小数位的数字数据。精度,p,取值,1,到,38,之间。小数位数,s,指定小数点的最大位数，默认为,0,。存储大小根据精度而定。,numeric,功能上等同于,Decimal,。,表,5-2,（续,1,）,5.2.2,数据类型,货币型数值（,money,和,smallmoney,）,money,取值范围,-263,到,263 - 1,之间。存储大小为,8,个字节。,Smallmoney,取值范围,-214,748.3648,与,+214,748.3647,之间。存储大小为,4,个字节。,浮点型数值,float(n),介于,-1.79E + 308,与,1.79E + 308,之间的浮点精度数字。,n,必须为从,1,到,53,之间的值。存储字节数根据,n,所在的范围和精度而定。,Real,介于,-3.40E + 38,到,3.40E + 38,的浮点精度数字。存储大小是,4,个字节。,日期时间型（,Datetime,和,Smalldatetime,）,datetime,介于,1753,年,1,月,1,日到,9999,年,12,月,31,日的日期和时间数据，精确到百分之三秒或,3.33,毫秒）。例如：,01/01/98 23:59:59.999,，,2008-01-02 23:59:59.995,。,smalldatetime,从,1900,年,1,月,1,日到,2079,年,6,月,6,日的日期和时间数据，精确到分。,表,5-2,（续,2,）,5.2.2,数据类型,字符型（,Char,和,Varchar,）,char(n),固定长度的非,Unicode,字符数据，最大长度为,8,000,个字符。存储大小是,n,个字节。,varchar(n),可变长度的非,Unicode,数据，最长为,8,000,个字符。存储大小为输入数据的实际长度而不是,n,个字节。输入数据可以是,0,个字符。,text,可变长度的非,Unicode,数据，最大长度为,231-1(2,147,483,647),个字符。,Unicode,字符型,存储由,Unicode,标准定义的任何字符和其他字符集定义的所有字符，存储空间相当于非,Unicode,的两倍。,nchar(n),固定长度的,Unicode,数据，最大长度为,4,000,个字符。,nvarchar(n),可变长度,Unicode,数据，其最大长度为,4,000,字符。,Ntext,可变长度,Unicode,数据，其最大长度为,230-1,个字符。,表,5-2,（续,3,）,5.2.2,数据类型,二进制字符串,binary(n),固定长度的二进制数据，其最大长度为,8,000,个字节。,varbinary(n),可变长度的二进制数据，其最大长度为,8,000,个字节。,Image,可变长度的二进制数据，其最大长度为,231 - 1 (2,147,483,647),个字节。,5.2.2,数据类型,在,SQL Server,中，用户可以定义基于系统数据类型的用户自定义数据类型（,User-Defined Data Types,）。当多个表的列中要存储同样类型的数据，而且这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型。,在,SQL Server2000,中，使用系统存储过程,sp_addtype,和,sp_droptype,分别来创建或删除用户定义数据类型。,在,SQL Server2008,中，可以使用,CREATE TYPE,和,DROP TYPE,语句来分别替代,sp_addtype,和,sp_droptype,的功能。,建表语句的语法如下：,CREATE TABLE,database_name.schema_name.,table_name,(,column_name,data_type, NULL | NOT NULL , PRIMARY KEY | UNIQUE , REFERENCES ref_table ( ref_column ) , DEFAULT constant_expression , IDENTITY ( seed , increment ) , ROWGUIDCOL , CONSTRAINT constraint_name , ,.n ),5.2.3,创建表,1,创建简单的表,从,CREATE TABLE,语句的语法中可以看出，建立一个表需要考虑很多因素。下面从创建最简单的表开始，逐步讲述如何创建完整定义的数据表。,实例,5-7,简单的表定义，建表时使用多种数据类型，但没有定义约束条件。,本实例创建一张员工表,myEmployees,，使用的数据类型包括,nchar,、,nvarchar,、,int,、,ntext,和,image,等。在建表之前，先判断该表是否存在，如果存在则将其删除后再创建。在建表之后，使用,INSERT,语句向该表插入两条记录，同时使用,SELECT,语句将表中的数据检索出来。,5.2.3,创建表,实例,5-7,程序代码,运行实例,5-7.sql,实例,5-7,（续,1,）,USE mySales,IF EXISTS(SELECT 1 FROM sysobjects WHERE name=myEmployees) DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID nchar(7),LastName nvarchar(20),FirstName nvarchar(10),BirthDate datetime,Address nvarchar(60),City nvarchar(15),PostalCode nvarchar(10),Phone nvarchar(24),Photo image,Notes ntext ),GO,5.2.3,创建表,实例,5-7,（续,2,）,INSERT INTO myEmployees (EmployeeID, LastName, FirstName, BirthDate, Address, City, PostalCode, Phone),VALUES (PSA086M,Afonso,Pedro,1952-02-19,908 W. Capital Way,Tacoma,98401,(206) 555-9482),INSERT INTO myEmployees (EmployeeID, LastName, FirstName, BirthDate, Address, City, PostalCode, Phone),VALUES (H-B728F, Devon,Ann,1967-09-19, 8127 Otter Dr.,Redmond,98052,(206) 555-8122),GO,SELECT * FROM myEmployees,解析：,在创建表时，必须确定表的名称以及各个列的定义，各列之间用逗号分隔。在同一表中列名必须是唯一的，但不同的表之间列名可以相同。,5.2.3,创建表,2,定义,Identity,标识列,Identity,表示新增的列是一种,标识列,，当表中添加新行时，为该标识列提供一个唯一的、递增的数值。,定义,Identity,标识列的基本语法如下：,column_name,IDENTITY (,seed,increment,) ,这里，,seed,是表中插入的第一行标识列所使用的值（称为基数），,increment,是相对于前一行标识值的增量值。当新的行插入到有标识列的表中后，通过向基数添加增量来自动生成下一个标识值。,在定义,Identity,列时，必须同时指定基数和增量，或者二者都不指定。如果二者都未指定，则取默认值,(1, 1),。,一个表只能有一个,Identity,属性列，而且必须使用,Int,、,Smallint,、,Bigint,、,Tinyint,、,Decimal,或,Numeric,数据类型来定义该列。标识符列不允许空值。,5.2.3,创建表,3,定义,Uniqueidentifier,标识列,Identity,属性只在所使用的表上保持唯一。如果应用程序需要生成在整个数据库或世界各地所有网络计算机的全部数据库中均为唯一的标识符列，就需要使用,RowGuidCol,属性、,Uniqueidentifier,数据类型和,NewID(),函数。,定义,Uniqueidentifier,标识列的语法如下：,column_name,uniqueidentifier, ROWGUIDCOL NOT NULL DEFAULT (NEWID() ,UNIQUE | PRIMARY KEY NONCLUSTERED,这里，使用,NewID(),函数创建一个,Uniqueidentifier,类型的唯一值。,Uniqueidentifier,值从,xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,形式的字符串常量中转换，其中，每个,x,是一个在,0-9,或,a-f,范围内的十六进制数字。,5.2.3,创建表,实例,5-8,使用,IDENTITY,属性和,UNIQUEIDENTIFIER,数据类型创建表。,本实例创建一张员工表（,myEmployees,），在员工编号,EmployeeID,列的定义中使用,Identity,标识列来自动累加其值（第一条记录的,EmployeeID,值为,101,，第二条记录的值自动加,1,为,102,，,），在,RowID,列的定义中使用,Uniqueidentifier,类型、,ROWGUIDCOL,属性和,DEFAULT,约束，表示在插入新行时，可以不指定,RowID,的值，,SQL Server,自动根据,NewID(),函数生成一个随机数。,本实例借助系统函数,OBJECT_ID,判断表是否存在，如果存在，则将其删除。,5.2.3,创建表,实例,5-8,程序代码,运行实例,5-8.sql,实例,5-8,（续）,IF (OBJECT_ID (myEmployees) IS NOT NULL),DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID int IDENTITY(101,1),FirstName nvarchar(10), LastName nvarchar(20),BirthDate datetime,RowID uniqueidentifier ROWGUIDCOL DEFAULT NewID(),UNIQUE ),GO,INSERT INTO myEmployees (FirstName,LastName,birthdate) VALUES (Karin,Josephs,1972-02-19),INSERT INTO myEmployees (FirstName,LastName,birthdate) VALUES (Peter,Franken,1964-10-23),SELECT * FROM myEmployees,5.2.3,创建表,Identity,与,Uniqueidentifier,比较：,一个表可以有多个,Uniqueidentifier,列。每个表中可以指定一个具有,RowGuidCol,属性的,Uniqueidentifier,列。,RowGuidCol,属性指明列的,Uniqueidentifier,值可唯一地标识表中的行。,Uniqueidentifier,数据类型的主要优点是保证由,T-SQL,的,NewID(),函数或应用程序,Guid,函数生成的值在全局是唯一的。但,Uniqueidentifier,数据类型值长且难懂，比,Identity,标识占用更多的空间，其键值生成的索引可能会比,INT,键值实现的索引相对慢一些。,因此，在不需要全局唯一性，或者需要一个连续递增的键值时，一般考虑使用,Identity,属性。,5.2.3,创建表,4,定义计算列,计算列（,Computed Column,）,是指表中某列数据是由同一表中的其它列经过计算而得到的。,定义计算列的语法如下：,Column_name,AS,computed_expression, PERSISTED ,这里，,computed_expression,是定义计算列的表达式。表达式可以由非计算列的列名、常量、函数和变量组合而成，但计算列的表达式中不能包含子查询。,计算列的数据类型由,SQL Server,自动的根据表达式判断而确定，用户不能为计算列显式地指定一个数据类型。,由于计算列是一个基于其它列的表达式，因此它通常不会物理存储在表中（使用,SQL Server2008,中的,PERSISTED,关键字除外）,。,5.2.3,创建表,实例,5-9,创建计算列，并为计算列指定数据类型。,本实例中，示例,1,将,myExample,表中的计算列,avg1,定义为表达式,(low+high)/2,，,avg2,定义为表达式,1.0*(low+high)/2,的值。,avg1,列得到的是整型数值，因为它的表达式中所有变量和常量都是整型；,avg2,列得到的是带小数的实型数值，因为它的表达式中带有小数。,示例,2,在,myOrderItems,表中根据数量（,Qty,）和单价（,UnitPrice,）的乘积来定义一个金额的计算列（,Amount,）。在该计算列的表达式中使用了数据类型转换函数,CAST(),，强制将计算列的值转换成带两位小数的,Decimal,类型。,5.2.3,创建表,实例,5-9,程序代码,运行实例,5-9.sql,实例,5-9,（续,1,）,IF EXISTS(SELECT * FROM sysobjects WHERE name=myExample) DROP TABLE myExample,GO,CREATE TABLE myExample (,Low int, High int,Avg1 AS (Low + High)/2,Avg2 AS 1.0*(Low + High)/2 ),GO,INSERT INTO myExample (Low, High) VALUES (1,2),INSERT INTO myExample (Low, High) VALUES (3,4),SELECT * FROM myExample,GO,解析：,计算列的数据类型不能在定义表时直接指定，而是,SQL Server,自动根据表达式而确定。,5.2.3,创建表,实例,5-9,（续,2,）,IF EXISTS(SELECT 1 FROM sysobjects WHERE name=myOrderItems) DROP TABLE myOrderItems,GO,CREATE TABLE myOrderItems (,OrderID int, ProductID int,Qty int,UnitPrice money,Amount AS CAST(Qty*UnitPrice AS Decimal(12,2) ) PERSISTED),GO,INSERT INTO myOrderItems (ProductID, Qty, UnitPrice),VALUES (11, 16.6, 16.45),INSERT INTO myOrderItems (ProductID, Qty, UnitPrice),VALUES (29, 68.4, 123.79),5.2.3,创建表,5.2.4,修改表,修改表是指改变建表时定义的选项，例如增加、修改或删除列，还可以增加或删除约束条件。,修改表通过,ALTER TABLE,实现，主要子句见表,5.3,。,语句,功能,举例,ADD,新增列,ALTER TABLE t1 Add f1 Int Null,DROP COLUMN,删除列,ALTER TABLE t1 Drop Column f1,ALTER COLUMN,修改某列的定义,ALTER TABLE t1 Alter Column f2 nvarchar(100) Not Null,ADD CO,NSTRAINT,增加表级约束条件,ALTER TABLE t1 Add Constraint CK_t1_f1 CHECK(f10),DROP CONSTRAINT,删除一个约束条件,ALTER TABLE t1 Drop Constraint CK_t1_f1,表,5.3,修改表的主要命令子句及其功能,实例,5-10,在既有表中增加列、删除列或修改列的初始定义。,本实例,myExample,表在初始定义时包含两个列。,第一条,ALTER TABLE,语句向该表新添,Col3,、,Col4,两个列，其中,Col3,为,IDENTITY,列（原有各行中该列的数据将被填充进去），,Col4,为计算列。,第二条,ALTER TABLE,语句修改,Col2,列的数据类型，将其从数值型转换成字符型。,第三条,ALTER TABLE,语句将,Col1,这一列删除,。,5.2.4,修改表,实例,5-10,程序代码,运行实例,5-10.sql,实例,5-10,（续,1,）,IF (OBJECT_ID (myExample) IS NOT NULL),DROP TABLE myExample,GO,CREATE TABLE myExample ( Col1 int, Col2 int ),INSERT INTO myExample (Col1,Col2 )VALUES (1,100),GO,/*,修改表，新增两列。*,/,ALTER TABLE myExample ADD,Col3 INT IDENTITY(1,1),Col4 AS Col3+100,GO,INSERT INTO myExample (Col1, Col2 ) VALUES (2, 200),SELECT * FROM myExample,解析：,一条,ADD,子句可以增加多个列。,5.2.4,修改表,实例,5-10,（续,2,）,/*,将,Col2,数据类型修改成字符型。*,/,ALTER TABLE myExample ALTER COLUMN Col2 nchar(10) NOT NULL,SELECT * FROM myExample,/*,删除,Col1,这一列。*,/,ALTER TABLE myExample DROP Column Col1,SELECT * FROM myExample,GO,5.2.4,修改表,在实际应用中，修改表比定义表要复杂得多。例如，修改表中某一列的数据类型是存在风险的，因为表中既有的数据不一定支持新的数据类型。,除此之外，对于非常庞大的表，使用,ALTER TABLE,涉及所有行的修改。有时会花费很长时间来完成和产生所需的日志记录。因此，使用,ALTER TABLE,语句应当格外谨慎。,5.2.4,修改表,在数据库中，删除表与删除表中的行和列在概念上是不同的。如果删除了一张表的所有行，那么表及表的各项定义（包括约束）依然存在，只不过这时是一张空表而已。,一旦使用,DROP,语句，表才被真正删除，这时表中所有定义、数据、约束、索引、触发器及其它对象等将不再存在。,5.2.5,删除表,关于表的删除操作，还必须注意以下几点：,不能使用,DROP TABLE,删除被,FOREIGN KEY,约束引用的表，必须先删除,FOREIGN KEY,约束或引用表。,可以同时删除多个表。如果一个要删除的表引用了另一个也要删除的表的主键，则必须先列出包含该外键的引用表，然后再列出包含要引用的主键的表。,在删除表时，表的规则或默认值将被解除绑定，与该表关联的任何约束或触发器将被自动删除。,不能使用,DROP TABLE,删除系统表。,5.2.5,删除表,SQL Server,提供下列,6,种约束来强制数据的完整性：,PRIMARY KEY,主键约束,。强制一个表中不能有两行包含相同的主键值，也不能在构成主键的任何列中存放空值。,FOREIGN KEY,外键约束,。用于建立和加强两个表数据之间的链接。一个表中外键所在列的值必须在另一个表中候选键所对应列的取值范围内。,UNIQUE,唯一约束,。强制执行列值的唯一性，不允许表中的任何两行包含相同值。,CHECK,检查约束,。对存放到列中的值进行限制，不符合,CHECK,指定条件的值无法存储到数据表中去。,5.3,数据完整性约束,NOT NULL,非空性,。指定表中的列不能接受空值。,DEFAULT,默认约束,。对插入时没有显式提供值的列指定一个默认值。,同一个数据库的约束名是不允许重复的。,约束分为两个级别：列级约束和表级约束。,在建表时直接定义在列后面的约束，称为列级约束。,在建表时所有列之后（或建表之后使用,ALTER TABLE,语句）创建的约束称为表级约束。,定义列级约束时，约束的名称是可以缺省的，这时,SQL Server,自动为它生成一个名称。,但在使用,ALTER TABLE,定义表级约束时，必须明确指定约束的名称。,5.3,数据完整性约束,表中的主键必须满足下列两个条件：,任意两行都不具有相同的主键值,每一行都必须具有一个主键值（即主键列不允许空值）,5.3.1,PRIMARY KEY,主键约束,一个表只能有一个主键，主键通常定义在表的一列上，也可以定义在多个列上（这时称为组合主键）。,在使用多个列作为主键时，唯一性和非空条件必须应用到构成主键的所有列中，即所有组合列的值必须是唯一的（但单个列的值可以不唯一），组合列中的每一个列都必须为非空值。,主键可以在创建表时的某一列中定义，也可以通过表修改语句（,ALTER TABLE,）在创建表之后再定义。,在列定义中把一个列设置为主键称为列级主键约束，在列定义之后设置主键被称为表级主键约束。,组合主键必须使用表级约束进行定义。,5.3.1 PRIMARY KEY,主键约束,实例,5-11,创建单个列的主键约束。,在,CREATE TABLE,创建表时没有定义主键，使用,ALTER TABLE,添加一个主键。这时在建表定义,EmployeeID,列时，必须注明,NOT NULL,，否则无法为它增加主键约束。,本实例采用两种不同方式在员工表（,myEmployees,）中创建一个主键约束。示例,1,在定义列时直接定义主键约束（即列级约束），并指定约束名。,示例,2,在建表结束后，通过,ALTER TABLE,语句向表中添加主键约束（即表级约束），这时建表语句中的候选主键列必须指定非空（,NOT NULL,）约束。,5.3.1 PRIMARY KEY,主键约束,实例,5-11,（续,1,）,/*,示例,1,：在建表定义列时直接定义主键约束，主键约束名往往由字符,PK_,和表名组合而成。*,/,IF (OBJECT_ID(myEmployees) IS NOT NULL),DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID nchar(7) CONSTRAINT PK_myEmployees,PRIMARY KEY ,LastName nvarchar(20),FirstName nvarchar(10),BirthDate datetime ),GO,5.3.1 PRIMARY KEY,主键约束,实例,5-11,（续,2,）,/*,示例,2,：在创建表时没有定义主键约束，使用,ALTER TABLE,给这个表添加一个主键。注意：在建表定义,EmployeeID,列时，必须注明,NOT NULL,，否则无法为它增加主键约束。*,/,IF (OBJECT_ID(myEmployees) IS NOT NULL),DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID nchar(9) NOT NULL, -,添加非空约束,LastName nvarchar(20),FirstName nvarchar(10),BirthDate datetime ),GO,ALTER TABLE myEmployees ADD CONSTRAINT PK_myEmployees PRIMARY KEY ( EmployeeID ),5.3.1 PRIMARY KEY,主键约束,实例,5-12,创建表级组合主键约束，即表中的主键由多个列组成。,第一种方式,:,在建表语句中最后一列的定义之后，增加一个主键约束条件，这也属于定义表级约束。,第二种方式,:,在建表语句之后，通过,ALTER TABLE,添加组合主键约束。,5.3.1 PRIMARY KEY,主键约束,实例,5-12,（续）,/*,在建表时，在最后一个列定义之后，使用逗号，定义组合主键约束。*,/,IF (OBJECT_ID(myOrderItems) IS NOT NULL),DROP TABLE myOrderItems,GO,CREATE TABLE myOrderItems (,OrderID int NOT NULL, -,可以省略,ProductID int NOT NULL, -,可以省略,UnitPrice money,Quantity smallint,CONSTRAINT PK_myOrderItems PRIMARY KEY (OrderID ASC, ProductID ASC) ),GO,5.3.1 PRIMARY KEY,主键约束,实例,5-12,（续）,/*,使用,ALTER TABLE,和,ADD CONSTRAINT,向一个既有的表中增加组合主键约束。当然必须先删除原有的主键约束。*,/,ALTER TABLE myOrderItems DROP CONSTRAINT PK_myOrderItems,GO,ALTER TABLE myOrderItems,ADD CONSTRAINT PK_myOrderItems PRIMARY KEY (OrderID ASC, ProductID ASC),5.3.1 PRIMARY KEY,主键约束,在同一个数据库中，表与表之间往往会存在某些逻辑关系：一个表中某个列的数据必须在另一个表的某个列中是存在的。,在,SQL Server,中，可以通过,外键,来建立和加强两个表数据之间的链接关系，为列中的数据提供参照完整性约束。,外键约束可以参照另一表中的主键列，也可以参照另一表中具有,UNIQUE,约束的列，被参照列的值必须是唯一的（即具有,PRIMARY KEY,或,UNIQUE,约束）和非空的。,5.3.2 FOREIGN KEY,外键约束,实例,5-13,使用,REFERENCES,和,ALTER TABLE,创建外键约束，在一个表中定义列级和表级外键约束。,外键约束也可以分为列级约束和表级约束。外键约束使用,FOREIGN KEY,指定引用的列，由,REFERENCES,子句指定被引用的表和列。,5.3.2 FOREIGN KEY,外键约束,实例,5-13,（续）,IF (OBJECT_ID(myOrderItems) IS NOT NULL),DROP TABLE myOrderItems,GO,/*,定义列级外键约束条件。*,/,CREATE TABLE myOrderItems (,OrderID int NOT NULL CONSTRAINT,FK_myOrderItems_OrderID REFERENCES Orders(OrderID),ProductID int NOT NULL, UnitPrice money, Quantity int ),GO,/*,定义表级外键约束条件。*,/,ALTER TABLE myOrderItems ADD CONSTRAINT FK_myOrderItems_ProductID FOREIGN KEY(ProductID) REFERENCES Products(ProductID),5.3.2 FOREIGN KEY,外键约束,5.3.2 FOREIGN KEY,外键约束,实例,5-14,创建递归外键引用，即表中的外键列引用它自己所在表中的主键或唯一键。,FOREIGN KEY,约束可以引用同一表中的其他列，这种约束称为自我引用外键约束。对于存在自我引用的表，记录插入的次序很关键。被引用的记录必须先插入到表中，只有它们已经存在于表中时，其它引用它们的记录才能插入进去。,本实例在员工表（,myEmployees,）的,ReportsTo,列上创建一个外键约束，引用自己表中的主键,EmployeeID,列。,ReportsTo,的语义为当前员工的主管领导。,在插入记录时，第一个员工没有主管领导,(ReportsTo,值为,NULL),， 第二个员工的主管领导是第一个员工（,ReportsTo,值为第一条记录的主键值），第,3,个员工的主管领导是第二个员工（,ReportsTo,值为第二个条记录的主键值）。,实例,5-14,（续,1,）,IF (OBJECT_ID(myEmployees) IS NOT NULL),DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID nchar(7) PRIMARY KEY ,LastName nvarchar (20) NOT NULL,FirstName nvarchar (10) NOT NULL,Title nvarchar(40),ReportsTo nchar(7),CONSTRAINT FK_myEmployees_ReportsTo FOREIGN,KEY(ReportsTo) REFERENCES myEmployees(EmployeeID) ),5.3.2 FOREIGN KEY,外键约束,实例,5-14,（续,2,）,INSERT INTO myEmployees(EmployeeID, LastName, FirstName, Title, Reportsto) VALUES (FTA328M, Fuller, Andrew, Vice President,Sales, NULL),INSERT INTO myEmployees(EmployeeID, LastName, FirstName, Title, Reportsto) VALUES (BMS156M, Buchanan, Steven, Sales Manager, FTA328M),INSERT INTO myEmployees(EmployeeID, LastName, FirstName, Title, Reportsto) VALUES (DMN268F, Davolio, Nancy, Sales Representative, BMS156M),SELECT * FROM myEmployees,解析：,对于自我引用，必须注意记录插入的先后次序。被引用的记录必须先插入到表中，因为只有当它们已经存在于表中之后，其它引用它们的记录才能再插入进去。上述,3,条记录的插入次序不能颠倒。,5.3.2 FOREIGN KEY,外键约束,UNIQUE,唯一性,约束规定表中不同行某一列的值不能重复。,与,PRIMARY KEY,约束不同，一个表中可以定义多个,UNIQUE,约束，而且还允许为空值的列指定,UNIQUE,约束。,5.3.3 UNIQUE,唯一性约束,实例,5-15,创建和使用,UNIQUE,约束。,/*,示例,1,：在一个列上创建,UNIQUE,唯一约束。*,/,IF (OBJECT_ID(myEmployees) IS NOT NULL),DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID nchar(9) PRIMARY KEY ,LastName nvarchar(20),FirstName nvarchar(10),NationalIDNumber Varchar(18) UNIQUE ),GO,5.3.3 UNIQUE,唯一性约束,实例,5-15,（续,1,）,示例,2,在一个客户表,(myCustomers),上创建一个由客户名称（,CompanyName,）和所在城市（,City,）两个列组成的唯一约束，规定在同一座城市中任何两个客户的名称都不能相同。,/*,示例,2,：使用表级约束定义方法，在两个列上创建一个组合,UNIQUE,唯一约束。*,/,IF (OBJECT_ID(myCustomers) IS NOT NULL),DROP TABLE myCustomers,GO,CREATE TABLE myCustomers (,CustomerID nchar(5) PRIMARY KEY,CompanyName nvarchar(40) NOT NULL,Address nvarchar(60), City nvarchar(15) NOT NULL,CONSTRAINT UQ_myCustomers_companycity UNIQUE,(CompanyName ASC, City ASC) ),5.3.3 UNIQUE,唯一性约束,CHECK,检验性约束,用于定义列允许的格式和值，它通过限制列可接受的值，强制域的完整性。,例,:,通过,CHECK,约束可以把,Salary,列的取值范围限制在,$15,000,至,$100,000,之间，从而保证输入的薪资值不超出这个范围。为此，,CHECK,约束的逻辑表达式为：,CHECK (Salary =15000 AND Salary =0),Quantity smallint CHECK(Quantity= 0),Discount real CHECK(Discount= 0 and Discount=1),CONSTRAINT PK_myOrderItems PRIMARY KEY,(OrderID ASC, ProductID ASC ) ),5.3.4 CHECK,检验性约束,实例,5-16,（续,2,）,/*,示例,2,：带有字符验证模式的,CHECK,约束。,EmployeeID,和,Zip,的值必须遵循一个给定的模式，而,Birthday,的值应该小于系统当前日期（,GetDate(),）。*,/,IF (OBJECT_ID(myEmployees) IS NOT NULL),DROP TABLE myEmployees,GO,CREATE TABLE myEmployees (,EmployeeID nchar(7) PRIMARY KEY,CHECK( EmployeeID LIKE A-ZA-ZA-Z0-90-90-9FM,OR EmployeeID LIKE A-Z-A-Z0-90-90-9FM ) ,FirstName nvarchar(15), LastName nvarchar(20),BirthDate datetime CHECK ( BirthDateBirthDate,；,第二个,CHECK,约束规定员工编号（,EmployeeID,）的第一个字符必须与其姓名（,FirstName,）的第一个字符相同，即,LEFT(EmployeeID,1) = LEFT(FirstName,1),。,由于这两个约束条件都包含多个列，因此不能在某一个列上定义约束，而必须使用表级约束的定义方式。,5.3.4 CHECK,检验性约束,实例,5-17,（续,1,）,/*,在建表最后一列之后定义表级,CHECK,约束，因为这时所有的列都已经定义。*,/,IF (OBJECT_ID(myEmployees) IS NOT NULL) DROP TABL