Learn SQL : Key Constraints in SQL With Example and Interview Questions

WHAT ARE CONSTRAINTS?

Constraints are the set of rules that are specify for data in the table. They are used to ensure the accessibility and accuracy of the data in the table. If someone enter the data different from the constraint applied on it then that action is aborted.

The commonly used Constraints are:

• Not Null
• Unique Key
• Primary key
• Foreign key
• Check

Before studying the details off these constraints, take a look towards the keys used in database:

WHAT IS KEY?

Key is an attribute or combination of attributes for unique identification of records in an entity set.

Entity: A person, place, object, events or concepts in the user environment about which the organisation wish to maintain the data.

Attributes: It is the name property or characteristics of entity or we can say columns in the table.

1. Primary Key: It is used to uniquely identify the records in the table. This is the special column of the table that never contains a "NULL" value.

Features:

• It contain unique value for each record.
• It can not contain null value.

Example:

Emp_id	Name	Age	SSN
101	A	20	S01
102	B	21	S02
103	C	23	S03

In above example primary key is "Emp_id".

2. Super Key: It is the set of one or more columns when taken collectively can identify uniquely an entity.

• There can be more than one super key in the table.
• By default, all combinations of attributes can be set as super key.
• It is not minimal.

Example:

Super key 1 = {Emp_id, Name, Age, SSN}
Super key 2= {Emp_id, Name, Age}
Super key 3= {Emp_id, Name}

Super key 4={Emp_id, SSN}

3. Candidate Key: The minimal super key is call as candidate key i.e., those super key of  the entity set that does not have the subset.

• There can be more than one candidate key.

Example:
Candidate key 1 = {Emp_id}
Candidate key 2 = {SSN}

4. Composite Key: If we use multiple attributes to create primary key than that primary key is known as composite key.

5. Foreign Key: It is a field in one table that uniquely identifies fields of another table or same table or we can say it is a field in one table that refers to the primary key of another table.
Table containing foreign key is called child table and, the table containing the candidate key is known as parent table.

Employee table (Child table)

Emp_id	Name	Age	D.No.
101	A	20	10
102	B	20	11
103	C	21	10

Department table (Master table)

D.No.	D.Name	D.Loc
10	CSE	Delhi
11	IT	Bombay

6. Unique Key: It is used to ensure that all values in the columns are different. It is same as primary key but the difference is that there is only one primary key but a table have more than one unique key. Primary key can not be null but unique key can be null.

Questions:

1. Define keys.
2. What is the difference between Primary and Unique key?
3. What is Child and Master table?

Previous