Display names of All constraints for a table in Oracle SQL

In Oracle SQL, the names of all the constraints for the table can be displayed using system views such as USER_CONSTRAINTS and ALL_CONSTRAINTS.

This blog explains rendering all the constraint names for the table in Oracle SQL.

Table Of Contents

• Getting All Constraint Names in Oracle SQL
  • Syntax
  • Explanation
• Commonly Used Constraints in Oracle SQL
• Conclusion
• FAQs

Getting All Constraint Names in Oracle SQL

In SQL, the Constraints define the rules for data in a table, which are applied to table columns when the table is created, enforcing data integrity and consistency. For this reason, the data adheres to specific requirements (like unique values, valid foreign keys, or non-null values).

To analyze or debug a table’s structure, you can view the names of all constraints that are applied to a specific table. The system views such as USER_CONSTRAINTS and ALL_CONSTRAINTS, which are provided by Oracle, facilitate the retrieval of these kinds of information efficiently.

Syntax

SELECT constraint_name, constraint_type, table_name
FROM ALL_CONSTRAINTS
WHERE table_name = “EMPLOYEES”;

Explanation

Here, the constraint_name depicts the name of the constraint,  constraint_type denotes the types of constraints followed by their value, and table_name depicts the name of the table to which the constraint belongs. The ALL_CONSTRAINTS helps to get the metadata about all constraints that are accessible to the current user, regardless of the schema.

Constraints can be categorized into column level or table level. The column-level constraints are implemented to a column, and the table-level constraints are implemented to the whole table.

The user_constraints are utilized to display the names of the constraints in the database. The all_constraints provides the metadata about all constraints that are accessible to the current user, regardless of the schema.

Commonly Used Constraints in Oracle SQL

Most commonly used constraints in Oracle SQL

Example: Below example rendering names of all the constraints with the following SQL Query:

Employees Table:

Example: Getting the

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEES';

Output:

Conclusion

Constraints can be utilized to restrict the type of data that can be put into a table. For this, it certifies the accuracy and reliability of the table data. In case of any violation between the constraint and the data action, the action is aborted. Using the USER_CONSTRAINTS or ALL_CONSTRAINTS views can help to easily identify the constraints implemented in a table and their respective types. This helps the database design, debugging, and maintaining consistency across schemas.

FAQs

SELECT constraint_name
FROM user_constraints
WHERE table_name = 'User_Data' AND constraint_type = 'P';

ALTER TABLE table_Name DROP CONSTRAINT constraint_name;

SELECT column_name
FROM user_cons_columns
WHERE constraint_name = 'EMP_PK';