SQL Application Development in CA IDMS › Accessing Data Using SQL › Integrity Constraints

Integrity Constraints

Integrity rules are enforced by the DBMS using constraints that are specified as part of the database definition.

Unique Constraint

A unique constraint requires that each row of a table be unique with respect to the value of a column or combination of columns. A unique constraint is defined when an index or CALC key is defined with the UNIQUE parameter.

It is possible to define any number of unique constraints on a table.

Primary Key

The primary key is a column or combination of columns for which a unique constraint has been defined and which has been defined as not null. Consequently, the primary key uniquely identifies each row and prevents duplicate rows from being stored. For example, in the DEPARTMENT table of the demonstration database, DEPT_ID is the primary key.

A table usually has one and only one primary key.

Referential Constraint

A referential constraint is a relationship between two tables. A referential constraint identifies a foreign key in one of the tables, the referencing table. A foreign key is a column or combination of columns whose value must exist as the value of the primary key in a row of the related table, the referenced table.

When a referential constraint has been created, a row cannot be stored in the referencing table unless its foreign key value already exists as a primary key in the referenced table. Conversely, a row in the referenced table cannot be deleted or have its primary key value altered if the primary key value exists as a foreign key in the referencing table. This assures referential integrity between the tables.

Referential Constraint Illustration

The following example identifies two referential constraints between the DEPARTMENT table and the EMPLOYEE table:

1. A value cannot be stored in the DEPT_ID column of the EMPLOYEE table unless the value exists in the DEPT_ID column of the DEPARTMENT table
2. A value cannot be stored in the DEPT_HEAD_ID column of the DEPARTMENT table unless the value exists in the EMP_ID column of the EMPLOYEE table

    DEPARTMENT table

    ┌───────────┬─────────────────────────────┬─────────────┬──────────────┐
    │DEPT_ID    │DEPT_NAME                    │DIV_CODE     │DEPT_HEAD_ID  │
    ├───────────┼─────────────────────────────┼─────────────┼──────────────┤
    │       3510│APPRAISAL - USED CARS        │D02          │          3082│
    │       4500│HUMAN RESOURCES              │D09          │          3222│
    │       2210│SALES - NEW CARS             │D04          │          2010│
    │       5000│CORPORATE ACCOUNTING         │D09          │          2466│
    │       3520│APPRAISAL NEW CARS           │D04          │          3769│
    │       4600│MAINTENANCE                  │D06          │          2096│
    │       4200│LEASING - NEW CARS           │D04          │          1003│
    │       5100│BILLING                      │D06          │          2598│
    │       6000│LEGAL                        │D09          │          1003│
    │       1100│PURCHASING - USED CARS       │D02          │          2246│ ──┐
    │       3530│APPRAISAL ─ SERVICE          │D06          │          2209│   │
    │       5200│CORPORATE MARKETING          │D09          │          2894│   │
┌───┼─────► 1110│PURCHASING - NEW CARS        │D04          │          1765│   │
│   │       3000│CUSTOMER SERVICE             │D09          │          4321│   │
│   │       6200│CORPORATE ADMINISTRATION     │D09          │          2461│   │
│   │       2200│SALES - USED CARS            │D02          │          2180│   │
│   │       1120│PURCHASING - SERVICE         │D06          │          2004│   │
│   │       4900│MIS                          │D09          │          2466│   │
│   └───────────┴─────────────────────────────┴─────────────┴──────────────┘   │
│                                                                              │
│ EMPLOYEE (DEPT_ID)                                                           │
│ references DEPARTMENT (DEPT_ID)                                              │
│                                                                              │
│                                                                              │
│          EMPLOYEE table                                                      │
│                                                                              │
│          ┌───────────┬────────────────────┬───────────┐     DEPARTMENT       │
│          │DEPT_ID    │EMP_LNAME           │EMP_ID     │     (DEPT_HEAD_ID)   │
│          ├───────────┼────────────────────┼───────────┤                      │
│          │       1100│FORDMAN             │       5008│     EMPLOYEE         │
│          │       1100│HALLORAN            │       4703│     (EMP_ID)         │
│          │       1100│HAMEL               │       2246│ ◄────────────────────┘
└──────────┼─────  1110│ALEXANDER           │       1765│
           │       1110│WIDMAN              │       2106│
           │       1120│JOHNSON             │       2004│
           │       1120│JOHNSON             │       3294│
           │       1120│UMIDY               │       2898│
           │       1120│WHITE               │       3338│
           │       2200│ALBERTINI           │       2180│
           │        .  │  .                 │        .  │
           │        .  │  .                 │        .  │
           │        .  │  .                 │        .  │
           └───────────┴────────────────────┴───────────┘

Domain Constraint

A domain constraint restricts column values and is part of the table definition. The types of domain constraint are:

• Data type—Restricts column values to the data type of the column (for example, INTEGER restricts column values to the set of integers)
• Check constraint—Restricts column values to a range of values that satisfies a search condition
• Not null constraint—Requires that each column of a row contain an actual value and not the absence of a value

Constraint Violation

If the DBMS detects a constraint violation when processing an SQL statement, it returns an error.