Sql Constraint

Sql Constraint

Constraints are the rules apply on data columns on table. These are used to ensure the accuracy and reliability of the data in the database.

There are two type of Constraints

·         Column level : Column level constraints are applied only to one column

·         Table level: table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL.

·        NOT NULL: Ensures that a column cannot have NULL value.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200)

)

·        DEFAULT: Provides a default value for a column when none is specified.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

JoiningDate Datetime DEFAULT GETDATE()

)

·        UNIQUE: Ensures that all values in a column are different.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL ,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

CONSTRAINT uc_EmpID UNIQUE (Emp_Id)

)

·        PRIMARY: Uniquely identified each rows/records in a database table.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL PRIMARY KEY,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

)

·        FOREIGN Key: Uniquely identified a rows/records in any another database table.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL PRIMARY KEY,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

PRIMARY KEY (Emp_Id) FOREIGN KEY REFERENCES Emp_Master(Emp_Id)

)

·        CHECK: The CHECK constraint ensures that all values in a column satisfy certain conditions.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL CHECK (P_Id>0),

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

PRIMARY KEY (Emp_Id)

)

·        INDEX: Use to create and retrieve data from the database very quickly.

User can apply the Constraints either at the time of creation of the table with the CREATE TABLE statement or by using ALTER TABLE statement to create constraints on existing table.

Dropping Constraints:

Constraint can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

e.g., to drop the primary key constraint in the EMP_Details table, you can use the following command:

ALTER TABLE EMP_Details DROP CONSTRAINT EMP_Id;

Integrity Constraints:

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.