Oracle SQL Constraints: Complete Command Collection for Adding, Dropping, and Checking | Error Handling

“Oracle Constraints” are a mechanism to protect data integrity and quality. This article explains how to add, drop, and check constraints—operations frequently performed in development and operational environments—using SQL commands that can be used on actual machines.

If you are struggling with “forgetting constraint names” or “not understanding the cause of ORA errors,” this guide will help you master the correct design and implementation procedures. With this single article, you can cover everything from the basics to the applications of constraint operations, assuming Oracle Database 19c.

Conclusion: List of Frequently Used Constraint Operation Commands (Cheat Sheet)
What are Oracle Constraints? Mechanism and Importance
1. 5 Major Constraints
[Practical Example] SQL for Creating Tables with Constraints and Verification
1. 1. Create Parent Table (Primary Key)
2. 2. Create Child Table (Foreign Key, CHECK, DEFAULT, UNIQUE)
[Implementation] Commands to Add, Drop, and Enable Constraints
[Check] SQL to Investigate Configured Constraints
Common ORA Errors and Solutions (Troubleshooting)
Operational Precautions and Best Practices
Oracle Constraints FAQ
Summary

Conclusion: List of Frequently Used Constraint Operation Commands (Cheat Sheet)

First, here is a list of syntax for major commands that can be used immediately in the field. Please use this as a snippet.

Defined during table creation

CREATE TABLE table_name (..., col_name data_type CONSTRAINT const_name PRIMARY KEY, ...);

Add to an existing table

ALTER TABLE table_name ADD CONSTRAINT const_name CONSTRAINT_TYPE (column_name);

Add NOT NULL constraint (Use MODIFY)

ALTER TABLE table_name MODIFY (column_name CONSTRAINT const_name NOT NULL);

Drop a constraint

ALTER TABLE table_name DROP CONSTRAINT const_name;

Temporarily disable a constraint (Used during bulk data loading, etc.)

ALTER TABLE table_name DISABLE CONSTRAINT const_name;

Enable a constraint

ALTER TABLE table_name ENABLE CONSTRAINT const_name;

What are Oracle Constraints? Mechanism and Importance

Constraints are a mechanism where the Oracle Database forcibly checks data to ensure it does not violate business rules (e.g., IDs must not duplicate, prices must be positive numbers).

Data Integrity Assurance: Prevents the intrusion of data errors caused by application bugs or manual mistakes.
Performance Improvement: The optimizer may use constraint information to create more efficient execution plans (e.g., access paths to Primary Keys).

5 Major Constraints

The following table compares the five types of constraints frequently used in Oracle.

Constraint Type	Common Name	Overview / Role	NULL Handling
PRIMARY KEY	Primary Key	Uniquely identifies a row. Only one can be set per table.	Not Allowed
UNIQUE	Unique Constraint	Prohibits duplicate values. Multiple NULLs can be registered (*).	Allowed
NOT NULL	NOT NULL Constraint	Guarantees that a value is mandatory.	Not Allowed
FOREIGN KEY	Foreign Key	Refers to values in a parent table to maintain consistency (Referential Integrity).	Allowed
CHECK	Check Constraint	Verifies if a value satisfies a specific condition (e.g., `price > 0`).	Allowed

Note: In a UNIQUE constraint, if all columns are NULL, it is not considered a duplicate. However, due to Oracle specifications, handling NULLs requires caution.

[Practical Example] SQL for Creating Tables with Constraints and Verification

Here is a complete SQL example that creates two tables with a parent-child relationship (dept_demo and emp_demo) and implements various constraints.

Prerequisites:

You must have CREATE TABLE privileges on the target schema.
The SQL does not include multi-byte characters (full-width characters) to prevent environment-dependent errors.

1. Create Parent Table (Primary Key)

First, create the department table, which will be the referenced side.

-- Create parent table: dept_demo
CREATE TABLE dept_demo (
    dept_id   NUMBER(4),
    dept_name VARCHAR2(50) CONSTRAINT nn_dept_name NOT NULL,
    loc       VARCHAR2(50),
    CONSTRAINT pk_dept_demo PRIMARY KEY (dept_id)
);

2. Create Child Table (Foreign Key, CHECK, DEFAULT, UNIQUE)

Next, create the employee table, which will be the referencing side.

-- Create child table: emp_demo
CREATE TABLE emp_demo (
    emp_id     NUMBER(6),
    emp_name   VARCHAR2(50) CONSTRAINT nn_emp_name NOT NULL,
    email      VARCHAR2(100),
    salary     NUMBER(8, 2),
    dept_id    NUMBER(4),
    hire_date  DATE DEFAULT SYSDATE,
    -- Define Constraints
    CONSTRAINT pk_emp_demo PRIMARY KEY (emp_id),
    CONSTRAINT uq_emp_email UNIQUE (email),
    CONSTRAINT chk_emp_salary CHECK (salary > 0),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
        REFERENCES dept_demo (dept_id)
);

Explanation:

DEFAULT SYSDATE: Automatically inputs the current date and time if no date is specified during data insertion (strictly speaking, this is not a constraint, but it is a feature often used in conjunction).
CONSTRAINT constraint_name: It is strongly recommended to explicitly name constraints according to a naming convention (e.g., pk_table_name, fk_child_parent). If omitted, system-generated names (such as SYS_C0012345) will be assigned, making management difficult.

[Implementation] Commands to Add, Drop, and Enable Constraints

These are the procedures for manipulating constraints when requirements change after table creation.

1. Add Constraint (ADD / MODIFY)

If you want to add a “salary cap check” after the table has been created:

-- Add CHECK constraint
ALTER TABLE emp_demo ADD CONSTRAINT chk_salary_max CHECK (salary <= 1000000);

-- Add NOT NULL constraint (Modify syntax)
ALTER TABLE emp_demo 
MODIFY (hire_date CONSTRAINT nn_hire_date NOT NULL);

2. Drop Constraint (DROP)

Remove constraints that are no longer needed.

-- Drop Unique constraint
ALTER TABLE emp_demo DROP CONSTRAINT uq_emp_email;

Warning: When a PRIMARY KEY or UNIQUE constraint is dropped, the index created corresponding to it may also be dropped at the same time. Checking the impact scope is mandatory in operational systems.

3. Disable / Enable Constraints (DISABLE / ENABLE)

Use this when you want to temporarily stop constraint checks (e.g., prioritizing performance during bulk data loading). Unlike dropping, the definition remains.

-- Disable constraint
ALTER TABLE emp_demo DISABLE CONSTRAINT fk_emp_dept;

-- Enable constraint (Validates existing data)
ALTER TABLE emp_demo ENABLE CONSTRAINT fk_emp_dept;

When executing ENABLE, if existing data violates the constraint, an error occurs, and it cannot be enabled. In that case, consider using the ENABLE NOVALIDATE option.

[Check] SQL to Investigate Configured Constraints

To check the configured constraints, it is reliable to join and query the data dictionary views USER_CONSTRAINTS and USER_CONS_COLUMNS.

The following SQL retrieves a list of constraints for a specified table and the columns associated with them.

-- Check constraints and columns
SELECT 
    uc.constraint_name,
    uc.constraint_type,
    ucc.column_name,
    uc.search_condition,
    uc.status
FROM 
    user_constraints uc
JOIN 
    user_cons_columns ucc 
    ON uc.constraint_name = ucc.constraint_name
WHERE 
    uc.table_name = 'EMP_DEMO'
ORDER BY 
    uc.constraint_type, uc.constraint_name;

How to read CONSTRAINT_TYPE:

P: Primary Key
R: Referential Integrity (Foreign Key)
U: Unique Key
C: Check (NOT NULL is also included here)

SQL> SELECT
  2      uc.constraint_name,
  3      uc.constraint_type,
  4      ucc.column_name,
  5      uc.search_condition,
  6      uc.status
  7  FROM
  8      user_constraints uc
  9  JOIN
 10      user_cons_columns ucc
 11      ON uc.constraint_name = ucc.constraint_name
 12  WHERE
 13      uc.table_name = 'EMP_DEMO'
 14  ORDER BY
 15      uc.constraint_type, uc.constraint_name;

CONSTRAINT_NAME      C COLUMN_NAME       SEARCH_CONDITION             STATUS
-------------------- - ----------------- ---------------------------- --------
CHK_EMP_SALARY       C SALARY            salary > 0                   ENABLED
CHK_SALARY_MAX       C SALARY            salary <= 1000000            ENABLED
NN_EMP_NAME          C EMP_NAME          "EMP_NAME" IS NOT NULL       ENABLED
NN_HIRE_DATE         C HIRE_DATE         "HIRE_DATE" IS NOT NULL      ENABLED
PK_EMP_DEMO          P EMP_ID                                         ENABLED
FK_EMP_DEPT          R DEPT_ID                                        ENABLED

Common ORA Errors and Solutions (Troubleshooting)

These are frequently occurring errors related to constraints and their solutions.

Error Code	Cause	Solution
ORA-00001	Unique constraint violation Attempted to insert a duplicate value into a PK or UNIQUE column.	Check the value of the insertion data or correct/delete the original duplicate data.
ORA-01400	NULL insertion not allowed Attempted to insert NULL into a NOT NULL column.	Specify a value in the INSERT statement or set a DEFAULT value.
ORA-02291	Integrity constraint violation (parent key not found) Specified an ID not present in the parent table during INSERT into the child table.	Check if the corresponding ID exists in the parent table (`dept_demo`) and register the parent data first.
ORA-02292	Integrity constraint violation (child record found) Referenced by the child table during DELETE from the parent table.	Delete the related records in the child table (`emp_demo`) first, or set `ON DELETE CASCADE` on the FK.
ORA-02290	Check constraint violation Attempted to insert a value that does not satisfy the CHECK condition.	Confirm that the input value meets the condition expression (e.g., `salary > 0`).

Operational Precautions and Best Practices

Standardize Constraint Naming Conventions

When an error occurs, if ORA-00001: unique constraint (SCOTT.SYS_C00123) violated is displayed, you cannot immediately tell which column is the cause. Use explicit names like PK_EMP or UQ_EMP_EMAIL.

Create Indexes on Foreign Keys (FK)

When updating or deleting the primary key of a parent table, if there is no index on the foreign key column of the child table, a table lock may occur on the entire child table. From the perspective of deadlock prevention and performance, creating indexes on foreign key columns is recommended.

Avoid Excessive Constraints

While data quality is important, overly complex CHECK constraints should sometimes be absorbed by the application logic. Consider the balance with load performance on the DB.

Oracle Constraints FAQ

Q1. Can I add a NOT NULL constraint to a column later?

A. Yes, it is possible. However, if that column already contains NULL data, an error (ORA-02296) will occur. Please update (UPDATE) the NULL data before adding the constraint.

Q2. What is ON DELETE CASCADE in a foreign key constraint?

A. It is an option where if a parent record (e.g., Department) is deleted, the child records linked to it (e.g., Employees in that department) are automatically deleted as well. There is also ON DELETE SET NULL (sets child record values to NULL). While convenient, it carries the risk of unintentionally deleting large amounts of data, so caution is required when using it.

Q3. What is the difference between a Primary Key and a Unique Constraint?

A. A Primary Key is a “row identifier,” does not allow NULLs, and only one can be created per table. A Unique Constraint is for “duplicate elimination,” allows NULLs, and multiple can be created per table.

Summary

Constraints in the Oracle Database are the foundation for building a robust system.

Design: Appropriately select from the 5 constraints (PK, FK, UNIQUE, NOT NULL, CHECK).
Implementation: Consider maintainability and always name constraints according to naming conventions.
Operation: Check settings with USER_CONSTRAINTS and compare with actual data when ORA errors occur.

Aim for a highly reliable database design by utilizing the correct commands and knowledge.

This article explains concepts targeting Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Oracle Database Database Administrator’s Guide, 19c