Constraints in Oracle

Constraints in MySQL with Examples

In this article, I am going to discuss Constraints in Oracle with Examples. At the end of this article, you will understand what are constraints, why do we need constraints and the different types of constraints available in the Oracle database with Examples.

What is Data Integrity in Oracle?

While learning Constraints we need to understand one database term called Data Integrity. Data integrity means the data contained in the database is accurate, consistent, and reliable. To provide data integrity, RDBMS provides us a set of integrity constraints that ensures that the data entered into the database is going to be accurate, consistent, and reliable. This is the reason why the end-user can trust the data stored in the database.

So, Data Integrity means maintaining accurate and consistent data in database tables. This data integrity again classified into two types are as follows

  1. Declarative / pre-defined data integrity (using constraints)
  2. Procedural/ user-defined data integrity (using triggers)
Declarative / Pre-define Data Integrity:

This data integrity can be implemented with help of “constraints”. These are again of three types,

  1. Entity integrity
  2. Referential integrity
  3. Domain integrity

Entity Integrity: It ensures that each row is uniquely identified in a table. To implement this mechanism, we use the primary key or unique constraint.

Referential Integrity: It ensures that to create a relationship between tables. To implement this mechanism, we use the foreign key (referential key).

Domain integrity: Domain is nothing but a column. It ensures that to check values with user define conditions before accepting values into a column. To perform this mechanism we use check, default, not null constraints.

What are Constraints in Oracle?

The Oracle Constraints define specific rules to the column(s) data in a database table. While inserting, updating, or deleting the data rows, if the rules of the constraint are not followed, the system will display an error message and the action will be terminated. The Constraints in Oracle are defined while creating a new table. We can also alter the table and add new Constraints. The Constraints are mainly used to maintain data integrity. 

Why do we need Constraints in Oracle?

The Constraints in Oracle are basically used to restrict the insertion of unwanted data in the database. That means they are mainly used to maintain data integrity. We can create the constraint on single or multiple columns of a table in Oracle

What are the different types of Constraints available in Oracle?

Constraints are used to restrict unwanted(invalid) data into the table. All databases are supporting the following constraint types for maintaining data integrity.

  1. NOT NULL Constraint
  2. UNIQUE KEY Constraint
  3. CHECK KEY Constraint
  4. PRIMARY KEY Constraint
  5. FOREIGN KEY Constraint (REFERENCES Key).
  6. DEFAULT Constraint
How to create Constraints in Oracle?

All databases are supporting the following two types of methods to define constraints.

Column Level:

In this method, we are defining constraints on individual columns. The following is the syntax to define constraints at the column level.

CREATE TABLE <TN>(<COLUMN NAME1><DATATYPE>[SIZE] <CONSTRAINT TYPE>,……);

Table Level:

In this method, we are defining constraints after all columns are declared i.e. at the end of the table definition. The following is the syntax to define constraints at the table level.

CREATE TABLE <TN>(<COLUMN NAME1><DATATYPE>[SIZE],<COLUMN NAME2> <DATATYPE>[SIZE],………………………………..,<CONSTRAINT TYPE>(<COLUMN NAME1>, <COLUMN NAME2>,……………..));

In the next article, I am going to discuss UNIQUE Constraint in Oracle with Examples. Here, in this article, I try to explain Constraints in Oracle with Examples and I hope you enjoy this Constraints in Oracle with Examples article.

Leave a Reply

Your email address will not be published.