Chapter Three. Declarative Constraints


3.1 PRIMARY KEY

3.1.1 Creating the Constraint

3.1.2 Naming the Constraint

3.1.3 The Primary Key Index

3.1.4 Sequences

3.1.5 Sequences in Code

3.1.6 Concatenated Primary Key

3.1.7 Extra Indexes with Pseudo Keys

3.1.8 Enable, Disable, and Drop

3.1.9 Deferrable Option

3.1.10 NOVALIDATE

3.1.11 Error Handling in PL/SQL

3.2 UNIQUE

3.2.1 Combining NOT NULL, CHECK with UNIQUE Constraints

3.2.2 Students Table Example

3.2.3 Deferrable and NOVALIDATE Options

3.2.4 Error Handling in PL/SQL

3.3 FOREIGN KEY

3.3.1 Four Types of Errors

3.3.2 Delete Cascade

3.3.3 Mandatory Foreign Key Columns

3.3.4 Referencing the Parent Syntax

3.3.5 Referential Integrity across Schemas and Databases

3.3.6 Multiple Parents and DDL Migration

3.3.7 Many-to-Many Relationships

3.3.8 Self-Referential Integrity

3.3.9 PL/SQL Error Handling with Parent/Child Tables

3.3.10 The Deferrable Option

3.4 CHECK

3.4.1 Multicolumn Constraint

3.4.2 Supplementing Unique Constraints

3.4.3 Students Table Example

3.4.4 Lookup Tables versus Check Constraints

3.4.5 Cardinality

3.4.6 Designing for Check Constraints

3.5 NOT NULL CONSTRAINTS

3.6 DEFAULT VALUES

3.7 MODIFYING CONSTRAINTS

3.8 EXCEPTION HANDLING

3.9 DATA LOADS

This is the first of several chapters that cover enforcing business rules with constraints. Declarative constraints provide a core and traditional strategy for business rule enforcement. This chapter explains the how and why of declarative constraints. Chapter 4 illustrates the use of declarative constraints and includes a sample student's data model. Chapter 5 demonstrates how to extract declarative constraint definitions from the database data dictionary. Chapter 6 begins a series of chapters on triggers that enforce constraints procedurally.

This chapter makes frequent reference to a STUDENTS table. The STUDENTS table is part of the sample data model described in Chapter 4. The model description includes an entity relationship diagram, DDL, sample data, plus a description of the business rules enforced.

Oracle supports the following SQL constraints:

  • Primary Key constraint

  • Unique constraint

  • Foreign Key constraint

  • Check constraint

  • Not Null constraint, which is really a special case of a Check constraint



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net