Display names of all constraints for a table in Oracle SQL

by

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

More Related Contents:

  1. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  2. How do I do top 1 in Oracle? [duplicate]
  3. REGEX to select nth value from a list, allowing for nulls
  4. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  5. How can I select from list of values in Oracle
  6. PLS-00428: an INTO clause is expected in this SELECT statement
  7. How to get the last row of an Oracle table
  8. Using pivot on multiple columns of an Oracle row
  9. How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
  10. Pivoting of data using two columns
  11. INSERT SELECT statement in Oracle 11G
  12. Sleep function in ORACLE
  13. SQL Error: ORA-00942 table or view does not exist
  14. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  15. Deferrable Constraints in SQL Server
  16. Nth max salary in Oracle
  17. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  18. Autoincrement in oracle to already created table
  19. Oracle 11g – Check constraint with RegEx
  20. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  21. How to use a dynamic parameter in a IN clause of a JPA named query?
  22. Why does the wm_concat not work here?
  23. The network adapter could not establish the connection – Oracle 11g
  24. Oracle string aggregation
  25. Curious issue with Oracle UNION and ORDER BY
  26. Subtracting Dates in Oracle – Number or Interval Datatype?
  27. How to call Oracle MD5 hash function?
  28. Oracle RAC and sequences
  29. Using bind variables with dynamic SELECT INTO clause in PL/SQL
  30. Insert into from CTE

Leave a Comment