Chapter 4: Constraints
Multiple Constraints on a Single Column
A column may be included in multiple constraints. The order# column is included in a primary key and a foreign key constraint
PRIMARY KEY
Determines which column(s) uniquely identifies each record. The primary key cannot be NULL and the data values must be unique
Viewing Constraints - USER_CONS_COLUMNS
Display constraint listing by column
Viewing Constraints - USER_CONSTRAINTS
Display constraint listing for a specific table
NOT NULL
Ensures that a specified column cant contain a NULL value. It can only be created with the column-lvl approach to table creation
CHECK
Ensures that a specified condition is true before the data value is added to a table (Ex. An orders ship date can't be earlier than its order date)
UNIQUE
Ensures that all data values stored in a specified column are unique. It differs from PRIMARY KEY because it allows NULL values
Using PRIMARY KEY Constraint
Ensures that columns do not contain duplicate or NULL values. Only one per table is allowed
Creating Constraints at Column Level
If a constraint is being created at the column level, the constraint applies to the column specified
FOREIGN KEY
In a one-to-many or parent-child relationship, the constraint is added to the "many" table. The constraint ensures that if a value is entered in a specified column, it must already exist in the "one" table or the record isn't added
Including Constraints during Table Creation - Table Level
Include at end of column list
Including Constraints during Table Creation - Column Level
Include in column definition
Primary Key Constraint for Composite Key
List column names within parentheses separated by commas
Using the UNIQUE Constraint
No duplicates are allowed in the referenced column. NULL values are permitted
Constraint Types
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL
Using the FOREIGN KEY Constraint
Requires a value to exist in the referenced column of another table. NULL values are allowed. Enforces referential integrity. Maps to the PRIMARY KEY in parent table
Constaints
Rules that are applied to data being added to a table. The constraint represents business rules, policies, and/or procedures used to ensure accuracy and integrity of data. Data violating the constraint is not added to the table
Using the CHECK Constraint
Updates and additions must meet specified condition
Using DISABLE/ENABLE
Use DISABLE or ENABLE clause of ALTER TABLE command
Creating Constaints
Use the optional CONSTRAINT keyword during creation to assign a name. Let the server name the constraint using default format SYS_Cn. Informative names can assist in debugging.
Creating Constraints (Continued)
When: -During table creation (as part of CREATE TABLE command) -After table creation, by modifying the existing table (using the ALTER TABLE command) How: -Column level approach -Table level approach
Enforcement of Constraints
All constraints are enforced at the table level. If a data value violates a constraint the entire row is rejected
Creating Constraints at the Table Level
Approach can be used to create any constraint type except NOT NULL. Required if constraint is based on multiple columns.
Adding Constraints to Existing Tables
Constraints are added to an existing table with the ALTER TABLE command. Add a NOT NULL constraint using MODIFY clause. All other constraints are added using ADD clause
Dropping Constraints
Constraints cannot be modified; they 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
Using the NOT NULL Constraint
The NOT NULL constraint is a 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 an existing table using ALTER TABLE...MODIFY command
Deletion of Foreign Key Values
You cannot delte a value in a parent table referenced by a row in a child table. Use ON DELETE CASCADE keywords when creating FOREIGN KEY constraint - it automatically deletes a parent row when the row in a child table is created.
