搜索
Copyright©OracleCorporation,2001.Allrightsreserved.约束10-2Copyright©OracleCorporation,2001.Allrightsreserved.目标通过本章学习，您将可以:•描述约束•创建和维护约束10-3Copyright©OracleCorporation,2001.Allrightsreserved.什么是约束•约束是表级的强制规定•约束放置在表中删除有关联关系的数据•有以下五种约束:–NOTNULL–UNIQUE–PRIMARYKEY–FOREIGNKEY–CHECK10-4Copyright©OracleCorporation,2001.Allrightsreserved.注意事项•如果不指定约束名Oracleserver自动按照SYS_Cn的格式指定约束名•在什么时候创建约束:–建表的同时–建表之后•可以在表级或列级定义约束•可以通过数据字典视图查看约束10-5Copyright©OracleCorporation,2001.Allrightsreserved.定义约束CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],...[table_constraint][,...]);CREATETABLEemployees(employee_idNUMBER(6),first_nameVARCHAR2(20),...job_idVARCHAR2(10)NOTNULL,CONSTRAINTemp_emp_id_pkPRIMARYKEY(EMPLOYEE_ID));10-6Copyright©OracleCorporation,2001.Allrightsreserved.定义约束•列级•表级column[CONSTRAINTconstraint_name]constraint_type,column,...[CONSTRAINTconstraint_name]constraint_type(column,...),10-7Copyright©OracleCorporation,2001.Allrightsreserved.NOTNULL约束保证列值不能为空:NOTNULL约束无NOTNULL约束NOTNULL约束…10-8Copyright©OracleCorporation,2001.Allrightsreserved.CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL,...NOTNULL约束只能定义在列级:系统命名用户命名10-9Copyright©OracleCorporation,2001.Allrightsreserved.UNIQUE约束EMPLOYEESUNIQUE约束INSERTINTO不允许:已经存在允许…10-10Copyright©OracleCorporation,2001.Allrightsreserved.UNIQUE约束可以定义在表级或列级:CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...CONSTRAINTemp_email_ukUNIQUE(email));10-11Copyright©OracleCorporation,2001.Allrightsreserved.PRIMARYKEY约束DEPARTMENTSPRIMARYKEYINSERTINTO不允许(空值)不允许(50已经存在)…10-12Copyright©OracleCorporation,2001.Allrightsreserved.CREATETABLEdepartments(department_idNUMBER(4),department_nameVARCHAR2(30)CONSTRAINTdept_name_nnNOTNULL,manager_idNUMBER(6),location_idNUMBER(4),CONSTRAINTdept_id_pkPRIMARYKEY(department_id));PRIMARYKEY约束可以定义在表级或列级:10-13Copyright©OracleCorporation,2001.Allrightsreserved.FOREIGNKEY约束DEPARTMENTSEMPLOYEESFOREIGNKEYINSERTINTO不允许(9不存在)允许PRIMARYKEY……10-14Copyright©OracleCorporation,2001.Allrightsreserved.FOREIGNKEY约束可以定义在表级或列级:CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...department_idNUMBER(4),CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id),CONSTRAINTemp_email_ukUNIQUE(email));10-15Copyright©OracleCorporation,2001.Allrightsreserved.FOREIGNKEY约束的关键字•FOREIGNKEY:在表级指定子表中的列•REFERENCES:标示在父表中的列•ONDELETECASCADE:当父表中的列被删除是，子表中相对应的列也被删除•ONDELETESETNULL:子表中相应的列置空10-16Copyright©OracleCorporation,2001.Allrightsreserved.CHECK约束•定义每一行必须满足的条件•以下的表达式是不允许的:–出现CURRVAL,NEXTVAL,LEVEL,和ROWNUM伪列–使用SYSDATE,UID,USER,和USERENV函数–在查询中涉及到其它列的值...,salaryNUMBER(2)CONSTRAINTemp_salary_minCHECK(salary0),...10-17Copyright©OracleCorporation,2001.Allrightsreserved.添加约束的语法使用ALTERTABLE语句:•添加或删除约束,但是不能修改约束•有效化或无效化约束•添加NOTNULL约束要使用MODIFY语句ALTERTABLEtableADD[CONSTRAINTconstraint]type(column);10-18Copyright©OracleCorporation,2001.Allrightsreserved.添加约束添加约束举例ALTERTABLEemployeesADDCONSTRAINTemp_manager_fkFOREIGNKEY(manager_id)REFERENCESemployees(employee_id);Tablealtered.10-19Copyright©OracleCorporation,2001.Allrightsreserved.删除约束•从表EMPLOYEES中删除约束•使用CASCADE选项删除约束ALTERTABLEemployeesDROPCONSTRAINTemp_manager_fk;Tablealtered.ALTERTABLEdepartmentsDROPPRIMARYKEYCASCADE;Tablealtered.10-20Copyright©OracleCorporation,2001.Allrightsreserved.无效化约束•在ALTERTABLE语句中使用DISABLE子句将约束无效化。•使用CASCADE选项将相关的约束也无效化ALTERTABLEemployeesDISABLECONSTRAINTemp_emp_id_pkCASCADE;Tablealtered.10-21Copyright©OracleCorporation,2001.Allrightsreserved.激活约束•ENABLE子句可将当前无效的约束激活•当定义或激活UNIQUE或PRIMARYKEY约束时系统会自动创建UNIQUE或PRIMARYKEY索引ALTERTABLEemployeesENABLECONSTRAINTemp_emp_id_pk;Tablealtered.10-22Copyright©OracleCorporation,2001.Allrightsreserved.及连约束•CASCADECONSTRAINTS子句在DROPCOLUMN子句中使用•在删除表的列时CASCADECONSTRAINTS子句指定将相关的约束一起删除•在删除表的列时CASCADECONSTRAINTS子句同时也删除多列约束10-23Copyright©OracleCorporation,2001.Allrightsreserved.及连约束及连约束举例:ALTERTABLEtest1DROP(pk)CASCADECONSTRAINTS;Tablealtered.ALTERTABLEtest1DROP(pk,fk,col1)CASCADECONSTRAINTS;Tablealtered.10-24Copyright©OracleCorporation,2001.Allrightsreserved.SELECTconstraint_name,constraint_type,search_conditionFROMuser_constraintsWHEREtable_name='EMPLOYEES';查询约束查询数据字典视图USER_CONSTRAINTS…10-25Copyright©OracleCorporation,2001.Allrightsreserved.SELECTconstraint_name,column_nameFROMuser_cons_columnsWHEREtable_name='EMPLOYEES';查询定义约束的列查询数据字典视图USER_CONS_COLUMNS…10-26Copyright©OracleCorporation,2001.Allrightsreserved.总结通过本章学习，您已经学会如何创建约束描述约束的类型:–NOTNULL–UNIQUE–PRIMARYKEY–FOREIGNKEY–CHECK•查询数据字典视图以获得约束的信息10-29Copyright©OracleCorporation,2001.Allrightsreserved.