Constraints of SQL Table

Constraints helps to ensure that valid data goes into the database entity by specifying rules.

- NOT NULL : Ensures that attribute cannot be null.

 - Primary Key : Represents a column or combination of columns as a unique identity for a row

 - FOREIGN KEY : Used to establish relationships between tables using matching columns.

 - UNIQUE : The value is unique (not repeated for other entities)

 - Check : Used to allow values for a column based on a condition

 - Default : Used for inserting default value into columns.

NOT NULL
 The NOT NULL constraint will not allow a column to accept NULL values

 Below is an example for creating NOT NULL constraint.

 Create table Customer(CustomerID int NOT NULL,Name varchar(50) NOT
NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))

Primary Key
 The PRIMARY KEY constraint identifies each record in a database table by allowing only unique values. Each table can have only ONE primary key

 Below is an example for creating Primary Key constraint.

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))

 Primary Key can also be added using alter statement as below

                                       ALTER TABLE Customer
                                       ADD PRIMARY KEY (CustomerID)

Foreign Key

 Foreign Key is used to establish relationships between tables using matching columns. A FOREIGN KEY in one table refers to a PRIMARY KEY in another table. It ensures that data available in the Primary key column of one table can only be entered in the Foreign key column of another table.

 Below is an example for creating Foreign Key constraint

 CREATE TABLE Orders
 OrderId int NOT NULL PRIMARY KEY,
 OrderNo int NOT NULL,
 CustomerId int FOREIGN KEY REFERENCES Customer(CustomerID)


 Foreign Key can also be added using alter statement as below

 ALTER TABLE Orders
 ADD FOREIGN KEY (CustomerId)
 REFERENCES Customer(CustomerId)

UNIQUE
 UNIQUE Constraint like Primary Key ensures uniqueness for a column. The UNIQUE Constraint is different from Primary Key in that a table can have only Primary Key but there can be many columns with Unique Constraint.

 Below is an example for creating Unique Constraint

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Contact int UNIQUE)


Check
 Check constraint is used to allow values for a column based on a condition.

 Below is an example for creating Check Constraint

 We can create a Check Constraint on Age column to allow only values greater than 0.

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),CHECK (Age >0))


Default
 Default constraint is used for inserting default value into columns.

 Below is an example for creating Default Constraint

 We can create a Default Constraint on Location column to enter the default location as 'India' if location is not inserted while entering Customer details.

 Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Location varchar(20) DEFAULT 'India')


Problem Scenario:
 Create database tables for adding employee details such as Employeeid, Employee name,Date of Joining,Age,Contact number and their salary details such as  HRA,Basic,PF,Bonus and Gross Salary on monthly basis.

Approach to solve the problem
 1. As we need to insert Employee details and their salary details on monthly basis we can  create one table for Employee details and one table for storing Employee Salary details
 2. Create the table Employee with Employeeid,Employee name,Date of Joining, Age,Contact number columns.
 3.Create the table EmployeeSalary with HRA,Basic,PF,Bonus and Gross
 Salary,SalaryDate columns.


Solution
 Create table Employee(EmployeeID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,DateOfJoining Date,Contact
int UNIQUE,CHECK (Age >0))

 Create Table EmployeeSalary(SalaryID int NOT NULL Primary Key,Basic int,HRA
int,Bonus int,Gross int,SalaryDate Date,EmployeeID FOREIGN KEY REFERENCES
Employee(EmployeeID))

Explanation about the solution
 1. As the Employee Id should be unique and identify each row separately we can create
 Employee Id as the Primary Key in Employee table.
 2. We can create a Check Constraint on age and Unique Constraint on Contact in
 Employee table.
 3. In Employee salary details create Salary Id as Primary Key and Employee ID as foreign
 key referencing Employee Id in Employee table.