第05章 实施数据完整性

SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 1 页 共 34 页 5 实施数据完整性实施数据完整性 本章主题 什么是数据完整性 数据完整性的实施形式 活用约束 CHECK 约束 PRIMARY KEY 约束 UNIQUE 约束 关系链和 FOREIGN KEY 约束 活用默认值 活用规则 完整性的检查次序 让我们努力掌握这一章的精彩内容吧 让我们努力掌握这一章的精彩内容吧 临渊羡鱼 不如退而结网临渊羡鱼 不如退而结网 5 1 什么是数据完整性什么是数据完整性 数据完整性 Data Integrity 代表数据的正确性 一致性与可靠性 实施数据完整 性的目的在于确保数据的质量 举 2 个例说明 身份证号不可重复 工资不能少于 1580 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 2 页 共 34 页 实实体体完完整整性性 实体完整性 要求表中每一条数据记录都是一个唯一的实体 也就是每一条数据 记录必须拥有一个唯一标识 说白了 说白了 要求所有数据记录至少必须有一个字段或几个字段的组合的内容绝对不要求所有数据记录至少必须有一个字段或几个字段的组合的内容绝对不 能发生重复的情况 能发生重复的情况 域域完完整整性性 域完整性 要求存入字段中的数据值必须符合特定的条件 以及决定字段是否允 许接受 NULL 引引用用完完整整性性 引用完整性 指当添加 修改或删除数据记录时 表间的关联性不可破坏 用用户户定定义义完完整整性性 泛指其他所有不属于前 3 种完整性的业务规则 该种业务规则无法利用前该种业务规则无法利用前 3 种完整性来完成种完整性来完成 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 3 页 共 34 页 表格 5 1 数据完整性类型 数据完整性的类型数据完整性的类型实施此数据完整性的可用方法实施此数据完整性的可用方法 实体完整性实体完整性 Entity Integrity 索引 PRIMARY KEY 约束 UNIQUE 约束 IDENTITY 属性 域完整性 Domain Integrity DEFAULT 定义 FOREIGH KEY 约束 CHECK 约束 NOT NULL 定义 规则 引用完整性 Referential Integrity FOREIGH KEY 约束 CHECK 约束 存储过程 触发器 用户定义完整性 User Defined Integrity CREATE TABLE 命令语句中所有字段与表级的 约束 存储过程 触发器 5 2 数据完整性的实施形式数据完整性的实施形式 两种实施形式 声明式数据完整性声明式数据完整性 是将数据所需符合的条件融入到对象定义中 特点 1 通过直接针对表和字段定义声明的约束 可使声明式数据完整性成 为数据库定义的一部分 特点 2 可使用约束 默认值与规则实施声明式数据完整性 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 4 页 共 34 页 程序化数据完整性程序化数据完整性 通过编写的程序代码来完成 建议建议较复杂的业务逻辑采用程序化数据完整性 程序化数据完整性可通过相关的程序语言及工具通过相关的程序语言及工具在客户端或服务器端实施 SQL Server 可使用存储过程和触发器可使用存储过程和触发器实施程序化数据完整性 5 3 活用约束活用约束 约束 Constraints 最主要的功能是确保存入到字段中的数据符合我们的 要求 并确实维护表间的关联性连接 约束以字段为处理对象约束以字段为处理对象 如果约束的处理对象是某单一字段 称为 列约束列约束 如果约束的处理对象涉及多个字段 称为 表约束表约束 若要更改表现有的约束定义 只能使用若要更改表现有的约束定义 只能使用 企业管理器企业管理器 实现实现 数据库中各个约束的名称绝对不能相同 约束种类 约束种类 CHECK PRIMARY KEY UNIQUE FOREIGN KEY 5 4 CHECK 约束约束 CHECK 约束 只有符合特定条件和格式的数据才能存入字段中 约束 只有符合特定条件和格式的数据才能存入字段中 可以可以同时在一个字段应用多个 CHECK 约束 当执行 INSERT 或或 UPDATE 命令时 CHECK 约束便会进行检验 自动编号字段 自动编号字段 timestamp uniqueidentifier 数据类型字段数据类型字段不能应用 CHECK 约 束 选中或不选中复选框 创建中检查现存数据创建中检查现存数据 表示 SQL Server 在将表的结构保存之前 会先检查表中所有的数据记录是否符合您 指定的 CHECK 约束 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 5 页 共 34 页 选中或不选中复选框 对对 INSERT 和和 UPDATE 强制约束强制约束 表示当执行 INSERT 或 UPDATE 命令时 便会应用 CHECK 约束 以便检查添加或 修改后字段中的数据是否符合指定的条件 选中或不选中复选框 对复制强制约束对复制强制约束 当将表复制到其他的数据库 表的结构定义和数据记录会从原先所在的数据库复制 到目标数据库 此时如果不禁用在此表中的 CHECK 约束 很可能导致数据无法写入目标 数据库 新新建建表表时时创创建建CHECK 约约束束 使用企业管理器 注意 在操作过程中 并不要指定是并不要指定是 列级约束列级约束 还是还是 表级约束表级约束 是根据该约束 逻辑表达式自动确定 任务任务 新建 MyTable 表 将 age 字段设置在 1 150 之间 将 gender 字段取值设定为 男 或 者 女 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 6 页 共 34 页 使用 CREATE TABLE 命令 未指定约束名称 将由 SQL Server 自动指派 CREATE TABLE DemoTable2 MemberNo smallint NOT NULL CHECK MemberNo BETWEEN 1 AND 10000 name varchar 20 NOT NULL address varchar 60 NOT NULL 指定约束名称 CREATE TABLE DemoTable1 MemberNo smallint NOT NULL CONSTRAINT MemberNoChk CHECK MemberNo BETWEEN 1 AND 10000 name varchar 20 NOT NULL address varchar 60 NOT NULL 脚本文件名 Demo63 sql CREATE TABLE DemoTable3 MemberNo smallint NOT NULL name varchar 20 NOT NULL address varchar 60 NOT NULL CONSTRAINT ChkMemberNo 表级约束 CHECK MemberNo BETWEEN 1 AND 10000 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 7 页 共 34 页 未指定约束名称 将由 SQL Server 自动指派 CREATE TABLE DemoTable4 MemberNo smallint NOT NULL name varchar 20 NOT NULL address varchar 60 NOT NULL CHECK MemberNo BETWEEN 1 AND 10000 CREATE TABLE DemoTable5 Id char 10 NOT NULL CONSTRAINT IdChk CHECK Id LIKE A Z 1 2 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 JobId char 6 NOT NULL CONSTRAINT JobIdChk CHECK JobId LIKE T1C 0 9 0 9 0 9 Name varchar 20 NOT NULL Sex char 1 NOT NULL CONSTRAINT SexChk CHECK sex IN M F Birthday datetime NOT NULL CONSTRAINT AgeChk CHECK DATEDIFF year Birthday getdate 18 Phone char 13 NOT NULL CONSTRAINT PhoneChk SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 8 页 共 34 页 CHECK Phone LIKE 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 OR Phone LIKE 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 OR Phone LIKE 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 Address varchar 60 NOT NULL BloodType varchar 3 NOT NULL CONSTRAINT BloodTypeChk CHECK BloodType IN A B AB O RH RH Hire Salary money NOT NULL CONSTRAINT Hire SalaryChk CHECK Hire Salary 15000 AND Hire Salary Hire Salary SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 9 页 共 34 页 更更改改现现存存CHECK 约约束束的的定定义义 更改使用 企业管理器 来实施 修改修改 CHECK 约束有以下 约束有以下 CHECK 约束的逻辑表达式 CHECK 约束的名称 选中或不选中复选框 创建中检查现存数据 选中或不选中复选框 对 INSERT 和 UPDATE 强制约束 选中或不选中复选框 对复制强制约束 程序方式只能更改 是否要在执行 INSERT 或 UPDATE 命令时强制约束 特别提醒 如果一定要用程序方式用程序方式来更改 CHECK 约束 只能只能先删除约束 再新建 脚本文件名 Demo66 sql 将表 DemoTable5 中名称为 PhoneChk 的 CHECK 约束设置成不检查不检查添加或修改后的数据 ALTER TABLE DemoTable5 NOCHECK CONSTRAINT PhoneChk 脚本文件名 Demo67 sql 设置 CHECK 约束 PhoneChk 会检查会检查或修改后的数 据 ALTER TABLE DemoTable5 CHECK CONSTRAINT PhoneChk 脚本文件名 Demo68 sql 将表 DemoTable5 中的 3 个 CHECK 约束设置成不不 检查检查添加或修改后的数据 ALTER TABLE DemoTable5 NOCHECK CONSTRAINT SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 10 页 共 34 页 Hire SalaryChk Cur SalaryChk SalaryChk 脚本文件名 Demo69 sql 将表 DemoTable5 中所有 CHECK 约束设置成会检会检 查查或修改后的数据 ALTER TABLE DemoTable5 CHECK CONSTRAINT ALL 为为表表添添加加CHECK 约约束束 使用企业管理器 任务任务 将 choice 表的 grade 字段设置在 0 100 之间 将 teacher 表的 tsex 字段取值设定为 男 或者 女 将 teacher 表的 ttitle 字段取值设定为 助教 讲师 副教授 或者 教授 使用 ALTER TABLE 命令 脚本文件名 Demo610 sql 为表新建一 department 字段的同时同时为其它指定 CHECK 约束 ALTER TABLE DemoTable5 ADD Department varchar 16 NULL CONSTRAINT DepartmentChk CHECK Department IN 资讯部 人事部 会计部 业务部 如果加入 WITH NOCHECK 则不会用新加入的 CHECK 约束检查现有数据检查现有数据是否符合 指定条件 脚本文件名 Demo611 sql 为表添加一个添加一个表级的 CHECK 约束 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 11 页 共 34 页 ALTER TABLE DemoTable5 WITH CHECK ADD CONSTRAINT AvgChk 表级约束 CHECK Cur Salary Hire Salary 2 20000 特别提醒特别提醒 表级的表级的 CHECK 约束不一定要涉及多个字段约束不一定要涉及多个字段 它也可用来确保某单一字 段的内容符合指定条件 示例 下面的操作中 创建一个表并为它加入字段和示例 下面的操作中 创建一个表并为它加入字段和 CHECKCHECK 约束 约束 脚本文件名 Demo612 sql CREATE TABLE DemoTable6 Id char 10 NOT NULL CONSTRAINT ChkId CHECK Id LIKE A Z 1 2 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 Name varchar 20 NOT NULL 脚本文件名 Demo613 sql 为表添加数个字段 其中两个字段拥有 CHECK 约 束 ALTER TABLE DemoTable6 ADD Sex char 1 NOT NULL DEFAULT F CONSTRAINT ChkSex CHECK sex IN M F Birthday datetime NOT NULL DEFAULT 1981 1 1 CONSTRAINT ChkAge CHECK DATEDIFF year Birthday getdate 18 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 12 页 共 34 页 Phone char 13 NULL Address varchar 60 NULL BloodType varchar 3 NULL DEFAULT B Hire Salary money NOT NULL DEFAULT 15000 Cur Salary money NOT NULL DEFAULT 15000 脚本文件名 Demo614 sql 为表添加数个 CHECK 约束 ALTER TABLE DemoTable6 WITH CHECK ADD CONSTRAINT ChkBloodType CHECK BloodType IN A B AB O RH RH CONSTRAINT ChkHire Salary CHECK Hire Salary 15000 AND Hire Salary Hire Salary 删删除除 CHECK 约约束束 使用企业管理器 使用 ALTER TABLE 命令 脚本文件名 Demo615 sql ALTER TABLE DemoTable5 DROP CONSTRAINT AgeChk 脚本文件名 Demo616 sql 一次可以删除多个 CHECK 约束 ALTER TABLE DemoTable5 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 13 页 共 34 页 DROP CONSTRAINT BloodTypeChk PhoneChk SexChk 5 5 PRIMARY KEY 约束约束 PRIMARY KEY 约束可以将某单一字段或多个字段的组合定义为主键 并保证值绝 对是唯一且不可以重复 一旦成功地将单一字段或多个字段的组合定义为主键之后一旦成功地将单一字段或多个字段的组合定义为主键之后 当您添加或修改数据时 若发生在主键的源字段添加或修改后的值与其他现存的数据发生重复的情形 SQL Server 将会通知这项操作违反唯一性将会通知这项操作违反唯一性 从而取消从而取消这项添加或修改操作 一个名词一个名词 被定义为主键的单一字段或多个字段称之为主键的源字段称之为主键的源字段 注意 每一个表最多只能拥有一个最多只能拥有一个 PRIMARY KEY 约束 主键的源字段不允许接受不允许接受 NULL 值值 事实上 建立主键后建立主键后 SQL Server 会自动创建一个唯一索引自动创建一个唯一索引 新新建建表表时时创创建建PRIMARY KEY 约约束束 使用企业管理器 任务任务 将 student 表的 sno 字段设置为主键 将 teacher 表的 tno 字段设置为主键 将 choice 表的 sno cno 设置为主键 主键为复合字段 使用 CREATE TABLE 命令 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 14 页 共 34 页 脚本文件名 Demo617 sql 约束的名称由 SQL Server 自动指定 CREATE TABLE DemoTable7 Id char 10 PRIMARY KEY Name varchar 16 脚本文件名 Demo618 sql CREATE TABLE DemoTable8 Id char 10 CONSTRAINT PK Id PRIMARY KEY 指定了约束名称为 PK Id Name varchar 16 脚本文件名 Demo619 sql CREATE TABLE DemoTable9 MemberNo int IDENTITY 5 5 NOT NULL CONSTRAINT PK MemberNO PRIMARY KEY 将自动编号字段定义为主键 Name varchar 16 NOT NULL 脚本文件名 Demo620 sql CREATE TABLE DemoTable10 Au id varchar 11 NOT NULL Title id varchar 6 NOT NULL SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 15 页 共 34 页 Au ord tinyint NULL Royaltyper int NULL CONSTRAINT PK Au Id Title Id 指定两个字段的组合为主键 PRIMARY KEY Au id Title id 脚本文件名 Demo621 sql 将字段 JobNo 定义为主键并为之定义一个 CHECK 约束 CREATE TABLE DemoTable11 JobNo smallint NOT NULL CONSTRAINT PK JobNo PRIMARY KEY 约束 PRIMARY KEY CONSTRAINT JobNo Chk CHECK 约束 CHECK JobNo BETWEEN 1 AND 10000 name varchar 20 NOT NULL address varchar 60 NOT NULL 更更改改现现存存PRIMARY KEY 约约束束的的定定义义 使用企业管理器 特别提醒特别提醒 如果要以程序控制方式更改现存 PRIMARY KEY 约束 必须先删除后创必须先删除后创 建建 为为现现存存表表创创建建PRIMARY KEY 约约束束 使用企业管理器 使用 ALTER TABLE 命令 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 16 页 共 34 页 脚本文件名 Demo624 sql ALTER TABLE DemoTable ADD Id int IDENTITY 1 1 NOT NULL CONSTRAINT PK Id PRIMARY KEY 增加字段的同时 创建 PRIMARY KEY Name varchar 20 NULL 脚本文件名 Demo625 sql 为表创建一个主键 ALTER TABLE Employee ADD CONSTRAINT PK Emp Id PRIMARY KEY Emp Id 脚本文件名 Demo626 sql ALTER TABLE titleauthor ADD CONSTRAINT PK au id title id PRIMARY KEY au id title id 组合字段为 PRIMARY KEY 删删除除 PRIMARY KEY 约约束束 使用企业管理器 使用 ALTER TABLE 命令 脚本文件名 Demo627 sql ALTER TABLE titleauthor DROP CONSTRAINT PK au id title id SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 17 页 共 34 页 5 6 UNIQUE 约束约束 UNIQUE 约束与 PRIMARY KEY 约束几乎完全一样 差别主要是 一个表最多只能拥有一个一个表最多只能拥有一个 PRIMARY KEY 而可拥有多个多个 UNIQUE 约束 被 PRIMARY KEY 约束定义为主键的一或多个字段是不允许接受 NULL 值 应用 UNIQUE 约束的一个或多个字段则允许接受允许接受 NULL 值 但是如果在两行中值 但是如果在两行中 出现组合后的结果为出现组合后的结果为 NULL 的情况 则视为重复的情况 则视为重复 一旦创建 UNIQUE 约束 则 SQL Server 后自动创建一个唯一索引 另外 另外 UNIQUE 约束还能被一个约束还能被一个 FOREIGN KEY 约束引用 约束引用 这部分在讲到这部分在讲到 FOREIGN KEY 约束会讲到约束会讲到 新新建建表表时时创创建建UNIQUE 约约束束 使用企业管理器 任务任务 为 classs 表的 classname 字段创建唯一约束 为 professional 表的 pname 字段创建 唯一约束 为 department 表的 deptname 字段创建唯一约束 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 18 页 共 34 页 使用 CREATE TABLE 命令 方法与方法与 PRIMARY KEY 约束极其相似约束极其相似 脚本文件名 Demo628 sql 约束的名称由 SQL Server 自动指定 CREATE TABLE DemoTable14 Id char 10 NOT NULL PRIMARY KEY JobId char 6 NOT NULL UNIQUE Name varchar 16 脚本文件名 Demo629 sql CREATE TABLE DemoTable15 Id char 10 NOT NULL CONSTRAINT PK Id DemoTable15 PRIMARY KEY 指定了约束名称 JobId char 6 NOT NULL CONSTRAINT UQ JobId DemoTable15 UNIQUE 指定了约束名称 Name varchar 16 脚本文件名 Demo630 sql CREATE TABLE DemoTable16 Id int IDENTITY 10 10 NOT NULL CONSTRAINT PK Id DemoTable16 PRIMARY KEY CLUSTERED PRIMARY KEY 约束 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 19 页 共 34 页 JobId char 6 NOT NULL CONSTRAINT UQ JobId DemoTable16 UNIQUE UNIQUE 约束 LicenseNo char 6 NOT NULL CONSTRAINT UQ LicenseNo DemoTable16 UNIQUE UNIQUE 约束 Name varchar 16 NULL Phone char 11 NULL CellularPhone int NULL CONSTRAINT UQ Name Phone DemoTable16 UNIQUE Name Phone UNIQUE 约束 组合字段 CONSTRAINT UQ Name CellularPhone DemoTable16 UNIQUE Name CellularPhone UNIQUE 约束 组合字段 更更改改现现存存UNIQUE 约约束束的的定定义义 使用企业管理器 特别提醒特别提醒 如果要以程序控制方式更改现存 UNIQUE 约束 必须先删除后创建必须先删除后创建 为为现现存存表表创创建建UNIQUE 约约束束 使用企业管理器 使用 ALTER TABLE 命令 脚本文件名 Demo634 sql ALTER TABLE DemoTable20 ADD MemberNo int IDENTITY 1 1 NOT NULL CONSTRAINT UQ MemberNo DemoTable20 UNIQUE SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 20 页 共 34 页 Name varchar 20 NULL 脚本文件名 Demo635 sql ALTER TABLE DemoTable21 ADD Id uniqueidentifier NOT NULL DEFAULT NEWID CONSTRAINT UQ Id DemoTable21 UNIQUE Name varchar 20 NULL Birthday datetime NULL CONSTRAINT UQ Name Birthday DemoTable21 UNIQUE Name Birthday 脚本文件名 Demo636 sql ALTER TABLE DemoTable22 ADD CONSTRAINT UQ Id DemoTable22 UNIQUE Id 脚本文件名 Demo637 sql ALTER TABLE DemoTable23 ADD CONSTRAINT UQ au id title id DemoTable23 UNIQUE au id title id 脚本文件名 Demo638 sql CREATE TABLE DemoTable24 Id char 10 NOT NULL SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 21 页 共 34 页 CONSTRAINT PK Id DemoTable24 PRIMARY KEY CLUSTERED LicenseNo char 6 NOT NULL Address varchar 60 NULL ALTER TABLE DemoTable24 ADD MemberNo int IDENTITY 10 10 NOT NULL CONSTRAINT UQ MemberNo DemoTable24 UNIQUE Name varchar 20 NULL Birthday datetime NULL CONSTRAINT UQ Name Birthday DemoTable24 UNIQUE Name Birthday CONSTRAINT UQ LicenseNo DemoTable24 UNIQUE LicenseNo 删删除除 UNIQUE 约约束束 使用企业管理器 使用 ALTER TABLE 命令 特别提醒特别提醒 SQL Server 不允许使用企业管理器或 DROP INDEX 命令直接删除 UNIQUE 约束的唯一索引 事实上不需要这样做 因为当将当将 UNIQUE 约束删除时 约束删除时 UNIQUE 约束的唯一索引会自动一并删除约束的唯一索引会自动一并删除 脚本文件名 Demo639 sql ALTER TABLE DemoTable24 DROP CONSTRAINT UQ LicenseNo DemoTable24 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 22 页 共 34 页 5 7 关系键与关系键与 FOREIGN KEY 约束约束 SQL Server 是一个关系型数据库管理系统 也就是说它能够利用表间的关系来管理 数据 链接表的实质做法 链接表的实质做法 基于父表的共同字段的主键 Primary key 和子表共同字段 具有 共同含义 的外键 Foreign key 来创建关系 其中父表的主键使用 PRIMARY KEY 约 束定义 子表的外键由 FOREIGN KEY 约束定义 FOREIGN KEY 约束最主要的用途 约束最主要的用途 将表的某单一字段或多个字段的组合定义为外键 将表的某单一字段或多个字段的组合定义为外键 并决定该外键要链接到哪一个表的主键并决定该外键要链接到哪一个表的主键 以及维护关联表间的引用完整性 特别提醒特别提醒 FOREIGN KEY 约束不一定要将外键链接到其他表的主键 也可链接到其不一定要将外键链接到其他表的主键 也可链接到其 他表的他表的 UNIQUE 约束所应用的字段约束所应用的字段 这意味着 父表除了其主键与子表的外键创建关系 之外 还可通过其 UNIQUE 约束的字段与子表的外键创建关系 注意 注意 外键的源字段内容可以是重复的可以是重复的 而且允许接受 NULL 值 一个表可以拥有多个可以拥有多个 FOREIGN KEY 约束 一个表最多可拥有 253 个 FOREIGN KEY 约束 FOREIGN KEY 约束所指定的字段数目和数据类型 必须必须与其链接到的 PRIMARY KEY 或 UNIQUE 约束所指定的字段数目和数据类型完全相同完全相同 FOREIGN KEY 约束并不会自动创建索引 约束并不会自动创建索引 最好自行创建索引以提高查询效率最好自行创建索引以提高查询效率 在索引一章要谈到在索引一章要谈到 创创建建 FOREIGN KEY 约约束束 必须有一个认识 所谓创建 FOREIGN KEY 约束 其实就是在创建表间的关系 使用企业管理器企业管理器创建 FOREIGN KEY 约束 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 23 页 共 34 页 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 24 页 共 34 页 任务任务 创建 class 表和 student 表之间的关系 并建立数据库关系图显示两者关系 创 建 professional 表和 class 表之间的关系 并建立数据库关系图显示两者关系 创建表 department 和 professional 表之间的关系 并建立数据库关系图显示两者关系 也可以通过数据库关系图创建 FOREIGN KEY 约束 以程序方式创建 FOREIGN KEY 约束 在 CREATE TABLE 或 ALTER TABLE 命令中加入下面一段参数 CONSTRAINT 约束名称 FOREIGN KEY REFERENCES 父表名称 父表主键或 UNIQUE 约束的字段名列表 脚本文件名 Demo641 sql CREATE TABLE Customers CustomerID nchar 5 NOT NULL PRIMARY KEY CompanyName nvarchar 40 NOT NULL ContactName nvarchar 30 NULL SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 25 页 共 34 页 CREATE TABLE Orders OrderID int IDENTITY 1 1 NOT NULL CustomerID nchar 5 NULL CONSTRAINT FK Orders Customers FOREIGN KEY REFERENCES Customers CustomerID OrderDate datetime NULL 脚本文件名 Demo642 sql CREATE TABLE Customers CustomerID nchar 5 NOT NULL PRIMARY KEY CompanyName nvarchar 40 NOT NULL ContactName nvarchar 30 NULL CREATE TABLE Orders OrderID int IDENTITY 1 1 NOT NULL CustomerID nchar 5 NULL OrderDate datetime NULL ALTER TABLE Orders ADD SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 26 页 共 34 页 CONSTRAINT FK Orders Customers FOREIGN KEY CustomerID REFERENCES Customers CustomerID 脚本文件名 Demo643 sql ALTER TABLE Orders ADD CONSTRAINT FK Orders Customers FOREIGN KEY CustomerID REFERENCES Customers CustomerID CONSTRAINT FK Orders Employees FOREIGN KEY EmployeeID REFERENCES Employees EmployeeID CONSTRAINT FK Orders Shippers FOREIGN KEY ShipVia REFERENCES Shippers ShipperID 脚本文件名 Demo644 sql ALTER TABLE ChildTable ADD CONSTRAINT FK ChildTable Sales FOREIGN KEY stor id ord num title id REFERENCES Sales stor id ord num title id 更更改改 FOREIGN KEY 约约束束的的定定义义 使用数据库关系图修改 FOREIGN KEY 约束 编辑数据库关系图 右键点击 关系链接的关系线 查看 属性 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 27 页 共 34 页 特别提醒特别提醒 无法以程序控制方式编辑关系 除非先将原有的 FOREIGN KEY 约束删 除 再新建一个符合目前所需的 FOREIGN KEY 约束 删删除除 FOREIGN KEY 约约束束 使用数据库关系图删除 FOREIGN KEY 约束 从关系图中 选中 关系链接的关系线 从右键快捷菜单中选择 从数据库中删除关系 下面的范例先创建各个彼此相关联的表 然后再使用下面的范例先创建各个彼此相关联的表 然后再使用 ALTER TABLE 命令删除它们之间命令删除它们之间 的关系 的关系 让我们好好欣赏一下这段精彩的历程吧 让我们好好欣赏一下这段精彩的历程吧 脚本文件名 Demo645 sql 创建表 CREATE TABLE authors au id char 6 NOT NULL PRIMARY KEY au name varchar 20 NOT NULL CREATE TABLE titles title id char 6 NOT NULL PRIMARY KEY title varchar 80 NOT NULL pub id char 4 NOT NULL CREATE TABLE publishers pub id char 4 NOT NULL PRIMARY KEY pub name varchar 40 NULL SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 28 页 共 34 页 CREATE TABLE titleauthor au id char 6 NOT NULL title id char 6 NOT NULL au ord tinyint NULL royaltyper int NULL PRIMARY KEY au id title id 脚本文件名 Demo646 sql 将表 publishers 与 title 链接起来 ALTER TABLE titles ADD CONSTRAINT FK titles publishers FOREIGN KEY pub id REFERENCES publishers pub id 脚本文件名 Demo647 sql 将 titleauthor 与 authors title 链接起来 ALTER TABLE titleauthor ADD CONSTRAINT FK titleauthor authors FOREIGN KEY au id REFERENCES authors au id CONSTRAINT FK titleauthor titles FOREIGN KEY title id REFERENCES titles title id SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 29 页 共 34 页 脚本文件名 Demo648 sql 删除 表 publishers 与 title 的链接 ALTER TABLE titles DROP CONSTRAINT FK titles publishers 脚本文件名 Demo649 sql 删除 titleautho 与 rauthors title 的链接 起来 ALTER TABLE titleauthor DROP CONSTRAINT FK titleauthor authors FK titleauthor titles 5 8 活用默认值活用默认值 让我们看一看这个顺序 让我们看一看这个顺序 如果如果您为此字段定义默认值 Default 默认值将成为此字段的内容 如果如果您并未给此字段定义默认值 而且此字段允许接受 NULL 值 则 NULL 值将成 为该字段的内容 如果如果您并未给此字段定义默认值 而且此字段不允许接受 NULL 值 将会出现错误 一个例子 一个例子 地址字段 如果目前输入时无法确定该输入什么内容 与其让用户随便输入一个数据值 不如由系统以默认值的方式给字段指派一个数据 如不如由系统以默认值的方式给字段指派一个数据 如 目前未知目前未知 SQL Server 提供了两种方式给字段定义默认值 DEFAULT 定义 DEFAULT 对象 但不能犯下面两个错误 但不能犯下面两个错误 默认值的数据类型必须必须与字段的数据类型相同 SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 30 页 共 34 页 字段的默认值不可不可与 CHECK 约束或规则相违背 DEFAULT 定定义义 即默认值可以成为表结构定义的一部分 在定义表结构或修改表结构的同时 一并定义 字段的默认值 企业管理器 任务任务 设置 student 表 ssex 字段的默认值为男 将 teacher 表的 tsex 字段的默认值设定为 女 SQL 语句 脚本文件名 Demo650 sql CREATE TABLE DemoTable25 Id uniqueidentifier NOT NULL DEFAULT newid PRIMARY KEY Name varchar 16 NOT NULL Phone varchar 11 NOT NULL Birthday datetime NOT NULL 脚本文件名 Demo651 sql ALTER TABLE DemoTable25 ADD Address varchar 60 NOT NULL DEFAULT 台湾省台北市台湾省台北市 Income money NOT NULL DEFAULT 10000 DEFAULT 姓名未知姓名未知 FOR Name 为原有的 3 个字段定义默认值 DEFAULT getdate FOR Birthday DEFAULT 电话未知电话未知 FOR Phone SQL Server 数据库应用与开发 第 5 稿 5 实施数据完整性 沙洲职业工学院电子信息工程系 第 31 页 共 34 页 DEFAULT 对对象象 DEFAULT 对象是一个独立存在独立存在的数据库对象 最大的好处 只需创建一次 就可以被同一数据库中所有表的不同字段共享 一点建议一点建议 后续版本的 Microsoft SQL Server 将删除该功能 请避免在新的开发工作 中使用该功能 并着手修改当前还在使用该功能的应用程序 而应使用通过 ALTER TABLE 或 CREATE TABLE 的 DEFAULT 关键字创建的默认定义 创建默认的命令如下 CREATE DEFAULT schema name default name AS constant expression 其中 default nam