零点起飞学oracle第11章使用约束

第11章使用约束约束是用来束缚表中的数据的，使表中的数据满足某种条件。我们可以通过约束保证数据库数据的完整性和可靠性。Oracle的主要约束包括主键约束、外键约束、唯一性约束、检查约束、默认值约束、非空约束。本章主要学习Oracle中约束的创建，并深入理解这些约束的使用。11.1使用主键约束主键约束用来标识表中一个列，一个表中的主键约束只有一个，但是可以在一个主键约束中包含多个列，也成为联合主键。本节将着重讲述主键的创建、使用及相关操作。11.1.1为什么要使用主键主键约束是Oracle数据库的最常用约束，也是数据库设计者最需要注意的约束。主键可以像普通数据库对象那样进行创建、修改、删除等操作。在数据表设计时，数据表一般要求满足BCNF范式。这要求数据表至少含有一个候选码（可看作唯一性约束）。而其中一个候选码又被选为主码。这里的主码，可以看做数据表的主键。除了数据表设计的一般规则之外，主键的出现还有以下现实意义。1．实现记录的唯一性——实体完整性2．检索有利3．支持外键11.1.2什么是主键主键用来唯一地标识表中的一行数据，它规定在主键列上的数据不能重复，并且不能为空。如果在某个列上指定了主键约束，那么就不需要在该列上再指定notnull约束和unique约束。创建主键时，有以下注意事项。主键列的数据类型：我们设置主键时，主键列的数据类型不一定是数值型，也可以使其他的，如字符型。主键不一定只有一列：在表的设计视图中，选择可以要定义为主键的一个或多个字段。自增的数值型主键：设置主键时，可以采用自增的列作为主键，实现自动编号，速度快，对于检索非常有利。11.1.3创建主键约束主键约束在每一个数据表中只有一个。创建主键约束可以在创建表时与表的列描述同时进行，也可以在表创建成功之后，手动进行添加。1．创建表的同时，定义主键约束我们在创建表时定义主键约束。对于单列主键来说，可以在列的描述之后使用primarykey关键字将该列指定为主键列。其语法形式如下所示。createtable表名(列名1数据类型1

primarykey,列名2数据类型2,列名3数据类型......)其中，关键字primarykey紧跟在列1的描述之后，那么列1将作为数据表的主键列。该表也将拥有一个主键约束。11.1.3创建主键约束【示例11-1】重新创建表employeenew，并为表建立主键，主键列为employee_id。【示例11-2】对于单列主键，可以在列的描述中定义主键。但是对于多列主键，则必须将主键描述与列的描述并列进行。例如，在表employeenew中，希望将employee_id、last_name两列作为主键。11.1.3创建主键约束2．创建表后，添加主键约束在示例11-1和示例11-2中，我们总是将表首先删除，然后再重新创建数据表，并在重建过程中创建主键约束。此外，我们还可以不删除表，为已存在的表添加主键约束。此时可以利用altertable命令。利用altertable命令为表添加主键的语法形式如下所示。altertable表名add

primarykey(列名1,列名2…)其中，altertable命令用于修改表的属性；primarykey(列名1,列名2…)则指定主键被建立在哪些列之上，各列名之间使用逗号进行分隔。【示例11-4】例如，为表employeenew添加主键。11.1.3创建主键约束3．为主键命名主键是约束的一种，可以利用为表添加约束的方式来显式命名约束，从而实现命名主键。其语法形式如下所示。altertable表名add

constraint约束名称primarykey(列名1,列名2…)其中，altertable命令用于修改表的属性；addconstraint用于为表添加约束，并指定约束名称；primarykey(列名1,列名2…)指定约束的详细定义。【示例11-5】在SQLDeveploper中创建主键约束，并为约束显式命名。11.1.3创建主键约束4．测试主键的作用当在一个表上创建主键之后，向表中添加数据就有了约束。此时，输入数据时表中所有数据在主键列上的值（或值的组合）不能重复，并且主键列上的值不能为空。【示例11-6】尝试向表employeenew中插入已存在的id的记录，Oracle将抛出错误提示，并禁止用户的插入操作。【示例11-7】除不能向表employeenew中插入employee_id冲突的数据之外，同样不能插入iemployee_id为空的数据。11.1.3创建主键约束【示例11-8】单列主键约束要求主键列值不能重复，而联合主键要求主键列值的组合不能重复。例如，可以将表employeenew的主键修改为列employee_id与列last_name的联合主键，并测试主键的作用。【示例11-9】对于联合主键中的任意一列，其值均不可为空。例如，尝试向表employeenew中插入employee_id为空的数据，Oracle同样会抛出错误。11.1.4修改主键约束主键约束创建之后，可以对其进行属性修改。修改主键约束主要包括两方面的内容：禁用/启用主键和重命名主键。1．禁用/启用主键在Oracle中，对于任何已经创建好的约束，可以使用ENABLE或者是DISABLE来启用约束或者是禁用约束。禁用的约束好像并没有创建约束一样，并没有达到创建约束的目的，但是通过ENABLE子句，可以随时启用约束。禁用/启用数据表主键的语法如下所示。altertable表名disableprimarykey其中，disalbe

primarykey用于禁用数据表主键，因为数据表的主键的唯一性，因此，无需指定主键名称。11.1.4修改主键约束【示例11-10】表employeenew的主键pk_employeenew的当前状态为可用，那么不允许插入employee_id与last_name与已有记录冲突的数据。现禁用该主键，并再次尝试插入数据。【示例11-11】当启用主键约束时，Oracle仍然会检查数据表中现有数据是否违反了主键约束。如果违反，那么主键的启用也将失败。11.1.4修改主键约束2．重命名主键如果需要修改已有主键的名称，则需要使用到重命名主键策略。重命名主键的语法如下所示。altertable表名renameconstraint原主键名称to新主键名称其中，rename

constraint用于重命名约束，主键是约束的一种，因此，该选项同样适用于重命名主键；to关键字则用于指定新的主键名称。【示例11-12】重命名主键最常用的场合为修改系统自动生成的主键名称。11.1.5主键与索引索引可以使用户快速找到表中的特定信息。当用户在数据表的某列（或某些列）上创建了主键、而在检索数据时又使用了该索引列，Oracle可以很快的捕获符合条件的记录。而不必采用全表逐条扫描的方式。有关索引的详细信息，将在以后的章节讲解。11.1.5主键与索引【示例11-13】在Oracle中，创建了主键之后，都会存在一个建立在主键列上的索引。例如，表employeenew的主键pk_employeenew建立在列employee_id上。我们可以通过视图user_indexes获得表employeenew上索引的详细信息。【示例11-14】虽然索引名称不会随着主键的重命名而重命名，但是却会随着主键的删除而被删除。【示例11-15】当Oracle创建主键时，会首先查看主键列上是否已经创建了索引。如果未创建，则自动创建；如果已创建，Oracle直接创建主键，而不会进行索引创建。已存在的索引也和主键没有任何连带关系，因此，当删除主键时，不会触发索引的任何动作。11.1.5主键与索引下表总结了本节的主要内容，并概括了主键的特点。主键特点说明数据库操作和相关视图约束主键值约束的一种user_constraints、user_ind_columns主键的唯一性主键是主码的体现删除主键时，无需提供主键名称主键的值不能为空无法通过id=null的条件获得记录禁止向主键列插入空值null主键与唯一性约束主码来源于码禁止向主键列中插入重复数据（或组合）主键与索引主键将保证主键列上有索引user_indexs、user_ind_columns11.2使用外键约束主键的作用是保证数据完整性——即保证数据的唯一性。外键约束可以保证使用外键约束的列与所引用的主键约束的列一致。主键可以看做表中数据之间的区分。而外键约束则适用于不同的表之间的相互参照关系。11.2.1为什么要使用外键外键的主要作用是保证数据的参照完整性。最终保证数据库的完整性。本小节将讲述参照完整性的定义，并阐述外键定义及使用。1．参照完整性在两个表之间，一个表中的记录依附于另一个表的记录而存在，称为表之间的参照完整性。参照完整性总是存在着真实的业务背景。例如，在employeenew表中，存储了每位员工的信息；在jobs表中存储了员工职位信息。employeenew中的每条记录都依附于jobs表中记录的存在而存在，即建立了两个表之间的参照完整性。11.2.1为什么要使用外键2．外键的必要性参照完整性的设计是必要的。当人工输入的employeenew表的数据信息时，很难保证输入者精确地输入所有数据。更为严重的是，即使输入错误，获取错误数据的具体位置也是一件费力的工作。在数据库中，有规范而统一的解决方案——参照完整性。外键是实现参照完整性的手段，因此，在一个数据库中，外键的存在是非常必要的。11.2.2什么是外键外键实际是一个引用。一个数据表有自己的主键，而向外部其他数据表的引用，则称作外键。外键实际隐含了对外部引用的限制——必须获得外部数据表的唯一记录。11.2.3创建外键约束外键是约束的一种，外键约束创建后即可在数据字典中获得相应信息。1．创建外键约束创建外键约束的语法如下所示。altertable表名addconstraint约束名foreignkey(外键列名)

references主表(主表主键列)

其中，altertable用于修改从表的属性；addconstraint用于为表添加约束；foreignkey指定约束的具体类型——外键；references主表（主表主键列）用于指定外键引用的另一端——主表及主表的主键列。【示例11-16】外键约束必然存在主表与从表。对于employeenew表和jobs的主从关系，我们首先创建表employeenew和表jobs，上一节中已经创建employeenew。在此处需创建jobs表。11.2.3创建外键约束2．查看外键信息同样可以在视图user_constraints和user_cons_columns中获取外键的详细信息。【示例11-17】在视图user_cosntraints中获取表employeenew的约束信息。11.2.3创建外键约束3．验证外键约束的作用当尝试向表employeenew中插入数据，可以验证外键约束的作用。【示例11-18】向表employeenew中插入新的数据。【示例11-19】再次向表employeenew尝试插入信息。【示例11-20】当然，除了插入操作之外，数据修改操作同样会受到外键约束的限制。11.2.4级联更新与级联删除当需要修改主表和从表中的数据时，主表的操作，将连带影响到从表的操作，这就是级联更新与级联删除问题。所谓级联更新，是指当主表中的主键列进行修改时，子表的外键列也应该进行相应的修改。级联删除是指当主表中的记录删除时，子表中与之相关的记录也应该同时删除。外键约束之所以会限制父表与子表的更新，是因为数据完整性校验无法通过。该校验有两种类型——即时校验（immediate）和延迟校验（deferred）。默认为即时校验，即每执行一条语句，都会进行校验；而延迟校验可以指定校验的时机。11.2.4级联更新与级联删除1．级联更新Oracle外键只有级联删除没有级联更新，但是有时候系统中可能需要这样的功能，我们可以通过延迟校验的策略来实现级联更新。延迟约束表示不会再刚进行修改的时候进行约束查看，只有提交的时候才会检查。创建延迟校验的外键约束的语法如下所示。altertable表名addconstraint外键名称foreignkey(外键列)references主表名(主表主键列)deferrableinitiallydeferred其中，deferrableinitiallydeferred表示新建约束使用了延迟校验机制。【示例11-21】我们可以首先删除表employeenew的原有外键约束，并重建该约束，在重建过程中指定延迟校验规则。11.2.4级联更新与级联删除2．级联删除Oracle为级联删除提供了直接的处理方式——在创建外键时直接指定。相应的语法如下所示。altertable表名addconstraint外键名称foreignkey(外键列)references主表名(主表主键列)ondeletecascade其中，ondeletecascade指定外键采用级联删除机制。此处的级联删除是指，当用户删除主表中的某条记录时，Oracle会自动搜索并删除从表中的相关记录。当然，这里的相关性是由外键约束的引用来提供的。【示例11-22】对于表employeenew，可以首先删除现有外键，并为其创建级联删除外键。11.2.5外键的相关操作外键是约束的一种，外键的相关操作包括，重命名、禁用/启用、是否校验已有数据的参照完整性、删除外键。1.重命名重命名一个外键，应该使用rename选项。【示例11-23】将表employeenew的外键EM_JOBS_FK重命名为E_JOBS_FK。11.2.5外键的相关操作2.禁用/启用外键有时，用户需要插入特殊数据，此时需要禁用现有的外键约束。禁用外键约束是指，将外键约束的校验功能暂时关闭，如同外键不存在一样，直至再次打开。禁用外键应该使用disable选项。【示例11-24】禁用表employeenew的外键约束E_JOBS_FK。11.2.5外键的相关操作3.是否校验已有数据的参照完整性由于数据完整性校验失败，所以导致无法启用主键约束。此时，应该采用忽略已有数据参照完整性的策略，来启用外键约束。【示例11-25】忽略已有数据的参照完整性校验，应该使用novalidate选项。以无法正常启用的外键E_JOBS_FK为例。11.2.5外键的相关操作4．删除外键当一个外键约束没有存在的必要，则需要及时删除。删除外键应该使用drop

constraint选项。11.2.6外键使用总结外键是建立在两个表的参照的基础上，用以描述两个表之间的主从关系。对于外键约束，应该注意以下两点。1．严格遵守主从关系的数据表应该使用外键在数据库中，如果要求两个表严格遵守主从关系，那么应该使用外键约束。外键约束是主从关系的最佳保障方案。不要求助于应用程序来代替这一策略。11.2.6外键使用总结2.不能过多使用外键合理使用外键可以有效增强应用系统的健壮性。但是，不能过分使用外键。首先，使用过多的外键，会增加数据库复杂度。另外，过分使用外键，有可能影响应用系统的效率。综上所述，使用外键的原则为——需要严格遵守主从关系的数据表应该使用外键，否则，不应使用外键约束。11.3使用唯一性约束在数据表中，唯一性约束是用来表示一个列中的输入值是唯一的。这个约束与前面学习的主键非常相似。不同的就是唯一约束在一个表中可以有多个，而主键约束在一个表中只能有一个。唯一性约束与主键约束一样，也是建立在一个或多个列之上，从而实现数据在该列或者列组合上的唯一性。11.3.1为什么要使用唯一性约束使用唯一性约束可以保证在非主键列中不输入重复值，对于唯一性约束中的列，表中不允许有两行包含相同的非空值。11.3.2什么是唯一性约束唯一性约束和主键有一定的相似性。同时也是主键约束的有益补充。本小节将通过比较二者的异同来介绍唯一性约束。1．二者的相同点2．二者的不同点11.3.3创建唯一性约束可以利用创建约束的一般语法来新建唯一性约束。在创建成功之后，同样可以利用数据字典获取其详细信息。1．创建唯一性约束创建唯一性约束的语法如下所示。altertable表名addconstraint约束名称unquie(列名1、列名2…)其中unique表示该约束是一个唯一性约束；小括号内指定唯一性约束创建在哪些列之上，多列之间使用逗号进行分隔。11.3.3创建唯一性约束2．查询唯一性约束的详细信息对于唯一性约束，同样可以在视图user_constraints及user_cons_columns中获取其信息。11.3.3创建唯一性约束3.验证唯一性约束的有效性唯一性约束创建之后，要求在约束列上的值（或值的组合）保持一致。例如，对于表employeenew，该表具有一个建立在列email之上的唯一性约束。【示例11-26】查看表employeenew中的现有记录。尝试向其中插入新的数据，新纪录的email值与id是208的员工重复【示例11-27】值得注意的是，唯一性约束列允许空值存在，我们可以将列email的数据置为空，再次进行验证。11.3.4修改唯一性约束唯一性约束创建之后，可以对其进行删除、重命名、禁用/启用等操作。1．删除唯一性约束一个表中可以包含多个唯一性约束。对于不再需要的唯一性约束，同样可以使用dropconstraint选项进行删除。【示例11-28】假设，随着业务的需求和发展，表employeenew上的唯一性约束uk_employeenew不再需要，那么可以利用如下SQL语句进行删除。11.3.4修改唯一性约束2．重命名唯一性约束已经创建的约束可以将其重命名。其语法形式如下所示。altertable表名rename

constraint原约束名to目标约束名【示例11-29】例如，将表employeenew上创建的唯一性约束uk_employeenew重命名为uk_em，那么，可以利用rename选项进行重命名。11.3.4修改唯一性约束4．禁用/启用唯一性约束成功创建的约束，可以对其进行禁用/启用操作。唯一性约束一旦禁用，当数据表状态发生改变时，Oracle不再利用该约束进行校验。但是，当启用时，将对表中所有数据进行校验。禁用/启用唯一性约束的语法遵循统一语法。altertable表名disable/enableconstraint约束名称【示例11-30】对于表employeenew，首先禁用其唯一性约束uk_em。11.4使用检查约束检查约束能够规定每一个列输入的值，保证数据的正确性。前面讲的主键约束、外键约束与唯一性约束都有同一个特点，那就是它们都体现记录之间的相互关系，如下表所示。约束名称主要作用作用描述主键实体完整性同一表中的记录的唯一性标识外键参照完整性两个表中的记录的主从关系唯一性约束记录唯一性同一表中不同记录的区分11.4.1为什么要使用检查约束检查约束用来约束表中列的输入值的范围。比如在输入性别时，要求在数据库中只能输入男或者女，就可以使用检查约束来约束该列。检查约束实质是一个布尔表达式。一旦在数据表上创建了检查约束，那么该检查约束将在数据更新时计算布尔表达式的值。一个检查约束可以用来限制某列的取值范围，还可以用来限制多列之间的关系。11.4.2创建检查约束我们可以使用PL/SQL语句创建检查约束，在成功创建之后还可以查询其详细信息。1．创建检查约束创建检查约束时需要将约束的具体类型定义为check，如下所示。altertable表名addconstraint约束名check(布尔表达式)其中，altertable命令用于修改表的属性；addconstraint用于添加约束；check选项指定新建约束为一个检查约束；小括号内的布尔表达式代表检查约束的定义。在表employeenew中，列salary代表员工薪资。我们需要限制薪资是大于0的，那么应该为表employeenew添加检查约束。11.4.2创建检查约束2．查询检查约束的详细信息可以利用数据字典user_constraints与user_cons_columns查看检查约束的详细信息。11.4.2创建检查约束3．验证检查约束的作用在成功创建检查约束ck_sa之后，可以尝试向其中插入非法数据，以验证其作用。11.4.2创建检查约束4．多条件检查约束检查约束可以对多个条件同时进行校验。这些条件使用逻辑运算符进行联接。【示例11-31】在表employeenew中，含有列hire_date与列department_id，都不可以为空，可以为该表创建一个检查约束，该检查约束含有这两种检查条件，分别用来保证各自列不为空。11.4.3修改检查约束检查约束创建之后，可以对检查约束进行重命名、删除和禁用/启用等操作。1．重命名检查约束检查约束的重命名与其他约束的重命名具有完全相同的语法，即利用rename选项。例如，将检查约束ck_sa重命名为ck_salary。11.4.3修改检查约束2．禁用/启用检查约束检查约束的禁用/启用操作应当使用disable/enable选项。11.4.3修改检查约束3．删除检查约束检查约束的删除操作应当使用drop选项。例如，删除检查约束ck_hire_de。11.5使用默认值约束在前面章节中讲述了主键约束、外键约束、唯一性约束和检查约束，我们可以利用下表进行概括。本节主要讲解默认值约束。默认值约束是针对记录的列本身进行约束。约束名称主要作用作用描述主键实体完整性同一表中的记录的唯一性标识外键参照完整性两个表中的记录的主从关系唯一性约束记录唯一性同一表中不同记录的区分检查约束列的取值限制同一记录中列的取值范围或列之间的关系11.5.1为什么要使用默认值约束创建数据表之后，为了避免在插入语句中为其显式赋值，可以为该列指定默认值。当用户在插入数据时，不对该列显式赋值，从而减少了用户的工作量，提高了工作效率。在创建数据库表时，我们可以指定一个DEFAULT值（即默认值）。对数据库字段使用默认值有助于将数据库设计问题。当没有设定默认值约束时，我们可以将列的默认值看做空，即null；当显式设定默认值时，列的默认值发生改变。因此，我们可以认为列的默认值总是存在的。11.5.2创建默认值约束创建默认值约束之后，还是可以使用相应的数据字典获得默认约束的相关信息。1．创建默认值约束默认值约束是作为列的属性存在。因此，创建默认值约束实际是通过修改列属性的途径来实现的。创建默认值约束应该使用modify选项，并指定约束类型为default（默认值）。【示例11-32】例如，指定表employeenew的列hire_date的默认值为系统的当天日期。11.5.2创建默认值约束2．查看默认值约束的信息默认值约束并不是作为表的属性，而是作为列的属性而存在的。user_constraints中的约束都是针对表而言，因此，无法在数据字典user_constraints中获得其信息。而数据字典user_tab_columns包含了所有用户列的信息。11.5.3修改默认值约束默认值约束的创建之后，可以对其进行删除。删除默认值约束的方式为将其设置为null。【示例11-34】在表employeenew中，删除列hire_date的默认值。11.6使用非空约束非空约束同样是针对表的列所进行的约束。非空约束用于限制列值不能为空。非空约束可以实现数据的域完整性。11.6.1为什么要使用非空约束一般在默认情况下，数据表的列值允许为空。但是，有时候，并不允许用户忽略列值。所以，必须使用非空约束保证列值不为空。前面章节已经详细讲解了主键约束、唯一性约束、检查约束、默认值约束。本小节主要讲解非空约束的创建与使用，以及非空约束与其它约束的比较。11.6.1为什么要使用非空约束1.非空约束与检查约束检查约束可以限制数据表中的某列的取值范围。当这个取值范围被限定为非空（