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';
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
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' ;
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 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';
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.
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