Chapter 9 Constraints

1 / 22

# Chapter 9 Constraints - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Chapter 9 Constraints' - zasha

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Chapter 9Constraints

Constraints
• Rules used to enforce business rules, practices, and policies
• Rules used to ensure accuracy and integrity of data
Creating Constraints
• When:
• During table creation
• Modify existing table
• How:
• Column level approach
• Table level approach
General Syntax – Column Level

If a constraint is being created at the column level, the constraint applies to the column specified

General Syntax – Table Level
• Approach can be used to create any constraint type except NOT NULL
• Required if constraint is based on multiple columns
Enforcement
• All constraints are enforced at the table level
• If a data value violates a constraint, the entire row is rejected
• Added to existing table with ALTER TABLE command
• Add NOT NULL constraint using MODIFY clause
PRIMARY KEY Constraint
• Ensures that columns do not contain duplicate or NULL values
• Only one per table allowed
PRIMARY KEY Constraint for Composite Key

List column names within parentheses separated by commas

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
UNIQUE Constraint
• No duplicates allowed in referenced column
• NULL values are permitted
CHECK Constraint

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
Viewing Constraints – USER_CONSTRAINTS

Can display name, type, and condition of CHECK constraints

Disabling/Enabling Constraints

Use DISABLE or ENABLE clause of ALTER TABLE command

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