Oracle-OCP-SQL-10-Including-Constraints

Copyright©OracleCorporation,2001.Allrightsreserved.IncludingConstraints10-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Describeconstraints•Createandmaintainconstraints10-3Copyright©OracleCorporation,2001.Allrightsreserved.WhatareConstraints?•Constraintsenforcerulesatthetablelevel.•Constraintspreventthedeletionofatableiftherearedependencies.•Thefollowingconstrainttypesarevalid:–NOTNULL–UNIQUE–PRIMARYKEY–FOREIGNKEY–CHECK10-4Copyright©OracleCorporation,2001.Allrightsreserved.ConstraintGuidelines•NameaconstraintortheOracleservergeneratesanamebyusingtheSYS_Cnformat.•Createaconstrainteither:–Atthesametimeasthetableiscreated,or–Afterthetablehasbeencreated•Defineaconstraintatthecolumnortablelevel.•Viewaconstraintinthedatadictionary.10-5Copyright©OracleCorporation,2001.Allrightsreserved.DefiningConstraintsCREATETABLE[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.DefiningConstraints•Columnconstraintlevel•Tableconstraintlevelcolumn[CONSTRAINTconstraint_name]constraint_type,column,...[CONSTRAINTconstraint_name]constraint_type(column,...),10-7Copyright©OracleCorporation,2001.Allrightsreserved.TheNOTNULLConstraintEnsuresthatnullvaluesarenotpermittedforthecolumn:NOTNULLconstraint(Norowcancontainanullvalueforthiscolumn.)AbsenceofNOTNULLconstraint(Anyrowcancontainnullforthiscolumn.)NOTNULLconstraint…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,...TheNOTNULLConstraintIsdefinedatthecolumnlevel:SystemnamedUsernamed10-9Copyright©OracleCorporation,2001.Allrightsreserved.TheUNIQUEConstraintEMPLOYEESUNIQUEconstraintINSERTINTONotallowed:alreadyexistsAllowed…10-10Copyright©OracleCorporation,2001.Allrightsreserved.TheUNIQUEConstraintDefinedateitherthetablelevelorthecolumnlevel: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.ThePRIMARYKEYConstraintDEPARTMENTSPRIMARYKEYINSERTINTONotallowed(Nullvalue)Notallowed(50alreadyexists)…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));ThePRIMARYKEYConstraintDefinedateitherthetablelevelorthecolumnlevel:10-13Copyright©OracleCorporation,2001.Allrightsreserved.TheFOREIGNKEYConstraintDEPARTMENTSEMPLOYEESFOREIGNKEYINSERTINTONotallowed(9doesnotexist)AllowedPRIMARYKEY……10-14Copyright©OracleCorporation,2001.Allrightsreserved.TheFOREIGNKEYConstraintDefinedateitherthetablelevelorthecolumnlevel: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.FOREIGNKEYConstraintKeywords•FOREIGNKEY:Definesthecolumninthechildtableatthetableconstraintlevel•REFERENCES:Identifiesthetableandcolumnintheparenttable•ONDELETECASCADE:Deletesthedependentrowsinthechildtablewhenarowintheparenttableisdeleted.•ONDELETESETNULL:Convertsdependentforeignkeyvaluestonull10-16Copyright©OracleCorporation,2001.Allrightsreserved.TheCHECKConstraint•Definesaconditionthateachrowmustsatisfy•Thefollowingexpressionsarenotallowed:–ReferencestoCURRVAL,NEXTVAL,LEVEL,andROWNUMpseudocolumns–CallstoSYSDATE,UID,USER,andUSERENVfunctions–Queriesthatrefertoothervaluesinotherrows...,salaryNUMBER(2)CONSTRAINTemp_salary_minCHECK(salary0),...10-17Copyright©OracleCorporation,2001.Allrightsreserved.AddingaConstraintSyntaxUsetheALTERTABLEstatementto:•Addordropaconstraint,butnotmodifyitsstructure•Enableordisableconstraints•AddaNOTNULLconstraintbyusingtheMODIFYclauseALTERTABLEtableADD[CONSTRAINTconstraint]type(column);10-18Copyright©OracleCorporation,2001.Allrightsreserved.AddingaConstraintAddaFOREIGNKEYconstrainttotheEMPLOYEEStableindicatingthatamanagermustalreadyexistasavalidemployeeintheEMPLOYEEStable.ALTERTABLEemployeesADDCONSTRAINTemp_manager_fkFOREIGNKEY(manager_id)REFERENCESemployees(employee_id);Tablealtered.10-19Copyright©OracleCorporation,2001.Allrightsreserved.DroppingaConstraint•RemovethemanagerconstraintfromtheEMPLOYEEStable.•RemovethePRIMARYKEYconstraintontheDEPARTMENTStableanddroptheassociatedFOREIGNKEYconstraintontheEMPLOYEES.DEPARTMENT_IDcolumn.ALTERTABLEemployeesDROPCONSTRAINTemp_m