Chapter 9 Constraints

1. Chapter 9Constraints

2. Constraints • Rules used to enforce business rules, practices, and policies • Rules used to ensure accuracy and integrity of data

3. Constraint Types

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

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

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

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

8. 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

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

10. PRIMARY KEY Constraint for Composite Key List column names within parentheses separated by commas

11. 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

12. FOREIGN KEY Constraint - Example

13. UNIQUE Constraint • No duplicates allowed in referenced column • NULL values are permitted

14. CHECK Constraint Updates and additions must meet specified condition

15. 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

16. NOT NULL Constraint Example

17. Adding Constraints During Table Creation – Column Level Include in column definition

18. Adding Constraints During Table Creation – Table Level Include at end of column list

19. Viewing Constraints – USER_CONSTRAINTS Can display name, type, and condition of CHECK constraints

20. Disabling/Enabling Constraints Use DISABLE or ENABLE clause of ALTER TABLE command

21. 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

22. ALTER TABLE…DROP Syntax