Constraints in SQL and its Types

Constraints - 

Constraints is set of rules and regulation that has to be followed while inserting data in table.

Type of Constraints

1. Unique
2. Not null
3. Check
4. Primary Key
5. Foreign Key

1.Unique - Unique constraints is given for column for that column will not accepted any duplicate value.

Characteristics of Null - 

1. Null is empty of blank or nothing.
2. Null is pre-defined keyword in SQL.
3. Any operation we can perform on null gives as null as output.
    null+10 ---null
    null-10 --- null
    null*10 ---null
    null/10 --- null
4. Null will not allocate any memory.

There is disadvantages for the null value and disadvantages can be over come with the help of function  called as Null value logic(NVL).

2. Not Null - If a not null constraints is given for particular column then that column will not accept any Null value When we provide not null constraints for column then that column becomes mandatory. 

Note - Constraints are optional or not mandatory.

We can provide multiple constraints for column we can keep column without any constraints.

3. Check - Check is extra validation given for a column.

check constraint will always accept the condition. If the condition is true then it will accept the data if condition is false then it will not accept data.

Attribute - Attribute are the characteristics of the object or an entity.

Key Attribute - Key attribute are the attribute which can identify a particular record uniquely.

candidate key - Key attribute which are eligible to become a primary key are called candidate key.

4. Primary Key - Primary key is key attribute which can identify a particular record uniquely.

Characteristics of Primary Key -

1. If a column is made as a primary key if will not accept any duplicate data.(It is unique).
2. If  a column is made as primary key it will not accept any null value (It is not null).
3. There can be only one primary key for a table.
4. Primary key is not mandatory but as per company conversion it is recommended that table should have a primary key.

Why do you need primary key ?

To make a column as a foreign key we need a column which is a primary key in its own table.

To eliminate or avoid the duplicate data table should have primary key.

 

 5. Foreign Key - Foreign Key is use to establish the relationship between two or more than two table.

Characteristics of Foreign key -

1. The column is made as a foreign key, It can accept duplicate value (It is not unique).
2. If a column is made as a foreign key.
3. The table can have multiple foreign key.
4. To become a foreign key It should be a primary key of its own table.
5. Foreign key is not mandatory because constraint are not mandatory but we want establish the relationship between two or more that two people then table should have foreign key.

Different Between Primary key and foreign key.