表的创建与管理-第5章数据库对象的操作.ppt

第6章表的创建与管理 6 1数据类型6 2表的创建6 3表结构的修改6 4表的重命名与删除6 5向表中添加 更新 删除数据6 6数据完整性的概念与实施方法 上一章 返回目录 1 表的概念 表是数据库的数据对象 用于存储和操作数据的一种逻辑结构 是一系列列的集合 2 表的构成 表由表头和若干行数据构成 3 表的特性 表中每一行代表一个惟一的记录 每列代表一个域 教学过程 例1 学生成绩表 表名 表头 数据 行 列 4 对表的操作 填表 将数据写入表中 修改 改正表中的数据信息 删除 删除表中记录信息 查询 在表中按某些条件查找记录信息 5 注意 在SQLServer2005中 每个数据库最多可存储20亿个表 每个表可以有1024列 每行最多可以存储8060字节 SQLServer中表分为 永久表和临时表 其中永久表在创建后 除非用户删除否则将一直存放在数据库文件中 而临时表则会在用户退出或进行系统修复时被自动删除 6 数据类型 1 相关概念 概念 定义每个列所能存放的数据值和存储格式 精度 指数值数据中所存储的十进制数据的总位数 例如 tinyint类型可以表示范围是0 255 其精度为3 小数位数 指数值数据中小数点右边可以有的数字位数的最大值 例 89 658 其精度为5 小数位数为3 长度 存储数据所用的字节数 例 tinyint所用长度为1字节 2 种类 系统数据类型和用户自定义数据类型 SQLServer2005中列的数据类型既可以是系统数据类型也可以是用户自定义数据类型 6 1数据类型 6 1 1系统数据类型6 1 2自定义数据类型 6 1 1系统数据类型 1 整型数据类型2 浮点数据类型3 字符数据类型4 日期和时间数据类型5 文本和图形数据类型 6 货币数据类型7 位数据类型8 二进制数据类型9 特殊数据类型10 新增数据类型 具体种类 1 整型数据类型 整型数据类型是最常用的数据类型之一 它主要用来存储数值 可以直接进行数据运算 而不必使用函数转换 int integer int 或integer 数据类型可以存储从 231 2 147 483 648 到231 1 2 147 483 647 范围之间的所有正负整数 Smallint 可以存储从 215 32 768 到215 1范围之间的所有正负整数 Tinyint 可以存储从0到255范围之间的所有正整数 2 浮点数据类型 浮点数据类型用于存储十进制小数 浮点数值的数据在SQLServer中采用只入不舍的方式进行存储 Real 可以存储正的或者负的十进制数值 最大可以有7位精确位数 Float 可以精确到第15位小数 其范围从 1 79E 308到1 79E 308 Decimal和numeric Decimal数据类型和numeric数据类型完全相同 它们可以提供小数所需要的实际存储空间 但也有一定的限制 可以用2到17个字节来存储从 1038 1到1038 1之间的数值 3 字符数据类型 字符数据类型可以用来存储各种字母 数字符号和特殊符号 Char 其定义形式为char n 每个字符和符号占用一个字节的存储空间 Varchar 其定义形式为varchar n 用char数据类型可以存储长达255个字符的可变长度字符串 Nchar 其定义形式为nchar n Nvarchar 其定义形式为nvarchar n 4 日期和时间数据类型 Datetime 用于存储日期和时间的结合体 它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间 Smalldatetime 与datetime数据类型类似 但其日期时间范围较小 它存储从1900年1月1日到2079年6月6日内的日期 5 文本和图形数据类型 Text 用于存储大量文本数据 其容量理论上为1到231 1 2 147 483 647 个字节 但实际应用时要根据硬盘的存储空间而定 Ntext 与text数据类型类似 存储在其中的数据通常是直接能输出到显示设备上的字符 显示设备可以是显示器 窗口或者打印机 Image 用于存储照片 目录图片或者图画 其理论容量为231 1 2 147 483 647 个字节 6 货币数据类型 Money 用于存储货币值 存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中 存储范围为 922337213685477 5808到922337213685477 5808 精度为货币单位的万分之一 Smallmoney 与money数据类型类似 但其存储的货币值范围比money数据类型小 其存储范围为 214748 3468到214748 3467 7 位数据类型 Bit 称为位数据类型 其数据有两种取值 0和1 长度为1字节 8 二进制数据类型 Binary 其定义形式为binary n 数据的存储长度是固定的 即n 4字节 当输入的二进制数据长度小于n时 余下部分填充0 Varbinary 其定义形式为varbinary n 数据的存储长度是变化的 它为实际所输入数据的长度加上4字节 其它含义同binary 9 特殊数据类型 Timestamp 亦称时间戳数据类型 它提供数据库范围内的唯一值 反应数据库中数据修改的相对顺序 相当于一个单调上升的计数器 Uniqueidentifier 用于存储一个16字节长的二进制数据类型 它是SQLServer根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码 GloballyUniqueIdentifier 简写为GUID 10 新增数据类型 Bigint 用于存储从 263 9 223 372 036 854 775 807 到263 1 9 223 372 036 854 775 807 之间的所有正负整数 sql variant 用于存储除文本 图形数据和timestamp类型数据外的其它任何合法的SQLServer数据 table 用于存储对表或者视图处理后的结果集 这种新的数据类型使得变量可以存储一个表 从而使函数或过程返回查询结果更加方便 快捷 6 1 2自定义数据类型 创建用户定义类型要提供三个参数 名称 系统数据类型 是否允许为空 创建方法 1 使用企业管理器 EnterpriseManager 创建用户自定义数据类型 删除数据类型 2 利用系统存储过程创建用户自定义数据类型 系统存储过程sp addtype为用户提供了T SQL语句创建自定义数据类型的途径 其语法形式如下 sp addtype typename type phystype system data type nulltype null type owner owner name 使用Tranact SQL创建用户定义数据类型 格式 sp addtype类型名 系统数据类型 是否为空 例 sp addtypewx int notnull 使用Tranact SQL删除用户定义数据类型 格式 sp droptype类型名例 sp addtypewx 例子6 1 自定义一个地址数据类型 execsp addtypeaddress varchar 80 notnull 其运行结果如下 1row s affected typeadded 例子6 2 删除自定义的生日数据类型 execsp droptypebirthday其运行结果如下 1row s affected 0row s affected Typehasbeendropped 例 在XK数据库中创建一个名为TEL 电话号码 的用户自定义数据类型 定义为VARCHAR数据类型 长度为3 且不能为NULLUSEXkGOEXECsp addtypeTel VARCHAR 30 NOTNULL GO 例 删除刚建立的用户自定义数据类型USEXkGOEXECsp droptypeTelGO 6 2表的创建 在SQLServer2005中 每个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 也就是可以定义1024个字段 SQLServer2005提供了两种方法创建数据库表 第一种方法是利用企业管理器 EnterpriseManager 创建表 另一种方法是利用Transact SQL语句中的create命令创建表 1 利用EnterpriseManager创建表 在EnterpriseManager中 展开指定的服务器和数据库 打开想要创建新表的数据库 用右键单击表对象 从弹出的快捷菜单中选择新建表选项 或者在工具栏中选择图标 就会出现新建表对话框 在该对话框中 可以定义列的以下属性 列名称 数据类型 长度 精度 小数位数 是否允许为空 缺省值 标识列 标识列的初始值 标识列的增量值和是否有行的标识 然后根据提示进行设置 2 利用create命令创建表 其语法形式如下 CREATETABLE database name owner owner table name column nameAScomputed column expression n ON filegroup DEFAULT 创建表的各参数的说明如下 database name 用于指定在其中创建表的数据库名称 owner 用于指定新建表的所有者的用户名 table name 用于指定新建的表的名称 column name 用于指定新建表的列的名称 computed column expression 用于指定计算列的列值的表达式 ON filegroup DEFAULT 用于指定存储表的文件组名 TEXTIMAGE ON 用于指定text ntext和image列的数据存储的文件组 data type 用于指定列的数据类型 DEFAULT 用于指定列的缺省值 constant expression 用于指定列的缺省值的常量表达式 IDENTITY 用于指定列为标识列 Seed 用于指定标识列的初始值 Increment 用于指定标识列的增量值 NOTFORREPLICATION 用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用 即不足的生成列值 使得复制的数据行保持原来的列值 ROWGUIDCOL 用于指定列为全球唯一鉴别行号列 COLLATE 用于指定表使用的校验方式 column constraint和table constraint 用于指定列约束和表约束 例子6 3 创建了一个雇员信息表 其SQL语句的程序清单如下 CREATETABLEemployee numberintnotnull namevarchar 20 NOTNULL sexchar 2 NULL birthdaydatetimenull hire datedatetimeNOTNULLDEFAULT getdate professional titlevarchar 10 null salarymoneynull memontextnull 例子6 4 USEXkGO 创建班级信息表ClassCREATETABLEClass ClassNochar 8 NOTNULL DepartNochar 2 NOTNULL ClassNamechar 20 NOTNULL GO 创建学生信息表StudentCREATETABLEStudent StuNochar 8 NOTNULL ClassNochar 8 NOTNULL StuNamechar 8 NOTNULL Pwdchar 8 NOTNULL GO 创建学生选课表StuCouCREATETABLEStuCou StuNochar 8 NOTNULL CouNochar 3 NOTNULL WillOrdersmallintNOTNULL Statechar 4 NOTNULL RandomNumvarchar 50 NULL GO 6 3表结构的修改 6 3 1使用T SQL的ALTERTABLE语句修改表ALTERTABLE database name owner table name WITHNOCHECK ADD column namedatatype column constraints table constraint next col name next table constraint DROP CONSTRAINT constraint name COLUMNcolumn n 其中 ldatabase name 将要修改的表所在的数据库 owner 表对象的所有者 table name 将要修改的表的名称 WITHNOCHECK 允许check或者foreignkey约束加到一个表 而不验证现有的数据是否违反约束 若没有指定这个选项 增加的任何约束都要对现有数据进行检查 如果有任何约束违反 ALTERTABLE语句将被终止 ADD 在现有表中增加一个新的数据项或一个约束 column name 增加的字段名称 datatype 新增字段的数据类型 DROP 删除现有表中的一个字段或约束 CONSTRAINT 删除数据库中的约束 COLUMN 指定删除数据库中的列 例子6 5 创建了一个雇员信息表 其SQL语句的程序清单如下 createtableemployees idchar 8 primarykeynamechar 20 notnull departmentchar 20 null memochar 30 nullageintnull altertableemployeesaddsalaryintnulldropcolumnagealtercolumnmemovarchar 200 null 例子6 6为XK数据库中的CLASS表创建基于CLASSNO 列名为PK CLASS的主键 再将其删除 USEXkGO 增加主键约束ALTERTABLEClassADDCONSTRAINTPK ClassPRIMARYKEYCLUSTERED ClassNo GO 删除主键约束ALTERTABLEClassDROPCONSTRAINTPK ClassGO 6 3 2使用SQLServer企业管理器修改表 利用企业管理器增加 删除和修改字段 在企业管理器中 打开指定的服务器中要修改表的数据库 用右键单击要进行修改的表 从弹出的快捷菜单中选择设计表选项 则会出现设计表对话框 在该对话框中 可以利用图形化工具完成增加 删除和修改字段的操作 例子6 7修改XK数据库中的表STUDENT将STUNO设置为主键 6 4表的重命名与删除 重命名 1 使用T SQL语言进行例 USEXkGoEXECsp rename Depart Department GO2 使用企业管理器 删除表 1 利用企业管理器删除表在企业管理器中 展开指定的数据库和表格项 用右键单击要删除的表 从快捷菜单中选择 除去表 选项 则会出现除去对象对话框 单击 全部删除 按钮 即可删除表 2 利用DROPTABLE语句删除表DROPTABLE语句可以删除一个表和表中的数据及其与表有关的所有索引 触发器 约束 许可对象 DROPTABLE语句的语法形式如下 DROPTABLEtable name 例子6 8删除数据库XK中的表CLASS USEXkGoDROPTABLEClassGO 6 5表的数据操作 6 5 1向表中插入数据在企业管理器中向表中插入数据用INSERT语句向表中插入数据INSERT INTO table nameWITH n view name rowset function limited column list VALUES DEFAULT NULL expression n derived table execute statement 其中 lINTO 一个可选的关键字 可以将它用在INSERT和目标表之间 ltable name 将要接收数据的表或table变量的名称 lWITH n 指定目标表所允许的一个或多个表提示 需要有WITH关键字和圆括号 不允许有READPAST NOLOCK和READUNCOMMITTED lview name 视图的名称及可选的别名 通过view name来引用的视图必须是可更新的 由INSERT语句所做的修改不能影响视图的FROM子句中引用的多个基表 例如 在多表视图中的INSERT必须使用column list column list是指引用来自一个基表的列 lrowset function limited 是OPENQUERY或OPENROWSET函数 lcolumn list 要在其中插入数据的一列或多列的列表 必须用圆括号将column list括起来 并且用逗号进行分隔 lVALUES 引入要插入的数据值的列表 对于column list 如果已指定 中或者表中的每个列 都必须有一个数据值 必须用圆括号将值列表括起来 如果VALUES列表中的值与表中列的顺序不相同 或者未包含表中所有列的值 那么必须使用column list明确地指定存储每个传入值的列 lDEFAULT 强制SQLServer装载为列定义的默认值 如果对于某列并不存在默认值 并且该列允许NULL 那么就插入NULL 对于使用timestamp数据类型定义的列 插入下一个时间戳值 DEFAULT对标识列无效 lExpression 一个常量 变量或表达式 表达式不能包含SELECT或EXECUTE语句 lderived table 任何有效的SELECT语句 它返回将装载到表中的数据行 lexecute statement 任何有效的EXECUTE语句 它使用SELECT或READTEXT语句返回数据 6 5 2修改表中数据 在企业管理器中修改表数据用UPDATE语句修改表数据UPDATE table nameWITH n SET column name expression DEFAULT NULL WHERE 其中 ltable name 需要更新的表的名称 如果该表不在当前服务器或数据库中 或不为当前用户所有 这个名称可用链接服务器 数据库和所有者名称来限定 lWITH n 指定目标表所允许的一个或多个表提示 需要有WITH关键字和圆括号 lSET 指定要更新的列或变量名称的列表 lcolumn name 含有要更改数据的列的名称 column name必须驻留于UPDATE子句中所指定的表或视图中 标识列不能进行更新 lexpression 变量 字面值 表达式或加上括弧的返回单个值的subSELECT语句 lDEFAULT 指定使用对列定义的默认值替换列中的现有值 如果该列没有默认值并且定义为允许空值 这也可用来将列更改为NULL lWHERE 指定条件来限定所更新的行 6 5 3删除表中数据 在企业管理器中删除表中数据用DELETE语句删除表中数据DELETE FROM table nameWITH n view name rowset function limited FROM n WHERE 其中 lFROM 是可选的关键字 可用在DELETE关键字与目标table name view name或rowset function limited之间 ltable name 是要从其中删除行的表的名称 在其作用域内的table变量 或是将OPENDATASOURCE函数作为服务器名称的由四部分组成的表名 或视图名称 还可以在DELETE语句中作为表源使用 lWITH n 指定目标表所允许的一个或多个表提示 需要有WITH关键字和圆括号 不允许有READPAST NOLOCK和READUNCOMMITTED lview name 是视图名称 在视图的FROM子句中 view name引用的视图必须可更新且正确引用一个基表 lFROM 指定附加的FROM子句 这个对DELETE的Transact SQL扩展使用户得以从指定数据 并从第一个FROM子句内的表中删除相应的行 lWHERE 指定用于限制删除行的条件 如果没有提供WHERE子句 则DELETE删除表中的所有行 6 6数据完整性的概念与实施方法 数据库应用程序开发中的一个重要步骤就是设计和实施数据的完整性 并确定实施数据完整性的最佳方案 数据完整性是指数据库中数据的正确性和一致性 是衡量数据库质量的一个重要标准 在使用INSERT UPDATE和DELETE命令修改数据库中的数据时 数据的完整性可能被破坏 如插入了一个不存在学生的成绩 或将学生的班号修改为一个不存在的班级等 通过定义数据完整性规则 SQLServer可以通过自身提供的完整性规则有效地管理数据的输入 而不必使用额外的应用程序来协助管理 这样一方面可以节省系统开销 另一方面将使数据库中的数据独立于应用程序 使创建开放式数据库系统成为可能 实现数据完整性与业务规则 数据完整性的落实可为数据库中的数据质量提供保障 在对数据表进行规划时的两个重要步骤就是对数据列的有效数值加以识别 并决定如何落实列内数据的完整性 数据完整性分为四种类型 并可通过多种方式得到落实 1 域完整性 域完整性是指给定列的输入有效性 强制域有效性的方法有 通过限制数据类型 包括自定义数据类型 格式 CHECK约束和规则 或可能的取值范围 FOREIGNKEY约束 CHECK约束 DEFAULT定义 NOTNULL定义和规则 来实现 如 性别字段只能取 男 或 女 课程成绩取值范围为0 100 姓名字段不能为空 性别字段的默认值为 男 等等 2 实体完整性 实体完整性是指表中的每一行必须是惟一的 实体完整性强制表中的所有记录都有一个惟一的标识列 这个惟一标识列可能是一列 也可能有若干列的组合 强制实体完整性的方法有 UNIQUE约束 PRIMARYKEY约束或IDENTITY属性 如 在学生情况表中 学生的学号是惟一的 它与每个学生一一对应 成绩表中 学号 课程号是惟一的 它与每个学生的每门课相对应 对于实体完整性 有如下规则 实体完整性规则针对基本关系 一个基本关系表通常对应一个实体集 例如 学生关系对应学生集合 现实世界中的实体是可以区分的 它们具有一种唯一性质的标识 例如 学生的学号 教师的职工号等 在关系模型中 主关键字作为唯一的标识 且不能为空 3 参照完整性 引用完整性 参照完整性确保数据库中数据的一致性 在SQLServer中 参照完整性基于外键 引用表 与主键 被引用表 之间或外键与惟一键之间的关系 通过FOREIGNKEY和CHECK约束 通过外键将引用表和被引用表关联起来 参照完整性通过已定义的表间关系 确保键值在所有表中都必须一致 因此不能引用不存在的值 即指向不存在的行 如果在被引用的表中 某一记录被外部键引用 则该记录就不能删除 若需要更改键值 那么在整个数据库中 对该键值的所有引用都要进行一致的更改 以保证数据的参照完整性 建立外键约束 用来控制数据库相关表之间的主关键字和外部关键字之间数据一致性的规则 相关表之间的数据一致性要求 有子必有父 子表中的每一个记录在对应的主表中必须有一父记录 插子必有父 在父表中修改记录时 如果修改了主关键字的值 则子表中相关记录的外部关键字值必须同样修改父删子必删 在父表中删除记录时 与该记录相关的子表中的记录必须全部删除 注 参照完整性是建立在库表之间永久关系基础之上的 4 用户定义完整性 用户可以根据需要 可以在SQLServer中定义不属于上述标准类别的特定规则的用户完整性定义 所有的完整性类型都支持用户自定义完整性 CREATETABLE中的所有列级和表级约束 存储过程和触发器 6 5 2数据完整性实施方法 目前有许多关系的DBMS提供了多种定义完整性约束条件的功能和检查是否违背完整性约束条件的方法 DBMS若发现了用户的操作使数据库违背了完整性约束条件 将采取一定的措施 拒绝用户执行此操作 约束分为列级约束和表级约束 如果约束只对一列起作用 应定义为列级约束 如果约束对多列起作用 应定义为表级约束 常用的关系数据库实现完整性约束的方法有 1 声明型数据完整性 作为数据库定义或说明的一部分在语法中实现 是SQL语言的参饰 在CreateTable和AlterTable定义中使用constraint和default语句限制表中数据的值 使用这种方法实现数据完整性简单且不容易出错 系统将直接实现数据完整性的要求定义在表和列上 在使用createtable和altertable语句中 进行完整性限定的子句有 缺省约束 defaultconstraints 当向数据库中的表中插入数据时 如果用户没有明确给出某列的值 则系统自动为该字段输入指定的值 检查约束 checkconstraints 限制插入列中的值 如性别字段输入时只允许出现男或女 其他值一律不许出现 主键约束 primarykeyconstraints 要求表中在指定的列上没有两行具有相同的值 且不允许为空值 唯一约束 uniqueconstraints 要求表中在指定的列上没有两行具有相同的值 允许为空值 参照约束 referenceconstraints 外键约束 foreignkeyconstraints 要求指定列或者外键中正被插入或者更新的新值 必须在主表的相应主键中已经存在 2 过程型数据完整性一般先写出实施数据完整性的条件 然后再写出强制该条件所执行的用于保证数据完整性的脚本 通常由触发器和存储过程实现 6 6 3约束 建立和使用约束的目的在于保证数据的完整性 设计表时需要定义列的有效值并通过列中数据 行中数据及表间数据决定如何强制保证数据的完整性 约束定义关于列中允许值的规则 是强制完整性的首选方法 约束是独立于表结构的 它作为数据库定义的一部分在创建表时声明 可以通过企业管理器或ALTERTABLE语句添加或删除 当表被删除时 表所附带的所有约束同时被删除 1约束的类型 在SQLServer2005中有5种约束 主键约束 primarykeyconstraint 唯一性约束 uniqueconstraint 检查约束 checkconstraint 缺省约束 defaultconstraint 外部键约束 foreignkeyconstraint NOTNULL约束 NOTNULL约束又称非空约束 表示使用该约束的列不允许使用空值 若该列是主键 则系统强制主键列为非空约束 而其它列的非空约束必须根据需要加以设置 非空约束可以在CREATETABLE建表时实现 在表创建完成后 也可以使用修改列属性的T SQL语句完成 ALTERTABLEtable nameALTERCOLUMNcolumn namenew data type NULL NOTNULL 例5 1 将学生信息表 Student 中的学生姓名列强制非空约束 可以通过修改列属性的方法完成 ALTERTABLEStudentALTERCOLUMNStudentNameVARCHAR 10 NOTNULL 2 使用T SQL语言创建 管理约束 1 使用CREATETABLE语句创建约束的一般语法CREATETABLEtable name column namedata type CONSTRAINTconstraint name PRIMARYKEY CLUSTERED NONCLUSTERED UNIQUE CLUSTERED NONCLUSTERED FOREIGNKEY REFERENCESref table ref column DEFAULTconstraint expression CHECK logical expression 2 缺省约束当表中的某列必须有值 并且当使用INSERT命令向数据表插入记录数据时 若用户没有明确指定该列的值 该列也需要有一个明确值的情况下 就需要使用DEFAULT约束 使用了DEFAULT约束的列 SQLServer将根据用户的插入数据 自动维护域完整性 当用户插入时该列有指定值 则该列使用该值插入 否则使用DEFAULT约束中指定的默认值 DEFAULT约束可以使用以下方法实现 l作为表定义的一部分在创建表时创建 l添加到现有表中 l删除现有的DEFAULT定义 ALTERTABLEStudent 添加DEFAULT约束 ADDCONSTRAINTDefault SexDEFAULT 男 FORSex 使用缺省约束时 应该注意以下几点 每个字段只能定义一个缺省约束 如果定义的缺省值长于其对应字段的允许长度 那么输入到表中的缺省值将被截断 不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上 如果字段定义为用户定义的数据类型 而且有一个缺省绑定到这个数据类型上 则不允许该字段有缺省约束 例为数据库JWGL中表teacher的SEX 性别 字段创建一个缺省约束 缺省值为1 男 USEJWGLGOALTERTABLEteacherADDCONSTRAINTdefault sexDEFAULT1FORSEX 例 在XK数据库中为StuCOU表创建名为DF StuCOU State的DEFAULT约束 该约束使State列的值默认为 报名 USEXkGOALTERTABLEStuCouADDCONSTRAINTDF StuCou StateDEFAULT 报名 FORStateGO 例 删除刚建立的名为StuCOU State的DEFAULT约束USEXkGOALTERTABLEStuCouDROPCONSTRAINTDF StuCou StateGO 3 检查约束CHECK约束用于限制输入到列中的值的范围 从而强制数据库中数据的域完整性 它与FOREIGNKEY约束控制列中数值类似 区别在于它们判断值有效的方法不同 FOREIGNKEY约束从另一个表中获得有效数值列表 而CHECK约束通过逻辑表达式的成立与否加以判断 一个列级检查约束只能与限制的字段有关 一个表级检查约束只能与限制的表中字段有关 一个表中可以定义多个检查约束 每个CREATETABLE语句中每个字段只能定义一个检查约束 在多个字段上定义检查约束 则必须将检查约束定义为表级约束 当执行INSERT语句或者UPDATE语句时 检查约束将验证数据 检查约束中不能包含子查询 创建检查约束常用的操作方法有两种 1 企业管理器操作法 使用企业管理器创建检查约束 与创建唯一性约束类似 2 Transact SQL语句操作法 检查约束的Transact SQL语句操作法 其语法形式如下 CONSTRAINTconstraint nameCHECK NOTFORREPLICATION logical expression 例 为数据库JWGL中表student course的GRADE 成绩 字段创建一个检查约束 使得GRADE的值在0 100之间 USEJWGLGOALTERTABLEstudent courseADDCONSTRAINTcheck gradeCHECK grade 0andgrade 100 例子 创建了一个学生信息表 其中输入性别字段值时 只能接受 F 或者 M 并且为phonenum字段创建检查约束 限制只能输入类似01080798654之类的数据 而不能随意输入其他数据 createtablestudent Idchar 8 namechar 8 sexchar 2 phonenumintconstraintchk sexcheck sexin F M Constraintchk phonenumcheck phonenumlike 010 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 例 为专业情况表 Speciality 中的专业代码 SpecialityID 列创建一个CHECK约束 将专业代码限定为五位 其中第一位只能是 a z 或 A Z 其它位为 0 9 使用其它符号将被认为是无效的 USEEducational 选择当前数据库GO 定义CHECK约束ALTERTABLESpecialityADDCONSTRAINTChk SpecialityIDCHECK SpecialityIDLIKE A Z 0 9 0 9 0 9 0 9 例 在XK数据库中为Student表创建名为CK StuNO的CHECK约束 该约束限制StuNO列中只允许8为数字USEXkGOALTERTABLEStudentADDCONSTRAINTCK StuNoCHECK StuNolike 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 GO 例 删除刚建立的CHECK约束USEXkGOALTERTABLEStudentDROPCONSTRAINTCK StuNoGO 例 在XK数据库中为StuNO表创建名为CK WillORder的CHECK约束 该约束限制WillORder的值只能在1 5之间USEXkGOALTERTABLEStuCouADDCONSTRAINTCK WillOrderCHECK WillOrder 1ANDWillOrder 5 GO 4 主键约束在一个表中 存在着这样的字段 它在每个记录中的值都是惟一的 因此可以用来标识表中的各个不同记录 这样的字段被称为关键字 关键字可以是列或列的组合 例如学生情况表中的StudentID列就是一个关键字 因为每个学生的学号必须是惟一的 当表中有两个或两个以上的列或列的组合满足以上条件时 称这些列或列的组合为候选关键字 当表的关键字多于一个时 可以将其中的一个关键字作为主键 PRIMARYKEY 主键的添加 删除和修改操作方法有两种 一 企业管理器操作法 在企业管理器中 用右键单击要操作的数据库表 从弹出的快捷菜单中选择设计表选项 然后根据提示操作 二 Transact SQL语句操作法 其语法形式如下 CONSTRAINTconstraint namePRIMARYKEY CLUSTERED NONCLUSTERED column name n 主键约束各参数的说明如下 constraint name 用于指定约束的名称 约束的名称在数据库中应该是唯一的 如果不指定 则系统会自动生成一个约束名 LUSTERED NONCLUSTERED 用于指定索引的类型 即聚簇索引或者非聚簇索引 CLUSTERED为默认值 column name 用于指定主键的列名 主键最多由16个列组成 例 将数据库JWGL中teacher表的teacher id 教师号 字段设为主键 USEJWGLGOALTERTABLEteacherADDCONSTRAINTPK teacher idPRIMARYKEYclustered teacher id 例 在XK数据库中为STUCOU表中的StuNo和CouNo列的组合创建名为IX StuNoCouNo的PRIMARYKEY约束USEXkGOALTERTABLEStuCouADDCONSTRAINTIX StuNoCouNoPRIMARYKEY StuNo CouNo GO 例 删除刚建立的名为IX StuNoCouNo的PRIMARYKEY约束USEXkGOALTERTABLEStuCouDROPCONSTRAINTIX StuNoCouNoGO 5 唯一性约束 唯一性约束用于指定一个或者多个列的组合的值具有唯一性 以防止在列中输入重复的值 当使用唯一性约束时 需要考虑以下几个因素 使用唯一性约束的字段允许为空值 一个表中可以允许有多个唯一性约束 可以把唯一性约束定义在多个字段上 唯一性约束用于强制在指定字段上创建一个唯一性索引 缺省情况下 创建的索引类型为非聚簇索引 创建和修改唯一性约束的操作方法 创建和修改唯一性约束的操作方法有两种 一 企业管理器操作法 通过企业管理器可以完成创建和修改唯一性约束的操作 二 Transact SQL语句操作法 CONSTRAINTconstraint nameUNIQUE CLUSTERED NONCLUSTERED column name n 例 将数据库JWGL中teacher表的teacher id 教师号 字段设为唯一约束 USEJWGLGOALTERTABLEdepartmentADDCONSTRAINTunique department nameUNIQUEnonclustered department name 例 在XK数据库中为Student表上创建名为IX PWD的 惟一索引 该索引基于密码 PWD 列 以保证学生密码不重复 USEXkGOCREATEUNIQUEINDEXIX PwdONStudent Pwd GO 例 为系情况表 Department 中的系名列 DepartmentName 添加一个UNIQUE约束 ALTERTABLEDepartmentADDCONSTRAINTUNIQUE DepartmentNameUNIQUE DepartmentName 唯一约束和主键约束的区别 唯一约束与主键约束都为指定的列建立唯一索引 即不允许唯一索引的列上有相同的值 主键约束限制更严格 不但不允许有重复值 而且也不允许有空值 唯一约束与主键约束产生的索引可以是聚簇索引也可以是非聚簇索引 但在缺省情况下唯一约束产生非聚簇索引 主键约束产生聚簇索引 6 外部键约束若列或列的组合不是本表的关键字 而是另一个表的关键字 则称这些列或列的组合是外键 FOREIGNKEY 一般表与表之间通过主键和外键进行连接 通过它可以强制表与表之间的参照完整性 FOREIGNKEY约束要求列中的每个值在被引用表中对应的被引用列中都存在 如学生情况表 Student 表中的所在班级 ClassID 就是一个外键 它与班级情况表 Class 表中的主键班号 ClassID 进行关联 通过外键约束强制参照完整性 SQLServer提供了外键 主键值约束 即满足以下两点 存在外键时 被参照表中这一行不能删除 向子表插入记录或更新子表中外键值的前提是 必须保证这个外键值与主表中主键的某个值相等或者该外键值为空 否则不允许插入或修改外键值 创建FOREIGNKEY约束时应考虑以下因素 它提供一列或多列数据的参照完整性 FOREIGNKEY约束不自动创建索引 但如果考虑数据库的多表连接 建议为外键创建一个索引 以提高连接性能主键与外键的数据类型和长度必须一致 或系统可转换 例 为表teacher创建外键department id 外键department id参考表department中的主键department id USEJWGLGOALTERTABLEteacherADDCONSTRAINTFK department idFOREIGNKEY department id REFERENCESdepartment department id GO l行级FOREIGNKEY约束 其定义格式为 CONSTRAINTconstraint name FOREIGNKEY column n 引用表中的列名或列名的组合 REFERENCESref table ref column n 被引用表及其列名 ONDELETE CASCADE NOACTION 是否级联删除相关的记录 ONUPDATE CASCADE NOACTION 是否级联更新相关的记录 NOTFORREPLICATION 例 在XK数据库中为StuNO表创建名为StuNO FK的FOREIGNKEY约束 该约束限制StuNO列的数据只能是student表的StuNO列存在的数据USEXkGOALTERTABLEStuCouADDCONSTRAINTStuNo FKFOREIGNKEY StuNo REFERENCESStudent StuNo GO 例 删除刚建立的名为StuNO FK的FOREIGNKEY约束USEXkGOALTERTABLEStuCouDROPCONSTRAINTSTuNo FKGO 例 为Student表中的所在班级列 ClassID 添加一个FOREIGNKEY约束 ALTERTABLEStudentADDCONSTRAINTFK Student ClassFOREIGNKEY ClassID REFERENCESClass ClassID ONUPDATECASCADE 外部键约束用于强制参照完整性 提供单个字段或者多个字段的参照完整性 当使用外部键约束时 应该考虑以下几个因素 外部键约束提供了字段参照完整性 外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配 外部键约束不能自动创建索引 需要用户手动创建 用户想要修改外部键约束的数据 必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限 参考同一表中的字段时 必须只使用REFERENCES子句 不能使用外部键子句 一个表中最多可以有31个外部键约束 在临时表中 不能使用外部键约束 主键和外部键的数据类型必须严格匹配 3 使用企业管理器管理约束1 管理NOTNULL DEFAULT约束1 启动企业管理器 展开服务器组 再展开要操作的服务器 2 展开 数据库 文件夹 再展开要操作的数据库 Educational 3 单击展开的 表 项 在右边的 表 列表框中显示出所选数据库中的所有表 4 右击要在其中设置NOTNULL DEFAULT约束的数据表 Student 在弹出的快捷菜单中选择 设计表 菜单项 出现 设计表Student 对话框 5 中间列表中的一行为一列的定义 选中要设置NOTNULL约束的StudentName列 单击行尾 允许空 列 将其中的 去掉即可实现NOTNULL约束 再次单击就可删除NOTNULL约束 6 单击Sex列 在图下部的列属性表的 默认值 一栏所对应的输入框中输入Sex列的默认值 男 去掉默认值时 只需将 默认值 一栏所对应的输入框中的文字删除即可 7 设置完成后 单击工具栏上的 按钮 保存修改的结果 2 管理CHECK约束1 按上面的方法进入表设计器 2 单击工具栏上的 管理约束 按钮 出现 属性 对话框 在 选定的约束 列表框中存放有该表已创建的CHECK约束 3 单击 新建 按钮 新建一个CHECK约束 系统自动为新建的CHECK约束设置一个缺省名 并清空下面的 约束表达式 输入框 4 先在下面的 约束表达式 输入框中输入需创建CHECK约束的约束表达式 然后在 约束名 输入框中将缺省名修改为需创建的CHECK约束名 5 若选中 创建中检查现存数据 复选框 则表示当该约束存盘时 系统先检查表中已有的数据是否符合该CHECK约束的条件 如不符合 系统将出现 不能添加约束 错误并拒绝存盘 如符合 则创建CHECK约束成功 否则系统将不检查表中已有的数据 直接创建CHECK约束 6 单击 关闭 按钮 退出 属性 对话框 3 管理PRIMARYKEY约束1 主键约束的设置非常简单 按上面的方法进入所示的表设计器 2 若表中列的前面是一个空白的 方块 表明该列不是 主键