Constraints in oracle
Constraints can be divided into the following two types,
1. Column level constraints: Limits only column data.
2. Table level constraints: Limits whole table data.
Constraints are used to make sure that the integrity of data is maintained in the database. Following are the most used constraints that can be applied to a table.
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
1. NOT NULL Constraint
NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value.
Note: - One important point to note about this constraint is that it cannot be defined at table level.
2. UNIQUE Constraint
UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. This constraint can be applied at column level or table level.
3. Primary Key Constraint
Primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.
4. Foreign Key Constraint
FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy links between tables.
Behavior of Foreign Key Column on Delete
There are two ways to maintain the integrity of data in Child table, when a particular record is deleted in the main table. When two tables are connected with foreign key and certain data in the main table is deleted, for which a record exits in the child table, and then we must have some mechanism to save the integrity of data in the child table.
A. On Delete Cascade: This will remove the record from child table, if that value of foreign key is deleted from the main table.
B. On Delete Null: This will set all the values in that record of child table as NULL, for which the value of foreign key is deleted from the main table.
C. If we don't use any of the above, then we cannot delete data from the main table for which data in child table exists.
5. CHECK Constraint
CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. It’s like condition checking before saving data into a column.
To understand concepts of constraints we create two tables and apply constraints on them as shown below
Creating department table
Inserting three rows in destination table as per constraints
Inserting fourth row in destination table
Above query show error because D_id is defined as primary key i.e. it should be unique and not null, here 3 is already inserted in designation table hence it shows error.
Inserting fourth row in destination table
Selecting designation table
Creating employee table
Inserting two rows in employee table as per constraints
Selecting employee table
Inserting third rows in employee table
Above query show error because emp_pfno is defined as unique, here 2 is already inserted in employee table hence it shows error.
Inserting again third rows in employee table
Above query show error because emp_name is defined as not null in employee table, here we trying to insert null value in emp_name of employee table hence it shows error.
Inserting again third rows in employee table
Above query show error because D_id is defined as foreign key in employee table which use reference of D_id in designation table, here 5 is not present in D_id in designation table hence it shows error.
I hope this article will help you