1 / 22

Chapter 9 Constraints

Chapter 9 Constraints. Constraints. Rules used to enforce business rules, practices, and policies Rules used to ensure accuracy and integrity of data. Constraint Types. Creating Constraints. When: During table creation Modify existing table How: Column level approach

zasha
Télécharger la présentation

Chapter 9 Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related