USER_CONSTRAINTS | Constraints in SQL

In SQL databases, the USER_CONSTRAINTS table is used (especially in Oracle) to retrieve information about the constraints defined on a user's tables. This table provides details about all constraints, including primary keys, foreign keys, unique constraints, check constraints, and more.

Columns in USER_CONSTRAINTS

The USER_CONSTRAINTS table includes the following important columns:

Query to Retrieve Constraint Details

To view constraints on a specific table, use the following query:

SELECT
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
STATUS,
SEARCH_CONDITION,
R_CONSTRAINT_NAME,
DELETE_RULE
FROM
USER_CONSTRAINTS
WHERE
TABLE_NAME = 'EMPLOYEES'; -- Replace with your table name

Constraint Types in USER_CONSTRAINTS

The CONSTRAINT_TYPE column provides the type of constraint:

P: Primary Key

R: Foreign Key

U: Unique Key

C: Check Constraint

Example

Assume we have a table EMPLOYEES with the following constraints:

1. A PRIMARY KEY on ID.

2. A FOREIGN KEY on DEPARTMENT_ID referencing DEPARTMENTS(ID).

3. A CHECK constraint ensuring AGE >= 18.

Query:

SELECT
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
STATUS
FROM
USER_CONSTRAINTS
WHERE
TABLE_NAME = 'EMPLOYEES';

Output: | CONSTRAINT_NAME | CONSTRAINT_TYPE | STATUS | |-----------------------|-----------------|----------| | EMP_PK | P | ENABLED | | EMP_DEPT_FK | R | ENABLED | | EMP_AGE_CHECK | C | ENABLED |