Oracle Database Constraints Tutorial: Create, Manage & Query Constraints

Oracle SQL | Constraint Management

Constraint Types Overview
Creating Constraints
1. During Table Creation (CREATE TABLE)
2. After Table Creation (ALTER TABLE)
Viewing Constraint Information
Managing Constraints

Constraint Types Overview
Oracle Database supports several types of constraints to enforce data integrity. Each constraint type serves a specific purpose and has a corresponding constraint type code in the data dictionary:
- PRIMARY KEY (constraint type: P) - Ensures each row is uniquely identifiable. Automatically creates a unique index and enforces NOT NULL on all key columns.
- FOREIGN KEY (constraint type: R) - Maintains referential integrity between tables by ensuring values match those in the referenced table's primary or unique key.
- UNIQUE (constraint type: U) - Prevents duplicate values in specified columns while allowing NULL values. Automatically creates a unique index.
- CHECK (constraint type: C) - Validates data based on specified conditions. Also includes NOT NULL constraints which are implemented as CHECK constraints internally.

Creating Constraints

Constraints can be defined at two different times: during table creation or after the table already exists. Naming constraints explicitly is recommended for easier maintenance and troubleshooting.

During Table Creation (CREATE TABLE)

Define constraints inline with column definitions or as table-level constraints:

CREATE TABLE table_constraint_1
(
    id NUMBER(1,0) NOT NULL -- NOT NULL
,   CONSTRAINT cp_table_constraint_1_id PRIMARY KEY (id) -- PRIMARY KEY
,   CONSTRAINT cc_table_constraint_1_id CHECK (id > 0 AND id < 100) -- CHECK
);

CREATE TABLE table_constraint_2
(
    id NUMBER(1,0)
,   CONSTRAINT cr_table_constraint_2_id FOREIGN KEY (id) REFERENCES table_constraint_1 (id) -- FOREIGN KEY
,   CONSTRAINT cu_table_constraint_2_id UNIQUE (id) -- UNIQUE
);

After Table Creation (ALTER TABLE)
Add constraints to existing tables using ALTER TABLE with the ADD CONSTRAINT clause. This is useful when modifying existing database schemas or adding business rules later:
```
ALTER TABLE table_constraint_2 ADD CONSTRAINT cc_table_constraint_2_id CHECK (id IS NOT NULL); -- CHECK
```

Viewing Constraint Information

Use the USER_CONSTRAINTS data dictionary view to retrieve information about constraints. Key columns include CONSTRAINT_NAME, CONSTRAINT_TYPE, GENERATED (shows if name was user-defined or system-generated), SEARCH_CONDITION (for CHECK constraints), and INDEX_NAME (for constraints with associated indexes).

Display constraints for the table "table_constraint_1":

SELECT constraint_name, constraint_type, generated, search_condition, index_name
FROM user_constraints
WHERE table_name = 'TABLE_CONSTRAINT_1';

Query result:

+--------------------------+-----------------+----------------+---------------------+--------------------------+
| CONSTRAINT_NAME          | CONSTRAINT_TYPE | GENERATED      | SEARCH_CONDITION    | INDEX_NAME               |
+--------------------------+-----------------+----------------+---------------------+--------------------------+
| SYS_C007078              | C               | GENERATED NAME | "ID" IS NOT NULL    | (null)                   |
| CC_TABLE_CONSTRAINT_1_ID | C               | USER NAME      | ID > 0 AND ID < 100 | (null)                   |
| CP_TABLE_CONSTRAINT_1_ID | P               | USER NAME      | (null)              | CP_TABLE_CONSTRAINT_1_ID |
+--------------------------+-----------------+----------------+---------------------+--------------------------+

Note: The SYS_C007078 constraint was automatically created for the NOT NULL declaration. Oracle generates system names starting with "SYS_C" when constraints are not explicitly named.

Display constraints for the table "table_constraint_2":

SELECT constraint_name, constraint_type, generated, search_condition, index_name
FROM user_constraints
WHERE table_name = 'TABLE_CONSTRAINT_2';

Query result:

+--------------------------+-----------------+----------------+---------------------+--------------------------+
| CONSTRAINT_NAME          | CONSTRAINT_TYPE | GENERATED      | SEARCH_CONDITION    | INDEX_NAME               |
+--------------------------+-----------------+----------------+---------------------+--------------------------+
| CU_TABLE_CONSTRAINT_2_ID | U               | USER NAME      | (null)              | CU_TABLE_CONSTRAINT_2_ID |
| CR_TABLE_CONSTRAINT_2_ID | R               | USER NAME      | (null)              | (null)                   |
| CC_TABLE_CONSTRAINT_2_ID | C               | USER NAME      | ID IS NOT NULL      | (null)                   |
+--------------------------+-----------------+----------------+---------------------+--------------------------+

Additional Views: Use USER_CONS_COLUMNS to see which columns are involved in each constraint, and ALL_CONSTRAINTS or DBA_CONSTRAINTS to view constraints across schemas.

Managing Constraints
Oracle provides several operations to manage existing constraints without recreating tables. These operations are useful for maintenance, data loading, and troubleshooting scenarios.
1. Disabling Constraints
  Temporarily disable constraint checking while keeping the constraint definition. This is useful during bulk data loads or when you need to perform operations that would temporarily violate constraints:
```
ALTER TABLE table_constraint_2 DISABLE CONSTRAINT cc_table_constraint_2_id;
```
  Important: Disabling a PRIMARY KEY or UNIQUE constraint also drops the associated index. Use DISABLE NOVALIDATE to keep the index while disabling constraint enforcement.
2. Enabling Constraints
  Re-enable previously disabled constraints. Oracle will validate all existing data against the constraint when it's enabled:
```
ALTER TABLE table_constraint_2 ENABLE CONSTRAINT cc_table_constraint_2_id;
```
  Note: If existing data violates the constraint, the ENABLE operation will fail. Use ENABLE NOVALIDATE to enable the constraint without checking existing data, or fix the data first.
3. Dropping Constraints
  Permanently remove a constraint from the table. This action cannot be undone and will also drop any associated indexes for PRIMARY KEY and UNIQUE constraints:
```
ALTER TABLE table_constraint_2 DROP CONSTRAINT cc_table_constraint_2_id;
```
  Note: When dropping PRIMARY KEY constraints referenced by foreign keys, use CASCADE to automatically drop dependent foreign key constraints, or drop the foreign keys first.