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.
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.
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!
