Oracle Database: How to check if the table has a primary key. Types of constraints

A constraint is a rule that you define and its task is to protect the table from clutter that may arise as a result of adding incorrect or incomplete data.

Constraint types

At Oracle we have five types of restrictions that we can define are:

Primary Key: ensures that a given column or set of columns has unique values and cannot be null. Most often used as a row identifier.
Foreign Key Constraint: It ensures that the values in a column or set of columns combine with the values in the reference table.
Unique Constraint: ensures that the values in a given column are unique.
Not Null Constraint: ensures that values in a given column cannot be null.
Condition (Check Constraint): it ensures that the values meet a certain condition.

  • Primary Key Constraint: ensures that a given column or set of columns has unique values and cannot be null. Most often used as a row identifier.
  • Foreign Key Constraint: ensures that the values in a column or set of columns combine with the values in the reference table.
  • Unique Constraint: ensures that the values in a given column are unique.
  • Not Null Constraint: ensures that values in a given column cannot be null.
  • Check Constraint: ensures that the values meet a certain condition.

Several ways

There are many ways to check the constraints on the table. In this post I will show you how you can do it using:

  • SQL Developer
  • DBeaver
  • SQL statement

SQL Developer

Click on the selected table from the tree on the left, and then select the “Constraints” tab. For this post, I created a table TEST_TABLE and added the primary key on ID and Not Null on the AGE column.

DBeaver

In DBeaver, just expand the “Constraints” tab in the tree under the selected table.

SQL Statement

At the end I will show you how to download all restrictions for a given table. This query can also be helpful when we need to get information about all fences within the database.

SELECT ACC.TABLE_NAME, ACC.COLUMN_NAME, ACC.POSITION, AC.STATUS, AC.OWNER, AC.CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE ACC.TABLE_NAME = 'TEST_TABLE'
AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.OWNER = ACC.OWNER;

Of course, the above query should be modified so that it best suits our needs. For more tables, it is worth using filtering or sorting, e.g. after the OWNER, POSITION, CONSTRAINT_TYPE columns, etc.

If you enjoyed this post please add the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

0 0 vote
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments