数据库默认值约束、默认值对象、检查约束、规则、完整性实现.ppt

AnQing Teachers College Department of Computer & Information 数据库原理与应用 Principle and Application of Database system 安庆师范学院计算机与信息学院 Principle and Application of Database System 13.1 在表中定义及删除默认值约束 1. 默认值约束的定义 1) 定义表结构时定义字段的默认值约 束 语法格式： CREATE TABLE table_name /*指定表名*/ (column_name datatype NOT NULL | NULL DEFAULT constraint_expression /*默认值约束表达式*/ ,n ) /*定义列名、该列的数据类型、是否空值及默认值约束*/ Principle and Application of Database System n例1 对于student数据库，定义studentx表时 定义ssex字段的默认值约束为男。 CREATE TABLE studentx (sno char(5) PRIMARY KEY, sname char(8), ssex char(2) DEFAULT 男, sbirthday datetime, class char(5) ) Principle and Application of Database System 2）修改表添加一个字段的同时定义相应的约束 语法格式： ALTER TABLE table_name /*指定表名*/ ADD column_name datatype NOT NULL | NULL CONSTRAINT constraint_name /*指定约束名*/ DEFAULT constraint_expression WITH VALUES/*默认 值约束表达式*/ Principle and Application of Database System WITH VALUES： n仅用在对表添加新字段的情况下，若使 用了WITH VALUES，则将为表中各现 有行添加的新字段提供默认值；如果没 有使用WITH VALUES，那么每一行的 新列中都将为NULL值。 Principle and Application of Database System n例2 在修改表时添加一个字段，并定义 默认值约束。 ALTER TABLE student ADD nation char(16) NULL DEFAULT 中 国 WITH VALUES Principle and Application of Database System 3）对表中指定的列定义默认值 语法格式： ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT constraint_expression FOR column Principle and Application of Database System ALTER TABLE student ADD DEFAULT 男 FOR ssex Principle and Application of Database System 2. 默认值约束的删除 默认值约束可在SSMS中删除。如果已知一个默认值约束的约 束名，也可在查询分析器中执行SQL命令删除 ALTER TABLE student DROP CONSTRAINT def_ssex Principle and Application of Database System 13.2 默认值对象的定义、使用与删除 1.通过SQL语句定义和绑定DEFAULT默认值对象 (1) 通过SQL命令定义DEFAULT默认值对象 定义DEFAULT默认值对象的命令如下。 语法格式： CREATE DEFAULT default_name AS constant_expression (2) 通过系统存储过程绑定DEFAULT默认值对象 创建默认值对象后，要使其起作用，应使用 sp_bindefault存储过程将 其绑定到列或用户定义数据类型。 语法格式： EXEC sp_bindefault default_name table_name.column_name. | user_datatype Principle and Application of Database System 例 在student数据库中定义def_ssex的默认值对 象，然后将其绑定到student表的ssex字段。 USE student GO CREATE DEFAULT def_ssex AS 男 GO EXEC sp_bindefault def_ssex, student.ssex Principle and Application of Database System n例 在student数据库中定义名为birthday_date的数据类 型,然后定义默认值对象birthday并将其绑定到用户定义 的数据类型birthday_date中。 USE student GO EXEC sp_addtype birthday_date,datetime,NULL GO CREATE DEFAULT birthday AS 1979-2-10 GO EXEC sp_bindefault birthday, birthday_date Principle and Application of Database System 13.2 默认值对象的定义、使用与删除 2. 默认值对象的删除 如果要删除一个默认值对象，首先应解除默认值对象与用户定义 类型及表字段的绑定关系，然后才能删除该默认值对象。 (1) 利用sp_unbindefault解除绑定关系 语法格式： sp_unbindefault objname = object_name (2) 删除默认值对象 解除默认值对象与用户定义类型及表字段的绑定关系后，即可 用DROP语句删除默认值对象。 语法格式： DROP DEFAULT default ,.n Principle and Application of Database System n例 解除默认值birthday与student数据 库中用户定义数据类型birthday_date的 绑定关系，然后删除名为birthday的默 认值对象。 USE student GO EXEC sp_unbindefault birthday_date GO DROP DEFAULT birthday Principle and Application of Database System 13.3 数据完整性的分类 域完整性又称为列完整性，指列数据输入的有效性。 1. 域完整性 USE student CREATE TABLE score1 ( sno char(5) NOT NULL, cno char(10) NOT NULL, degree numeric(4,1) NULL CHECK (degree =0 AND degree=0 AND degree=0 AND degree100 GO EXEC sp_bindrule chk_score, score.degree GO Principle and Application of Database System n例：定义一个用户数据类型telphone，及规则tel_rule ，然后将规则tel_rule绑定到用户数据类型telphone上 ，最后在表student一添加一telephone_no字段，其 数据类型为telphone. USE student GO EXEC sp_addtype telephone, char(12),null GO CREATE RULE tel_rule AS tel like0-90-90-90- 90-90-90-90-9 GO EXEC sp_bindrule tel_rule,telephone GO Principle and Application of Database System ALTER TABLE student ADD telephone_no telephone Principle and Application of Database System 6.3.2 域完整性的实现 (4) 规则对象的删除 删除规则对象前，首先应使用系统存储过程 sp_unbindrule解除被绑定对象与规则对象之间的绑定关 系。 语法格式： EXEC sp_unbindrule objname = object_name Principle and Application of Database System n例 解除tel_rule规则对象与用户定义类 型telephone的绑定关系，并删除规则 对象tel_rule. USE student GO EXEC sp_unbindrule telephone GO DROP RULE tel_rule Principle and Application of Database System 13.3.2 实体完整性的实现 1. 利用SSMS创建和删除PRIMARY KEY约束 1) 利用SSMS创建PRIMARY KEY约束 如果要对student表按sno字段建立 PRIMARY KEY约束，按如下步骤进行： (1) 选择student表图标，右击，出现快捷菜 单，选择菜单项“设计表” 。 (2) 在表设计器界面选中“sno”字段对应的这 一行，选择主键图标，这样在“sno”对应的这 一行前面，将出现一主键图标。 2) 利用SSMS删除PRIMARY KEY约束 如下步骤进行： (1) 进入student表的表设计器界面； (2) 选中student表设计器中主键对应的行，点 击工具栏的主键图标，则取消了原来定义的 主键。 1.选中此行 2.点击主键图标 Principle and Application of Database System 6.3.3 实体完整性的实现 2. 利用SSMS创建和删除UNIQUE约束 1) 利用SSMS创建UNIQUE约束2) 利用SSMS删除UNIQUE约束 Principle and Application of Database System 6.3.3 实体完整性的实现 3利用SQL命令创建及删除PRIMARY KEY约束或 UNIQUE约束 1) 创建表的同时创建PRIMARY KEY约束或UNIQUE约束 语法格式： CREATE TABLE table_name /*指定表名*/ ( column_name datatype /*定义字段*/ CONSTRAINT constraint_name /*约束名*/ NOT NULL PRIMARY KEY UNIQUE /*定义约束类型*/ CLUSTERED | NONCLUSTERED /*定义约束的索引类型*/ , n ) /*n表示可定义多个字段*/ Principle and Application of Database System n例：对student数据库中student2表的 sno字段创建PRIMARY KEY约束，索引 类型为非聚簇，对sname字段定义 UNIQUE约束，索引类型为聚簇。 Principle and Application of Database System CREATE TABLE student2 (sno char(5) PRIMARY KEY NONCLUSTERED, sname char(8) UNIQUE CLUSTERED, ssex char(2), sbirthday datetime, class char(5) ) Principle and Application of Database System 6.3.3 实体完整性的实现 2) 通过修改表创建PRIMARY KEY约束或UNIQUE约束 (1) 创建PRIMARY KEY约束 语法格式： ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( column ,.n ) (2) 创建UNIQUE约束 语法格式： ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED ( column ,.n ) Principle and Application of Database System n例：先在student数据库中创建表 student3,然后通过修改表，sno字段创 建PRIMARY KEY约束，对sname字段定 义UNIQUE约束。 Principle and Application of Database System CREATE TABLE student3 (sno char(5) not null, sname char(8), ssex char(2), sbirthday datetime, class char(5) ) Principle and Application of Database System GO ALTER TABLE student3 ADD CONSTRAINT student3_PK PRIMARY KEY NONCLUSTERED(sno) GO ALTER TABLE student3 ADD CONSTRAINT student3_UK UNIQUE CLUSTERED(sname) Principle and Application of Database System 3) 删除PRIMARY KEY约束或UNIQUE 约束 语法格式： ALTER TABLE table_name DROP CONSTRAINT constraint_name ,n Principle and Application of Database System ALTER TABLE XS5 DROP CONSTRAINT student3_PK,student3_UK Principle and Application of Database System 6.3.4 参照完整性的实现 1. 利用SSMS定义表间的参照关系 Principle and Application of Database System 6.3.4 参照完整性的实现 关系设置界面 Principle and Application of Database System 6.3.4 参照完整性的实现 2. 利用SSMS删除表间的参照关系 如果要删除前面建立的score表 与student、course表之间的参照 关系，可按以下步骤进行： (1) 进入主表或从表的表设计器 界面，在此进入的是从表score的 表设计器，右击鼠标，出现一快捷 菜单，选择菜单项“关系”. (2) 在图的属性界面的关系下拉 表中选择要删除的关系，然后点击 “删除”按钮，选择“关闭”按钮。 score表的参照关系属性界面 Principle and Application of Database System 6.3.4 参照完整性的实现 3. 利用SQL命令定义表间的参照关系 (1) 创建表的同时定义外码约束 语法格式： CREATE TABLE table_name /*指定表名*/ (column_name datatype FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION , n ) /*n表示可定义多个字段*/ Principle and Application of Database System n例 在student数据库中创建主表 student4,course4，sno为student4表的主 键，cno为course4的唯一键，然后定义从 表score4，score4.sno为外码，与 student4表的主键对应，当对主表进行更 新和删除操作时，对从表采用CASCADE操 作，o为外码，与course4的唯一 键对应，当对主表进行更新和删除时，对 从表采用NO ACTION方式。 Principle and Application of Database System CREATE TABLE student4 (sno char(5) PRIMARY KEY, sname