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?
This question is related to
sql
oracle
oracle11g
constraints
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;
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
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
SELECT * FROM USER_CONSTRAINTS
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';
Source: Stackoverflow.com