Home » Oracle » Oracle Sql » How to check all constraints on a table in Oracle

How to check all constraints on a table in Oracle

Constraints on a table in Oracle can be found using the below views
user_constraints: It provides information about constraints owned by the current user.
all_constraints: shows information about constraints accessible to the current user, including those owned by other users.
dba_constraints: provides information about all constraints in the database and requires DBA privileges.
User_cons_columns: it provides information about columns of constraints owned by the current user.
all_cons_columns: It shows information about the column of constraints accessible to the current user, including those owned by other users.
dba_cons_columns: provides information about the column of all constraints in the database and requires DBA privileges.

We can use the view according to the access we have to the Oracle database. Now I am going to explain how to check all constraints on a table in Oracle using the examples

How to find all constraints on a table in oracle

Let’s first create the Tables

SQL> CREATE TABLE "DEPT"
 ( "DEPTNO" NUMBER(2,0),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(13),
  CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
 )  ;
 Table created.
 SQL> CREATE TABLE "EMP"
     ( "EMPNO" NUMBER(4,0),
     "ENAME" VARCHAR2(10) Not null,
      "JOB" VARCHAR2(9) not null,
     "MGR" NUMBER(4,0),
     "HIREDATE" DATE,
     "SAL" NUMBER(7,2),
     "COMM" NUMBER(7,2),
     "DEPTNO" NUMBER(2,0),
      CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
      CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
      REFERENCES "DEPT" ("DEPTNO") ENABLE
     );
 Table created.

Now constraints can be found using the below Oracle select constraint queries

col CONSTRAINT_NAME  format a20
col INDEX_NAME format a20
col CONSTRAINT_TYPE format a5
column SEARCH_CONDITION format a30
column R_CONSTRAINT_NAME format a20
select CONSTRAINT_NAME  C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='&table_name';

Example
col CONSTRAINT_NAME  format a20
col INDEX_NAME format a20
col CONSTRAINT_TYPE format a5
column SEARCH_CONDITION format a30
column R_CONSTRAINT_NAME format a20
select CONSTRAINT_NAME  C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='EMP';
how to check all constraints on a table in oracle

Here CONSTRAINT_TYPE define the type of constraints
P stands for Oracle Primary Key
C stands for check constraints or not null constraints
R stands for Foreign Key constraints
U stands for Unique Key constraints

See also  Oracle virtual Private database(VPD)


Now Primary key, Foreign Key, and Unique key constraints columns can be found using the below query

column OWNER format a10
column CONSTRAINT_NAME format a120
column CONSTRAINT_NAME format a20
column COLUMN_NAME format a20
select OWNER,CONSTRAINT_NAME ,COLUMN_NAME,POSITION  from  User_cons_columns where TABLE_NAME='&table_name';

Example
column OWNER format a10
column CONSTRAINT_NAME format a120
column CONSTRAINT_NAME format a20
column COLUMN_NAME format a20
select OWNER,CONSTRAINT_NAME ,COLUMN_NAME,POSITION  from  User_cons_columns where TABLE_NAME='EMP';

We will get null values for the Position column if we have a single column in the constraint. If there are multiple columns present in the constraints, Position will specify the order of the column in the constraint

Similar Queries can be used for selecting constraints on DEPT Table

SQL> select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='DEPT';
SQL> select OWNER,CONSTRAINT_NAME ,COLUMN_NAME,POSITION  from  User_cons_columns where TABLE_NAME='DEPT';

How to find constraint by name in Oracle

select CONSTRAINT_NAME ,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where CONSTRAINT_NAME='&1'
or
select owner, CONSTRAINT_NAME ,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from all_constraints where CONSTRAINT_NAME='&1'

How to check the Referential integrity constraints in the table

SQL> select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE,Search_condition,R_CONSTRAINT_NAME R_NAME from user_constraints where TABLE_NAME='EMP' and CONSTRAINT_TYPE='R' ;
How to check the Referential integrity constraints in the table

The parent table being referenced can be found using the R_CONSTRAINT_NAME

 SQL>  select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE,table_name from user_constraints where CONSTRAINT_NAME='PK_DEPT';
How to check the Referential integrity constraints in the table

How to check the primary key in the table

SQL> select CONSTRAINT_NAME C_NAME,INDEX_NAME,CONSTRAINT_TYPE from user_constraints where TABLE_NAME='EMP' and CONSTRAINT_TYPE='P';
 C_NAME      INDEX_NAME           CONST 
 ----         -------             -----
 PK_EMP        PK_EMP               P

query to find unique constraints on a table in oracle

SQL> CREATE TABLE DEPT_MASTER (
     dept_nr NUMBER UNIQUE,
     dept_name varchar2(100) NOT NULL,
     dept_status NUMBER(1,0) NOT NULL,
       created_at date
         ); 
 Table created.
 SQL> select CONSTRAINT_NAME,INDEX_NAME,CONSTRAINT_TYPE from   user_constraints where TABLE_NAME='DEPT_MASTER' and CONSTRAINT_TYPE='U';
query to find unique constraints on a table in oracle

Other Queries for Constraints

select table_name
 from user_constraints
 where (r_constraint_name) in ( select constraint_name
 from user_constraints
 where table_name = 'T'
 and constraint_type in ( 'P', 'U' ) );

So, we can easily find all the constraints on the table in Oracle using data dictionary views. We can then take whatever action like modify, disable, or drop, we want to take on these constraints. Constraints are enforcing data integrity rules in the Oracle database and we must be careful while modifying/dropping them.

See also  What is voting disks in RAC

I hope you like this content on how to check all constraints on a table in an Oracle database. Please do provide feedback for this article.

Also Reads
Drop constraint in Oracle: We can drop the constraint in Oracle using the alter table command. we can drop primary, foreign key, check, not null and unique constraint with the same command
Coalesce Function in Oracle: The Coalesce function in Oracle will return the first expression if it is not null else it will coalesce the rest of the expression.
Supplemental Logging in Oracle: Supplemental Logging in Oracle is the additional column information required for reconstructing SQL to apply to any other database
Query to find object dependencies in Oracle: Check out for Query to find object dependencies in Oracle, child-level dependency, parent-level dependency, finding using dbms_utility.get_dependency
JSON in Oracle database: Check out this post on how to use JSON in Oracle, how to create a table containing JSON data, how to extract, insert the JSON data in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top