Display Names of All Constraints for a Table in Oracle SQL

Display names of all constraints for a table in Oracle SQL

You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints:

SELECT *
  FROM user_cons_columns
 WHERE table_name = '<your table name>';

FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.

If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS view:

SELECT *
  FROM user_constraints
 WHERE table_name = '<your table name>'
   AND constraint_name = '<your constraint name>';

If the table is held in a schema that is not your default schema then you might need to replace the views with:

all_cons_columns

and

all_constraints

adding to the where clause:

   AND owner = '<schema owner of the table>'

How to get list of all constraints present in a table in Oracle?

You can use the JOIN on two tables USER_CONSTRAINTS and USER_CONS_COLUMNS to fetch the Column Name, Constraint Type and Table Name.

SELECT ucc.COLUMN_NAME, uc.CONSTRAINT_TYPE ,uc.TABLE_NAME 
FROM USER_CONSTRAINTS uc  JOIN  USER_CONS_COLUMNS ucc 
ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME 
AND uc.TABLE_NAME=ucc.TABLE_NAME
WHERE uc.TABLE_NAME='mytablename';

Constraint Definition can be referred as:

C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)

For more information you can view Oracle Documentation here

Display all CONSTRAINT of table in SQL

For Oracle:

SELECT * 
FROM USER_CONSTRAINTS 
WHERE table_name = 'ACCOUNT';

---

For mySQL

You can use DESCRIBE ACCOUNTto view the table structure, included the constraints, or also SHOW CREATE TABLE ACCOUNT to see the creation code, which includes the constraints.

Another way is:

SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'ACCOUNT';

---

Oracle: get all constraints/columns (primary and foreign keys)

Join the constraint column view to the pk constraint view:

SELECT a.table_name
     , a.column_name
     , a.constraint_name
     , c.owner
     , c.r_owner
     , c_pk.table_name      r_table_name
     , c_pk.constraint_name r_pk
     , cc_pk.column_name    r_column_name
  FROM all_cons_columns a
  JOIN all_constraints  c       ON (a.owner                 = c.owner                   AND a.constraint_name   = c.constraint_name     )
  JOIN all_constraints  c_pk    ON (c.r_owner               = c_pk.owner                AND c.r_constraint_name = c_pk.constraint_name  )
  JOIN all_cons_columns cc_pk   on (cc_pk.constraint_name   = c_pk.constraint_name      AND cc_pk.owner         = c_pk.owner            )
 WHERE a.owner = 'BRANCH_DEV'
   AND a.table_name IN ( 'table1','table2' )
     ;

Get table name by constraint name

SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = <<your constraint name>>

will give you the name of the table. If you don't have access to the DBA_CONSTRAINTS view, ALL_CONSTRAINTS or USER_CONSTRAINTS should work as well.

Oracle find a constraint

select * from all_constraints
where owner = '<NAME>'
and constraint_name = 'SYS_C00381400'
/

Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users.

The construction of the constraint name indicates a system generated constraint name. For instance, if we specify NOT NULL in a table declaration. Or indeed a primary or unique key. For example:

SQL> create table t23 (id number not null primary key)
  2  /

Table created.

SQL> select constraint_name, constraint_type
  2  from user_constraints
  3  where table_name = 'T23'
  4  /

CONSTRAINT_NAME                C
------------------------------ -
SYS_C00935190                  C
SYS_C00935191                  P

SQL>

'C' for check, 'P' for primary.

Generally it's a good idea to give relational constraints an explicit name. For instance, if the database creates an index for the primary key (which it will do if that column is not already indexed) it will use the constraint name oo name the index. You don't want a database full of indexes named like SYS_C00935191.

To be honest most people don't bother naming NOT NULL constraints.

How to retrieve all table columns, data type, data length, constraint type and referenced column and table

You'll need outer self join. Look at the following example:

Master table:

SQL> create table master (id_mas number constraint pk_mas primary key);

Table created.

Detail table, whose ID_MAS column references the master table:

SQL> create table detail (id_det number constraint pk_det primary key,
  2                       id_mas number constraint fk_det_mas references master (id_mas));

Table created.

Query that returns info you're looking for. Note outer self join with both USER_CONSTRAINTS and USER_CONS_COLUMNS (lines #8 and 9):

SQL> select mt.table_name,
  2         mtc.column_name,
  3         mt.constraint_type,
  4         --
  5         mtr.table_name ref_table_name,
  6         mtrc.column_name ref_column_name
  7  from user_constraints mt join user_cons_columns mtc on mtc.constraint_name = mt.constraint_name
  8  left join user_constraints mtr on mtr.constraint_name = mt.r_constraint_name
  9  left join user_cons_columns mtrc on mtrc.constraint_name = mtr.constraint_name
 10  where mt.table_name in ('MASTER', 'DETAIL')
 11  order by mt.table_name desc, mt.constraint_type;

TABLE_NAME      COLUMN_NAME     CONSTRAINT_TYPE REF_TABLE_NAME  REF_COLUMN_NAME
--------------- --------------- --------------- --------------- ---------------
MASTER          ID_MAS          P
DETAIL          ID_DET          P
DETAIL          ID_MAS          R               MASTER          ID_MAS

SQL>

The 3rd rows reads as: in detail table, there's the id_mas column which references master table's id_mas column.

How to find all foreign key constraints in oracle?

I would do it like that ;

If you want it aggregated with all column on the same row

select distinct c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, 
listagg(c2.column_name,',') WITHIN GROUP ( ORDER BY C2.POSITION) OVER ( PARTITION BY c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name ) column_list
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'

or like that if you want one row per column

select  c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, c2.column_name
from dba_constraints c1
JOIN dba_cons_columns c2
ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner
where C1.constraint_type = 'R'
order by c1.owner, c1.table_name, c1.constraint_name, c2.position

or if you want referencing column details AND referenced columns details

with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list
                                FROM DBA_CONS_COLUMNS GROUP BY owner, table_name, constraint_name )
select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list
from DBA_constraints c1
JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner
JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner
where C1.constraint_type = 'R' 
-- AND c1.owner = 'YOUR_SCHEMA';

---

Related Topics