chapter 9 constraints n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 9 Constraints PowerPoint Presentation
Download Presentation
Chapter 9 Constraints

Loading in 2 Seconds...

play fullscreen
1 / 22

Chapter 9 Constraints - PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on

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

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

PowerPoint Slideshow about 'Chapter 9 Constraints' - zasha


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.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
constraints
Constraints
  • Rules used to enforce business rules, practices, and policies
  • Rules used to ensure accuracy and integrity of data
creating constraints
Creating Constraints
  • When:
    • During table creation
    • Modify existing table
  • How:
    • Column level approach
    • Table level approach
general syntax column level
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
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
Enforcement
  • All constraints are enforced at the table level
  • If a data value violates a constraint, the entire row is rejected
adding constraints to existing tables
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
primary key constraint
PRIMARY KEY Constraint
  • Ensures that columns do not contain duplicate or NULL values
  • Only one per table allowed
primary key constraint for composite key
PRIMARY KEY Constraint for Composite Key

List column names within parentheses separated by commas

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

Updates and additions must meet specified condition

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

Can display name, type, and condition of CHECK constraints

disabling enabling constraints
Disabling/Enabling Constraints

Use DISABLE or ENABLE clause of ALTER TABLE command

dropping a constraint
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