DBMS INTEGRITY CONSTRAINTS

DIFFERENT TYPES OF INTEGRITY CONSTRAINTS .

THERE ARE MAINLY 3 TYPES OF INTEGRITY CONSTRAINTS 

  (A)-domain integrity constraints 

   (B)-referential integrity constraints 

   (C)-functional integrity constraints 

    (D)-Foreign Key Integrity Constraint

(A)-Domain Integrity

Domain integrity means the definition of a valid set of values for an attribute. You define 

- data type, 
- lenght or size
- is null value allowed
- is the value unique or not
for an attribute.

You may also define the default value, the range (values in between) and/or specific values for the attribute. Some DBMS allow you to define the output format and/or input mask for the attribute.

These definitions ensure that a specific attribute will have a right and proper value in the database.

(B)-Entity Integrity Constraint

The entity integrity constraint states that primary keys can't be null. There must be a proper value in the primary key field.

This is because the primary key value is used to identify individual rows in a table. If there were null values for primary keys, it would mean that we could not indentify those rows.

On the other hand, there can be null values other than primary key fields. Null value means that one doesn't know the value for that field. Null value is different from zero value or space.

In the Car Rental database in the Car table each car must have a proper and unique Reg_No. There might be a car whose rate is unknown - maybe the car is broken or it is brand new - i.e. the Rate field has a null value. See the picture below.

The entity integrity constraints assure that a spesific row in a table can be identified. 

Picture. Car and CarType tables in the Rent database

(C)Referential Integrity Constraint

The referential integrity constraint is specified between two tables and it is used to maintain the consistency among rows between the two tables.

The rules are:
1. You can't delete a record from a primary table if matching records exist in a related table.
2. You can't change a primary key value in the primary table if that record has related records.
3. You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
4. However, you can enter a Null value in the foreign key, specifying that the records are unrelated.

Examples

Rule 1. You can't delete any of the rows in the CarType table that are visible in the picture since all the car types are in use in the Car table.

Rule 2. You can't change any of the model_ids in the CarType table since all the car types are in use in the Car table.

Rule 3. The values that you can enter in the model_id field in the Car table must be in the model_id field in the CarType table.

Rule 4. The model_id field in the Car table can have a null value which means that the car type of that car in not known

Foreign Key Integrity Constraint

There are two foreign key integrity constraints: cascade update related fields and cascade delete related rows. These constraints affect the referential integrity constraint.

   note - both of these are applied on the primary table .
Cascade Update Related Fields

Any time you change the primary key of a row in the primary table, the foreign key values are updated in the matching rows in the related table. This constraint overrules rule 2 in the referential integrity constraints. 

If this contraint is defined in the relationship between the tables Car and CarType, it is possible to change the model_id in the CarType table. If one should change the model_id 1 (Ford Focus) to model_id 100 in the CarType table, the model_ids in the Car table would change from 1 to 100 (cars ABC-112, ABC-122, ABC-123).

Cascade Delete Related Rows

Any time you delete a row in the primary table, the matching rows are automatically deleted in the related table. This constraint overrules rule 1 in the referential integrity constraints. 

SQL Constraints

SQl Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints can be divided into following two types,

• Column level constraints : limits only column data
• 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.

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

---

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. One important point to note about NOT NULL constraint is that it cannot be defined at table level.

---

Example using NOT NULL constraint

CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will not take NULL value.

---

UNIQUE Constraint

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data. UNIQUE constraint can be applied at column level or table level.

---

Example using UNIQUE constraint when creating a Table (Table Level)

CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will only have unique values and wont take NULL value.

---

Example using UNIQUE constraint after Table is created (Column Level)

ALTER table Student add UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

---

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.

---

Example using PRIMARY KEY constraint at Table Level

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);

The above command will creates a PRIMARY KEY on the s_id.

---

Example using PRIMARY KEY constraint at Column Level

ALTER table Student add PRIMARY KEY (s_id);

The above command will creates a PRIMARY KEY on the s_id.

---

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. To understand FOREIGN KEY, let's see it using two table.

Customer_Detail Table :

c_id	Customer_Name	address
101	Adam	Noida
102	Alex	Delhi
103	Stuart	Rohtak

Order_Detail Table :

Order_id	Order_Name	c_id
10	Order1	101
11	Order2	103
12	Order3	102

In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table. The value that is entered in c_id which is set as foreign key in Order_Detail table must be present in Customer_Detailtable where it is set as primary key. This prevents invalid data to be inserted into c_id column of Order_Detailtable.

---

Example using FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(order_id int PRIMARY KEY,
order_name varchar(60) NOT NULL,
c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id));

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column of Customer_Detail.

---

Example using FOREIGN KEY constraint at Column Level

ALTER table Order_Detail add FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

---

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted in main table. When two tables are connected with Foriegn key, and certain data in the main table is deleted, for which record exit in child table too, then we must have some mechanism to save the integrity of data in child table.

• On Delete Cascade : This will remove the record from child table, if that value of foriegn key is deleted from the main table.
• On Delete Null : This will set all the values in that record of child table as NULL, for which the value of foriegn key is eleted from the main table.
• 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. We will get an error if we try to do so.

ERROR : Record in child table exist

---

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. Its like condition checking before saving data into a column.

---

Example using CHECK constraint at Table Level

create table Student(s_id int NOT NULL CHECK(s_id > 0),
Name varchar(60) NOT NULL,
Age int);

The above query will restrict the s_id value to be greater than zero.

---

Example using CHECK constraint at Column Level

ALTER table Student add CHECK(s_id > 0);