Display names of all constraints for a table in Oracle SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT   FROM USER CONSTRAINTS

User · Answer

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     lt your table name gt      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     lt your table name gt      AND constraint name     lt your constraint name gt      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     lt schema owner of the table gt

User · Answer

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

[sql] Display names of all constraints for a table in Oracle SQL

Examples related to sql

Examples related to oracle

Examples related to oracle11g

Examples related to constraints