Sql Server Constraints
This Article is about using Constraints in Microsoft Sql Server. There are six constraints Primary key, Foreign key, Unique, Not null, Check and Default. These Constraints allows us to define the ways in which we can automatically enforce the integrity of a database. Each constraint plays an important role in our database architecture.
NOT NULL Constraint:
NOT NULL Constraint is used to have a column without NULL values. Without adding values we cannot update the table. To avoid NULL values we can use this constraint.
CREATE TABLE Details
(
P_Id int NOT NULL,
Name varchar(30) NOT NULL,
Address varchar(100)
)Primary Key Constraint:
Primary Key Constraint is used in the primary key column of the table to avoid NULL values and to have unique values in that column.
When the primary key is the combination of two columns multiple column constraint is created using constraint name.
We can add the primary key constraint to the already created table by using Alter statement.
To drop a Primary key constraint use drop statement.
Primary key constraint:
CREATE TABLE Details
(
P_Id int NOT NULL PRIMARY KEY,
Name varchar(30) NOT NULL,
Address varchar(100)
)
Primary key constraint on multiple columns:
CREATE TABLE Details
(
P_Id int NOT NULL,
Name varchar(30) NOT NULL,
Address varchar(100)
CONSTRAINT PKY_ID PRIMARY KEY (Id,Name)
)
Alter constraint:
ALTER TABLE Details ADD PRIMARY KEY (P_Id)
Drop constraint:
ALTER TABLE Details DROP CONSTRAINT PKY_IDForeign key Constraint:
Foreign key Constraint is used to make link between two tables. By using this constraint we can check that the value inserted in the foreign key column must present in the primary key column.
CREATE TABLE Summary
(
S_Id int NOT NULL,
Designation varchar(30) NOT NULL,
P_Id int FOREIGN KEY REFERENCES Details(P_Id)
)
Multiple columns:
CREATE TABLE Summary
(
S_Id int NOT NULL,
Designation varchar(30) NOT NULL,
PRIMARY KEY (S_Id),
CONSTRAINT FKY_ID FOREIGN KEY (P_Id)
REFERENCES Details(P_Id)
)
Alter constraint:
ALTER TABLE Details ADD CONSTRAINT FKY_ID
FOREIGN KEY (P_Id) REFERENCES Details(P_Id)
Drop constraint:
ALTER TABLE Details DROP CONSTRAINT FKY_IDUnique Constraint:
Unique constraint is as same as primary key constraint. The only difference between these two constraints is we can create many unique constraints in a single table.
CREATE TABLE Summary
(
S_Id int NOT NULL UNIQUE,
Designation varchar(30) NOT NULL,
P_Id int NOT NULL UNIQUE,
)
Multiple columns:
CREATE TABLE Summary
(
S_Id int NOT NULL,
Designation varchar(30) NOT NULL,
P_Id int NOT NULL,
CONSTRAINT UQ_ID UNIQUE (S_Id,P_Id)
)
Alter constraint:
ALTER TABLE Details ADD CONSTRAINT UQ_ID UNIQUE (S_Id)
Drop constraint:
ALTER TABLE Details DROP CONSTRAINT UQ_IDCHECK Constraint:
CHECK constraint is used to check the condition before inserting the value in the table.
CREATE TABLE Details
(
P_Id int NOT NULL CHECK (P_Id > 0),
Name varchar(30) NOT NULL,
Address varchar(100)
)DEFAULT Constraint:
DEFAULT constraint is used to insert a constant value to the column for the whole table. DEFAULT constraint can also be used to insert system functions like GETDATE()
CREATE TABLE Details
(
P_Id int NOT NULL ,
Name varchar(30) NOT NULL,
Address varchar(100) DEFAULT 'Chennai'
)
By using system functions:
CREATE TABLE Details
(
P_Id int NOT NULL PRIMARY KEY,
Name varchar(30) NOT NULL,
Address varchar(100),
Today datetime DEFAULT GETDATE()
)