Constraints on a table in oracle can be found using the below views
user_constraints
all_constraints
dba_constraints
User_cons_columns
all_cons_columns
dba_cons_columns
We can use the view according to the access we have in 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
Lets 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
SQL> col CONSTRAINT_NAME format a20 SQL> col INDEX_NAME format a20 SQL> col CONSTRAINT_TYPE format a5 SQL> column SEARCH_CONDITION format a30 SQL>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
SQL> column OWNER format a10 SQL> column CONSTRAINT_NAME format a120 SQL> column CONSTRAINT_NAME format a20 SQL> column COLUMN_NAME format a20 SQL> 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 used 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, 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 alter table command. we can drop primary, foreign key, check, not null and unique constraint with the same command
Coalesce Function in Oracle : Coalesce function in oracle will return the first expression if it is not null else it will do the 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 Reply