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?
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
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';
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
An easy way to this in MySQL is -
SHOW INDEXES IN <table-name>;
It shows the key names for the constraints