第05章 sql数据定义与完整性约束的实现
第5章 数据定义完整性约束的实现,SQL的表定义和完整性定义功能 SQL数据操作与完整性约束的作用,先建立数据库,回忆第2章建立数据库的命令,CREATE DATABASE 仓储订货 ON ( NAME = order_dat, FILENAME = 'c:mssqldataorderdat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = order_log, FILENAME = 'd:mssqllogorderlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ),5.1 SQL的表定义和 完整性定义功能,定义架构 定义表及其完整性约束 修改表结构的命令,3,什么是架构（Schema）?,定义架构,CREATE SCHEMA 仓储 CREATE SCHEMA 订货 CREATE SCHEMA 基础,5,定义表及其完整性约束,CREATE TABLE命令的基本格式 列的定义 计算列的定义 表级约束,6,CREATE TABLE命令的基本格式,一个表由若干列构成，在SQL Server 2005/2008中可以 定义实列(简称列，column_definition) 定义虚列(即计算列，computed_column_definition) 理解： ,CREATE TABLE schema_name.table_name ( | ,.n ),列的定义(),CREATE TABLE schema_name.table_name ( | ,.n ),column_name 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 constant_expression ,列名 数据类型 是否允许空值 主关键字约束 唯一性约束 参照完整性约束 域完整性约束 定义默认值,PRIMARY KEY和UNIQUE约束的区别？ 如何实现候选关键字约束？,9,测试,ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT ,计算列的定义,column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE ON UPDATE NO ACTION | CHECK ( logical_expression ) ,CREATE TABLE schema_name.table_name ( | ,.n ),为什么ON DELETE和ON UPDATE少了一些内容？,表级约束,如果某个完整性约束与多个列相关，则这样的完整性约束不能定义在单个列上，这时候就需要表级完整性约束。 单个列上的完整性约束也可以用表级完整性约束的形式来定义。 在定义表时，需要先定义被参照表，然后定义参照表。,仓储数据库,例5-1：定义仓库表,CREATE TABLE 仓储.仓库( 仓库号 CHAR(6) PRIMARY KEY, 城市 CHAR(10), 面积 INT CHECK (面积 0) ),仓库表属于仓储架构，包含仓库号、城市和面积等3个字段。其中仓库号是主关键字，仓库的面积值是大于0的整数。,例5-2：定义职工表,CREATE TABLE 基础.职工( 仓库号 CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 仓储.仓库(仓库号), 职工号 CHAR(8) PRIMARY KEY, 姓名 CHAR(10), 工资 numeric(8,2) CHECK (工资= 1000 AND 工资= 5000) DEFAULT 1200, 班组长 CHAR(8) FOREIGN KEY REFERENCES 基础.职工(职工号) ),14,职工表属于基础架构，包含仓库号、职工号、姓名、工资和班组长等5个字段。其中职工号是主关键字，仓库号是外部关键字（参照仓库表的仓库号字段值），职工的工资在1000到5000之间（默认是1200），班组长字段值说明当前职工的班组长（直接领导）是谁（和职工号字段的值域相同，参照本关系的职工号属性）。,例5-3：定义器件表,CREATE TABLE 基础.器件( 器件号 CHAR(6) PRIMARY KEY, 器件名称 CHAR(20) NOT NULL, 规格 CHAR(16) NOT NULL, 单价 SMALLMONEY , UNIQUE (器件名称,规格),15,在一个表中只能定义一个主关键字，其他具有候选关键字特征的字段应该定义非空值和唯一性约束。,器件表属于基础架构，包含器件号、器件名称、规格和单价等4个字段。其中器件号是主关键字，器件名称和规格的组合具有候选关键字的性质（要唯一、且不可为空值）。,例5-4：定义库存表,CREATE TABLE 仓储.库存( 仓库号 CHAR(6) FOREIGN KEY REFERENCES 仓储.仓库, 器件号 CHAR(6) FOREIGN KEY REFERENCES 基础.器件, 数量 INT CHECK (数量=0), PRIMARY KEY(仓库号,器件号),16,库存表属于仓储架构，包含仓库号、器件号和数量等3个字段。其中仓库号参照仓库关系的仓库号，器件号参照器件关系的器件号，主关键字由仓库号和器件号构成，库存数量应该大于等于0。,例5-5：定义供应商表,CREATE TABLE 订货.供应商( 供应商号 CHAR(5) PRIMARY KEY, 供应商名 CHAR(20), 地址 CHAR(20),17,供应商表属于订货架构，它包含供应商号、供应商名和地址等3个字段。其中供应商号是主关键字。,例5-6：定义订购单表。,CREATE TABLE 订货.订购单( 订购单号 CHAR(5) PRIMARY KEY, 经手人 CHAR(8) NOT NULL REFERENCES 基础.职工(职工号), 供货方 CHAR(5) NULL REFERENCES 订货.供应商(供应商号), 订购日期 DATETIME DEFAULT getdate(), 金额 MONEY NULL),18,订购单表属于订货架构，它包含订购单号、经手人、供货方、订购日期和金额等5个字段。其中订购单号是主关键字；经手人是负责该订购单的职工号，它参照职工关系的职工号字段，该字段不允许为空值（强制联系）；供货方是接受该订购单的供应商号，它参照供应商关系的供应商号字段，该字段可以为空值（非强制联系）；订购日期的默认值是系统的当前日期。,例5-7：定义订购明细表。,CREATE TABLE 订货.订购明细( 订购单号 CHAR(5) FOREIGN KEY REFERENCES 订货.订购单 ON DELETE CASCADE ON UPDATE CASCADE, 序号 SMALLINT CHECK (序号=1), 器件号 CHAR(6) FOREIGN KEY REFERENCES 基础.器件, 单价 SMALLMONEY, 数量 INT CONSTRAINT num CHECK (数量=0), PRIMARY KEY (订购单号,序号) ),19,订购明细表属于订货架构，它包含订购单号、序号、器件号、单价和数量等5个字段。其中主关键字由订购单号和序号共同构成；订购单号字段参照订购单关系的订购单号字段；器件号参照器件表的器件号字段；序号为大于等于1的整数；数量为大于等于0的整数。,修改表结构,ALTER TABLE schema_name.table_name ALTER COLUMN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name ,ALTER COLUMN修改已有列的定义，但是只能修改为兼容数据类型或重新定义是否允许空值； ADD新列、计算列或表级约束； DROP删除约束或删除列。,例5-8：删除职工表上定义的约束ref_wh。,ALTER TABLE 基础.职工 DROP CONSTRAINT ref_wh,21,如果在定义表和约束的时候没有用CONSTRAINT短语指定约束名称？,例5-9：添加完整性约束。,ALTER TABLE 基础.职工 ADD CONSTRAINT ref_wh FOREIGN KEY (仓库号) REFERENCES 仓储.仓库(仓库号) ON DELETE SET NULL ON UPDATE CASCADE,22,为职工表的仓库号字段添加约束，说明该字段是外部关键字，同时规定当被参照记录被删除时，将参照记录的仓库号字段值置为空值（即空值删除），当被参照记录的仓库号修改时级联修改参照记录的仓库号字段值（即级联更新）。,例5-10：增加和删除字段。,为订购明细表增加一个完成日期字段： ALTER TABLE 订货.订购明细 ADD 完成日期 datetime,23,删除刚为订购明细表增加的完成日期字段： ALTER TABLE 订货.订购明细 DROP COLUMN 完成日期,为订购单表增加一个完成日期字段，默认值是空值NULL： ALTER TABLE 订货.订购单 ADD 完成日期 datetime DEFAULT NULL,例5-11：增加计算字段。,ALTER TABLE 订货.订购明细 ADD 金额 AS 单价*数量,24,为订购明细表增加一个计算字段，字段名为金额，计算表达式为单价*数量：,5.2 SQL数据操作与完整性约束的作用,插入操作及其完整性约束 删除操作及其完整性约束 更新操作及其完整性约束,25,SQL的插入语句,常用格式： INSERT INTO schema_name.table_name ( column_list ) VALUES(expression | DEFAULT | NULL ,.n ),schema_name指出模式名 table_name指出表名 column_list给出插入操作所涉及列的列表（默认是表的全部列） expression | DEFAULT | NULL ,.n 给出对应于column_list的各个列的值,插入操作与完整性约束？,插入仓库元组,仓库号 CHAR(5) PRIMARY KEY 城市 CHAR(10) 面积 INT CHECK (面积
