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.