CODEWITHSUNDEEP
sqloracle-databaseoracle11gconstraints
Jeris
Jeris

Reputation: 2473

Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL.

The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten.

How do I list out all the names of constraints that I have specified for each column of a table?

Is there any SQL statement for doing so?

Upvotes: 147

Views: 617712

Answers (9)

noobprogrammer1987
noobprogrammer1987

Reputation: 93

User constraints. I added the UPPER so I do not have to remember to use capital letters.

SELECT *
  FROM user_constraints
 WHERE table_name = UPPER('table_name');

All constraints. I added the UPPER so I do not have to remember to use capital letters.

SELECT *
  FROM all_constraints
 WHERE table_name = UPPER('table_name');

If you have a nice database program like toad this is also useful. This does not have to be in capital letters.

describe table_name;

Upvotes: -1

KIRAN KUMAR MATAM
KIRAN KUMAR MATAM

Reputation: 370

select a.constraint_name   as f_key,
   a.owner             as f_owner,
   a.table_name        as f_table,
   a.r_constraint_name as p_key,
   a.r_owner           as p_owner,
   b.table_name        as p_table
from all_constraints a inner join all_constraints b
                     on a.r_constraint_name = b.constraint_name

Upvotes: 1

Payel Senapati
Payel Senapati

Reputation: 1356

An easy way to this in MySQL is -

SHOW INDEXES IN <table-name>;

It shows the key names for the constraints

Upvotes: -3

Gweltaz Niquel
Gweltaz Niquel

Reputation: 659

Often enterprise databases have several users and I'm not aways on the right one :

SELECT * FROM ALL_CONSTRAINTS WHERE table_name = 'YOUR TABLE NAME' ;

Picked from Oracle documentation

Upvotes: 11

Wachaga Mwaura
Wachaga Mwaura

Reputation: 3600

Use either of the two commands below. Everything must be in uppercase. The table name must be wrapped in quotation marks:

--SEE THE CONSTRAINTS ON A TABLE
SELECT COLUMN_NAME, CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'TBL_CUSTOMER';

--OR FOR LESS DETAIL
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TBL_CUSTOMER';

Upvotes: 1

Ollie
Ollie

Reputation: 17538

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

Upvotes: 207

Van Gogh
Van Gogh

Reputation: 465

maybe this can help:

SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = "my_table_name";

cheers

Upvotes: 14

kapil kumar
kapil kumar

Reputation: 91

select constraint_name,constraint_type 
from user_constraints
where table_name = 'YOUR TABLE NAME';

note: table name should be in caps.

In case you don't know the name of the table then,

select constraint_name,constraint_type,table_name 
from user_constraints;

Upvotes: 9

Paciv
Paciv

Reputation: 1487

SELECT * FROM USER_CONSTRAINTS

Upvotes: 18

Related Questions