第04章约束表关系多对

数据库操作与管理语言OracleSQL

表的约束本章目标数据完整性主键、外键、非空、默认Check添加、删除约束启用、禁用约束表关系与主外键约束及关联查询一对一一对多多对多数据完整性存储在数据库中的所有数据值均正确的状态。如果数据库中存储有不正确的数据值，则该数据库称为已失去数据完整性。数据完整性(DataIntegrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性-1数据类型年龄使用什么类型？格式是否正确身份证号，可以是15位也可以是18位范围性别可选择“男”、“女”、“未知”是否允许重复银行卡号不允许重复特定业务要求银行贷款数据完整性-2实体完整性域完整性引用完整性自定义完整性实体完整性实体完整性实体完整性要求每一个表中的主键字段都不能为空或者重复的值。实现方法唯一约束主键约束域完整性域完整性域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等实现方法限制数据类型外键约束默认值非空约束引用完整性引用完整性也称之为参照完整性，当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。引用完整性要求关系中不允许引用不存在的实体。引用完整性与实体完整性是关系模型必须满足的完整性约束条件。实现方法外键约束自定义完整性自定义完整性用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。实现方法存储过程触发器表约束表约束的目的：确保表中数据的完整性常用的约束类型：主键约束(PRIMARYKEY)：要求主键列数据唯一，并且不允许为空非空约束(NOTNULL)：指定的列的值不允许为空唯一键约束(UNIQUE)：要求该列唯一，允许为空，但只能出现一个空值检查约束(CHECK)：指定表中一列或多列可以接受的数据值格式默认约束(DEFAULT)：指定某列的默认值外键约束(FOREIGNKEY)：用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行主键：选择主键的原则最少性尽量选择单个键作为主键必须保证唯一。稳定性尽量选择数值更新少的列作为主键一般选择没有意义，且用户不关心，但程序员关心的列做为表的主键。可以使用系统的sys_guid作主键的值。可以使用自动增长的integer列做为主键的值。主键约束的特点：唯一、不可重复。非空NULL。其他特点用户不关心。对用户没有任何意义。但对程序开发人员，意义重大。创建主键约束的语法：有三种：第一种，在创建表时，直接指定主键

SQL>createglobaltemporarytablet1(idintprimarykey,--这儿的primarykey指定了id为主键

namevarchar(30));第二种,在create中单独一行createglobaltemporarytablet3(idint,namevarchar(30),--单独一行创建主键。此时必须要指定主键的名称。

constraintt3_pkprimarykey(id));第三种，通过修改表创建主键--通过修改表，创建主键createglobaltemporarytablet4(idint,namevarchar(30));--修改表，添加主键altertablet4addconstraintt4_pkprimarykey(id);案例：创建主键：CREATETABLEorder_status2(idINTEGERCONSTRAINTorder_status2_pkPRIMARYKEY,statusVARCHAR2(10),last_modifiedDATEDEFAULTSYSDATE);可以通过以下两个系统视图，查询约束信息：

user_constraints--表上的约束User_cons_columns–列上的约束主键约束：向主键字段写入一个null值，将会出现以下错误：在行23上开始执行命令时出错:insertintot4(id,name)values(null,'jack')错误报告:SQL错误:ORA-01400:无法将NULL插入

("HR"."T4"."ID")01400.00000-"cannotinsertNULLinto(%s)"*Cause:*Action:

将主键中写入重复的数据，将出现以下错误insertintot4(id,name)values(88,'jack')错误报告:SQL错误:ORA-00001:违反唯一约束条件

(HR.T4_PK)00001.00000-"uniqueconstraint(%s.%s)violated"*Cause:AnUPDATEorINSERTstatementattemptedtoinsertaduplicatekey.ForTrustedOracleconfiguredinDBMSMACmode,youmayseethismessageifaduplicateentryexistsatadifferentlevel.*Action:Eitherremovetheuniquerestrictionordonotinsertthekey.删除主键约束：--创建带有主键的列createtablet1(idintnotnull,nmvarchar(30),constraintt1_pkprimarykey(id));--删除主键altertablet1dropconstraintt1_pk;--添加多行数据insertintot1values(1,1);select*fromt1;--必须先将重复的数据删除以后才可以添加主键,默认只留下一行deletefromt1whererownum=1;--然后再添加主键altertablet1addconstraintt1_pk2primarykey(id);禁用和启动主键约束：--禁用约束altertablet1disableconstraintt1_pk2;--启动主键约束altertablet1enableconstraintt1_pk2;唯一约束以下ALTERTABLE语句给order_status2表的status列添加UNIQUE约束ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_uqUNIQUE(status);在往order_status2表中添加任意行时，行的status列的值必须要唯一。唯一键约束：Unique是唯一约束，也可称为候选键Unique创建的约束列上，不可以存在相同的值。但可以保存两个null.createglobaltemporarytablet5(idintunique,namevarchar(30));向唯一约束中写入两个相同的值：SQL错误:ORA-00001:违反唯一约束条件(HR.SYS_C007008)00001.00000-"uniqueconstraint(%s.%s)violated"*Cause:AnUPDATEorINSERTstatementattemptedtoinsertaduplicatekey.ForTrustedOracleconfiguredinDBMSMACmode,youmayseethismessageifaduplicateentryexistsatadifferentlevel.*Action:Eitherremovetheuniquerestrictionordonotinsertthekey.删除/启用/禁用唯一约束--创建唯一约束createtablet2(idint,ageint,constraintt2_uqunique(id));--写入记录insertintot2values(1,1);select*fromt2;--删除唯一约束altertablet2dropconstraintt2_uq;--添加唯一约束,如果存在重复的记录则添加不成功altertablet2addconstraintt2_uq2unique(id);--删除重复的数据deletefromt2whererownum=1;--禁用唯一约束altertablet2disableconstraintt2_uq2;--启用唯一约束altertablet2enableconstraintt2_uq2;非空约束非空约束给status列添加非空约束ALTERTABLEorder_status2MODIFYstatusCONSTRAINTorder_status2_status_nnNOTNULL;给modified_by列添加非空约束ALTERTABLEorder_status2MODIFYmodified_byCONSTRAINTorder_status2_modified_by_nnNOTNULL;给last_modified列添加非空约束，不指定约束名ALTERTABLEorder_status2MODIFYlast_modifiedNOTNULL;非空约束:--以下创建一个非空约束createglobaltemporarytablet8(idintnotnull,nmvarchar(30));insertintot8values(null,'ddd');--外部添加createglobaltemporarytablet9(idint,ageint);--修改为非空约束altertablet9modifyidnotnull;insertintot9values(null,33);删除/添加/启用/禁用非空约束：--以下是非空约束createtablet3(idintnotnull,ageint);--查询非空约束select*fromuser_constraintswheretable_name='T3';--写入空值，出错insertintot3values(null,33);deletefromt3;--删除非空约束altertablet3dropconstraintSYS_C007024;--添加非空约束altertablet3modifyidnotnull;--禁用altertablet3disableconstraintSYS_C007025;--启动altertablet3enableconstraintSYS_C007025;默认值约束是指用户没有给定此列的值时，则使用默认值

--添加默认值约束createglobaltemporarytablet10(idint,ageintdefault0);createglobaltemporarytablet11(idint,ageint);--添加默认值altertablet11modifyagedefault11;CHECK约束添加CHECK约束ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_ckCHECK(statusIN('PLACED','PENDING','SHIPPED'));测试数据正确数据INSERTINTOorder_status2(id,status,last_modified,modified_by)VALUES(1,'PENDING','01-1月-2005',1);错误数据INSERTINTOorder_status2(id,status,last_modified,modified_by)VALUES(2,'CLEARED','01-1月-2005',2);提示错误：ORA-02290:违反检查约束条件(STORE.ORDER_STATUS2_STATUS_CK)Check约束性别：--check约束createtablet4(idint,sexchar(1)constraintsex_chkcheck(sexin('1','0')));--写入一行记录insertintot4values(2,'0');check约束区间：createtablet5(idint,ageint);--限制年龄在0~100altertablet5addconstraintt5_chkcheck(age>=0andage<=100);--查询select*fromuser_constraintswheretable_name='T5';--写入数据,age>100将不成功insertintot5values(3,144);deletefromt5;--禁用altertablet5disableconstraintt5_chk;--启用altertablet5enableconstraintt5_chk;Check约束字符串：--添加一个是检查，只可以写入以M开始的数据altertablepersonaddconstraintp_ckcheck(namelike'M%');主外键约束与范式：范式：第一范式：对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.第三范式要求非主键列互不依赖.第四范式禁止主键列和非主键列一对多关系不受约束第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余.主键与外键主键与外键是维护多表关系的有效手段。注意事项当主表中没有对应的记录时，不能将记录添加到子表不能更改主表中的值而导致子表中的记录孤立子表存在与主表对应的记录，不能从主表中删除该记录删除主表前，先删子表E-R模型E-R模型的特点

Entities：实体Attributes：属性Relationships：关系通常有许多表通常是满足3NF的主键/外键1对多映射建立E-R模型是数据库概念设计的重要内容，而概念设计是设计阶段的组成部分。同时建立E-R模型的工作，属于软件生命周期的设计阶段。E-R图示例外键约束-1添加外键约束--创建主外键关系createtableperson(idvarchar（32),namevarchar(30),constraintp_pkprimarykey(id));--创建外表createtablecar(idvarchar(32),namevarchar(30),pidvarchar(32),constraintc_pkprimarykey(id),

constraintc_fkforeignkey(pid)referencesperson(id));请插入数据、删除数据、删除约束、添加约束进行测试。外键约束-2在创建外键约束时可以添加ONDELETECASCADE选项，那么当主表的数据被删除时，子表对应的行同样也自动被删除。例如--创建主外键关系且设置级联删除createtablep2(idint,nmvarchar2(30),constraintp2_pkprimarykey(id）);--下面的ondeletecascade就是删除时级联createtablec2(idint,nmvarchar2(30),pidint,constraintc2_pkprimarykey(id),constraintc2_fkforeignkey(pid)referencesp2(id)ondeletecascade);外键约束-3在创建外键约束时可以添加ONDELETESETNULL选项，那么当主表的数据被删除时，子表匹配的相关行的列会被设置为NULL值，而不是被删除例如设置外键ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_modified_by_fkmodified_byREFERENCESemployees(employee_id)ONDELETESETNULL;表与表的关系：一对一一对一的场景一对一的实现方案主键对主键的一对一主键对唯一的一对一一对一的查询innerjoin,leftjoin,rightjoing一对多一对多的场景主键对外键的引用一对多的查询多对多多对多的场景多的多的实现使用中间表实现多对多多对多的查询大量案例查看和修改约束信息：修改约束-1删除约束通过ALTERTABLE的DROPCONSTRAINT选项删除约束ALTERTABLEorder_status2DROPCONSTRAINTorder_status2_status_uq;停用约束约束在创建完之后就生效了，也可以在创建时在CONSTRAINT关键字末尾加DISABLE关键字，默认停用它。ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_uqUNIQUE(status)DISABLE;也可以停用已经启用的约束，例如约束order_status2_status_nnALTERTABLEorder_status2DISABLECONSTRAINTorder_status2_status_nn;修改约束-2启用约束可以使用ALTERTABLE的ENABLECONSTRAINT选项将已存在的约束失效启用。例如ALTERTABLEorder_status2ENABLECONSTRAINTorder_status2_status_uq;说明在启用约束时，表中所有的数据行的数据必须要满足这个约束。也可以通过设置ENABLENOVALIDATE，对新的数据行增加约束ALTERTABLEorder_status2ENABLENOVALIDATECONSTRAINTorder_status2_status_uq;说明默认是ENABLEVALIDATE，对存在的数据行也要求符合约束延迟约束延迟约束(DeferredConstraint)就是在事务提交的时候才生效可以在约束中加DEFERRABLE选项约