How to check if the table has a primary key Oracle Database – 5 Types of cool constraints!

You are currently viewing How to check if the table has a primary key Oracle Database – 5 Types of cool constraints!
Photo by Mika Baumeister on Unsplash
Share This Post, Help Others, And Earn My Heartfelt Appreciation! :)
4.7
(842)

In this tutorial I will show you how to check if the table has a primary key Oracle Database. 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.

How to check if the table has a primary key Oracle Database – 5 Types of cool constraints

Constraint types in Oracle Database

At Oracle Database 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 as tool for Oracle Database

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.

How to check if the table has a primary key Oracle Database - 5 Types of cool constraints
How to check if the table has a primary key Oracle Database

DBeaver

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

How to check if the table has a primary key Oracle Database - 5 Types of cool constraints
How to check if the table has a primary key Oracle Database

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;
How to check if the table has a primary key Oracle Database - 5 Types of cool constraints
How to check if the table has a primary key Oracle Database

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.

That’s all about topic: How to check if the table has a primary key Oracle Database!

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

How useful was this post?

Click on a star to rate it!

Average rating 4.7 / 5. Vote count: 842

No votes so far! Be the first to rate this post.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments