Oracle：约束_表关系_多对多

数据库操作与管理语言OracleSQL表的约束本章目标数据完整性主键、外键、非空、默认Check添加、删除约束启用、禁用约束表关系与主外键约束及关联查询一对一一对多多对多数据完整性存储在数据库中的所有数据值均正确的状态。如果数据库中存储有不正确的数据值，则该数据库称为已失去数据完整性。数据完整性(DataIntegrity)是指数据的精确性(Accuracy)和可靠性(Reliability)。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性-1数据类型年龄使用什么类型？格式是否正确身份证号，可以是15位也可以是18位范围性别可选择“男”、“女”、“未知”是否允许重复银行卡号不允许重复特定业务要求银行贷款数据完整性-2实体完整性域完整性引用完整性自定义完整性实体完整性实体完整性实体完整性要求每一个表中的主键字段都不能为空或者重复的值。实现方法唯一约束主键约束域完整性域完整性域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等实现方法限制数据类型外键约束默认值非空约束引用完整性引用完整性也称之为参照完整性，当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。引用完整性要求关系中不允许引用不存在的实体。引用完整性与实体完整性是关系模型必须满足的完整性约束条件。实现方法外键约束自定义完整性自定义完整性用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。实现方法存储过程触发器表约束表约束的目的：确保表中数据的完整性常用的约束类型：主键约束(PRIMARYKEY)：要求主键列数据唯一，并且不允许为空非空约束(NOTNULL)：指定的列的值不允许为空唯一键约束(UNIQUE)：要求该列唯一，允许为空，但只能出现一个空值检查约束(CHECK)：指定表中一列或多列可以接受的数据值格式默认约束(DEFAULT)：指定某列的默认值外键约束(FOREIGNKEY)：用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行主键：选择主键的原则最少性尽量选择单个键作为主键必须保证唯一。稳定性尽量选择数值更新少的列作为主键一般选择没有意义，且用户不关心，但程序员关心的列做为表的主键。可以使用系统的sys_guid作主键的值。可以使用自动增长的integer列做为主键的值。主键约束的特点：唯一、不可重复。非空NULL。其他特点用户不关心。对用户没有任何意义。但对程序开发人员，意义重大。创建主键约束的语法：有三种：第一种，在创建表时，直接指定主键SQLcreateglobaltemporarytablet1(idintprimarykey,--这儿的primarykey指定了id为主键namevarchar(30));第二种,在create中单独一行createglobaltemporarytablet3(idint,namevarchar(30),--单独一行创建主键。此时必须要指定主键的名称。constraintt3_pkprimarykey(id));第三种，通过修改表创建主键--通过修改表，创建主键createglobaltemporarytablet4(idint,namevarchar(30));--修改表，添加主键altertablet4addconstraintt4_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,youmayseethismessageifaduplicateentryexistsatadifferentlevel.*Action:Eitherremovetheuniquerestrictionordonotinsertthekey.删除主键约束：--创建带有主键的列createtablet1(idintnotnull,nmvarchar(30),constraintt1_pkprimarykey(id));--删除主键altertablet1dropconstraintt1_pk;--添加多行数据insertintot1values(1,1);select*fromt1;--必须先将重复的数据删除以后才可以添加主键,默认只留下一行deletefromt1whererownum=1;--然后再添加主键altertablet1addconstraintt1_pk2primarykey(id);禁用和启动主键约束：--禁用约束altertablet1disableconstraintt1_pk2;--启动主键约束altertablet1enableconstraintt1_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,youmayseethismessageifaduplicateentryexistsatadifferentlevel.*Action:Eitherremovetheuniquerestrictionordonotinsertthekey.删除/启用/禁用唯一约束--创建唯一约束createtablet2(idint,ageint,constraintt2_uqunique(id));--写入记录insertintot2values(1,1);select*fromt2;--删除唯一约束altertablet2dropconstraintt2_uq;--添加唯一约束,如果存在重复的记录则添加不成功altertablet2addconstraintt2_uq2unique(id);--删除重复的数据deletefromt2whererownum=1;--禁用唯一约束altertablet2disableconstraintt2_uq2;--启用唯一约束altertablet2enableconstraintt2_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);--修改为非空约束altertablet9modifyidnotnull;insertintot9values(null,33);删除/添加/启用/禁用非空约束：--以下是非空约束createtablet3(idintnotnull,ageint);--查询非空约束select*fromuser_constraintswheretable_name='T3';--写入空值，出错insertintot3values(null,33);deletefromt3;--删除非空约束altertablet3dropconstraintSYS_C007024;--添加非空约束altertablet3modifyidnotnull;--禁用altertablet3disableconstraintSYS_C007025;--启动altertablet3enableconstraintSYS_C007025;默认值约束是指用户没有给定此列的值时，则使用默认值--添加默认值约束createglobaltemporarytablet10(idint,ageintdefault0);createglobaltemporarytablet11(idint,ageint);--添加默认值altertablet11modifyagedefault11;CHECK约束添加CHECK约束ALTERTABLEorder_status2ADDCONSTRAINTorder_status2_status_ckCHECK(statusIN('PLACED','PENDING','SHIPPED'));测试数据正确数据INSERTINTOorder_status2(id,status,