Chapter 9 Constraints

1. Chapter 9Constraints Oracle9i: SQL

2. Chapter Objectives • Explain the purpose of constraints in a table • Distinguish among PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraints and the appropriate use for each constraint • Distinguish between creating constraints at the column level and table level Oracle9i: SQL

3. Chapter Objectives • Create PRIMARY KEY constraints for a single column and a composite primary key • Create a FOREIGN KEY constraint • Create a UNIQUE constraint • Create a CHECK constraint Oracle9i: SQL

4. Chapter Objectives • Create a NOT NULL constraint, using the ALTER TABLE…MODIFY command • Include constraints during table creation • Use DISABLE and ENABLE commands • Use the DROP command Oracle9i: SQL

5. Constraints • Rules used to enforce business rules, practices, and policies • Rules used to ensure accuracy and integrity of data Oracle9i: SQL

6. Constraint Types Oracle9i: SQL

7. Naming Constraints • Use optional CONSTRAINT keyword during creation to assign a name • Let server name constraint using default format SYS_Cn Oracle9i: SQL

8. Creating Constraints • When: • During table creation • Modify existing table • How: • Column level approach • Table level approach Oracle9i: SQL

9. General Syntax – Column Level If a constraint is being created at the column level, the constraint applies to the column specified Oracle9i: SQL

10. General Syntax – Table Level • Approach can be used to create any constraint type except NOT NULL • Required if constraint is based on multiple columns Oracle9i: SQL

11. Enforcement • All constraints are enforced at the table level • If a data value violates a constraint, the entire row is rejected Oracle9i: SQL

12. Adding Constraints to Existing Tables • Added to existing table with ALTER TABLE command • Add NOT NULL constraint using MODIFY clause • All other constraints added using ADD clause Oracle9i: SQL

13. PRIMARY KEY Constraint • Ensures that columns do not contain duplicate or NULL values • Only one per table allowed Oracle9i: SQL

14. PRIMARY KEY Constraint for Composite Key List column names within parentheses separated by commas Oracle9i: SQL

15. FOREIGN KEY Constraint • Requires a value to exist in referenced column of other table • NULL values are allowed • Enforces referential integrity • Maps to the PRIMARY KEY in parent table Oracle9i: SQL

16. FOREIGN KEY Constraint - Example Oracle9i: SQL

17. Deletion of Foreign Key Values • Cannot delete a value in parent table referenced by a row in child table • Use ON DELETE CASCADE keywords when creating FOREIGN KEY constraint – automatically deletes parent row when row in child table is deleted Oracle9i: SQL

18. UNIQUE Constraint • No duplicates allowed in referenced column • NULL values are permitted Oracle9i: SQL

19. CHECK Constraint Updates and additions must meet specified condition Oracle9i: SQL

20. NOT NULL Constraint • Special CHECK constraint with IS NOT NULL condition • Can only be created at column level • Included in output of DESCRIBE command • Can only be added to existing table using ALTER TABLE…MODIFY command Oracle9i: SQL

21. NOT NULL Constraint Example Oracle9i: SQL

22. Adding Constraints During Table Creation – Column Level Include in column definition Oracle9i: SQL

23. Adding Constraints During Table Creation – Table Level Include at end of column list Oracle9i: SQL

24. Viewing Constraints – USER_CONSTRAINTS Can display name, type, and condition of CHECK constraints Oracle9i: SQL

25. Disabling/Enabling Constraints Use DISABLE or ENABLE clause of ALTER TABLE command Oracle9i: SQL

26. Dropping a Constraint • Constraints cannot be modified, must be dropped and recreated • Actual syntax depends on type of constraint • PRIMARY KEY - just list type of constraint • UNIQUE - include column name • All others - reference constraint name Oracle9i: SQL

27. ALTER TABLE…DROP Syntax Oracle9i: SQL