SQL constraints are used to specify
rules for the data in a table.
If there is any violation between the constraint and the
data action, the action is aborted by the constraint.
Constraints can be specified when the table is created
(inside the CREATE TABLE statement) or after the table is created (inside the
ALTER TABLE statement).
SQL CREATE
TABLE + CONSTRAINT Syntax
CREATE TABLE table_name
( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, …. ); |
In SQL, we have the following constraints:
·
NOT NULL –
Indicates that a column cannot store NULL value
·
UNIQUE –
Ensures that each row for a column must have a unique value
·
PRIMARY KEY –
A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination
of two or more columns) have a unique identity which helps to find a particular
record in a table more easily and quickly
·
FOREIGN KEY –
Ensure the referential integrity of the data in one table to match values in
another table
·
CHECK –
Ensures that the value in a column meets a specific condition
·
DEFAULT –
Specifies a default value for a column
SQL NOT
NULL Constraint
By default, a table column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept
NULL values.
The NOT NULL constraint enforces a field to always contain
a value. This means that you cannot insert a new record, or update a record
without adding a value to this field.
The following SQL Table the “StudentId” column and
the “StudentName” column to not accept NULL values:
CREATE TABLE
Student
(
StudentID int NOT NULL,
StudentName varchar(255) NOT NULL,
FirstName varchar(255),
StudentAddress varchar(255),
City varchar(255)
)
|
And StudentID and StudentName is mandatory when you are
inserting any record to database ,otherwise it will error.
SQL
UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a
database table.
The UNIQUE and PRIMARY KEY constraints both provide a
guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE
constraint defined on it.
Note that you can have many UNIQUE constraints per table,
but only one PRIMARY KEY constraint per table.
The following SQL Table the “StudentId” column and
the “StudentName” column to not accept NULL values:
CREATE TABLE
Student
(
StudentID INT NOT NULL UNIQUE,
StudentName varchar(255) NOT NULL,
FirstName varchar(255),
StudentAddress varchar(255),
City varchar(255)
)
|
SQL UNIQUE
Constraint on ALTER TABLE
ALTER TABLE Student
ADD CONSTRAINT uc_StudentID UNIQUE (StudentID,StudentName) |
DROP a UNIQUE
Constraint
ALTER TABLE Student
DROP CONSTRAINT uc_StudentID |
SQL PRIMARY
KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record
in a database table.
Primary keys must contain UNIQUE values.
A primary key column cannot contain NULL values.
Most tables should have a primary key, and each table can
have only ONE primary key.
CREATE TABLE
Student
(
StudentID INT NOT NULL PRIMARY KEY,
StudentName varchar(255) NOT NULL,
FirstName varchar(255),
StudentAddress varchar(255),
City varchar(255)
)
|
Note: (1) We can create only one primary key for a table.
(2): However, the VALUE of the primary key is made up of
TWO COLUMNS (StudentID + StudentName), This combination of making primary for
two column,called composite key.
SQL PRIMARY
KEY Constraint on ALTER TABLE
ALTER TABLE Student
ADD CONSTRAINT pk_PersonID PRIMARY KEY (StudentId,StudentName) |
To DROP a
PRIMARY KEY Constraint
ALTER TABLE Student
DROP CONSTRAINT pk_StudentID |
SQL FOREIGN
KEY Constraint
A FOREIGN KEY in one table points to a PRIMARY KEY in
another table.
CREATE TABLE
Student
(
StudentID INT NOT NULL PRIMARY KEY,
StudentName varchar(255) NOT NULL,
FirstName varchar(255),
StudentAddress varchar(255),
City varchar(255)
)
CREATE table Collage
(
CollageID INT NOT NULL PRIMARY KEY,
Address varchar(255),
StudentID INT NOT NULL REFERENCES
dbo.Student(ID) — it is the foreign for the studentID
)
You can create Collage table like
following
CREATE table Collage
(
CollageID,
collageName varchar(255) ,
StudentID int,
PRIMARY KEY (CollageID),
FOREIGN KEY (StudentID) REFERENCES
Student(StudentID)
CONSTRAINT fk_StdCollage FOREIGN
KEY (StudentID)
REFERENCES Student(StudentID)
)
|
SQL FOREIGN
KEY Constraint on ALTER TABLE
ALTER TABLE Collage
ADD CONSTRAINT fk_ StdCollage FOREIGN KEY (StudentID) REFERENCES Student(StudentID) |
To DROP a
FOREIGN KEY Constraint
ALTER TABLE Collage
DROP CONSTRAINT fk_ StdCollage |
SQL CHECK
Constraint
The CHECK constraint is used to limit the value range that
can be placed in a column.
If you define a CHECK constraint on a single column it
allows only certain values for this column.
If you define a CHECK constraint on a table it can limit
the values in certain columns based on values in other columns in the row.
SQL CHECK
Constraint on CREATE TABLE
CREATE TABLE
Student
(
StudentID INT NOT NULL CHECK
(StudentID>0),
StudentName varchar(255) NOT NULL,
FirstName varchar(255),
StudentAddress varchar(255),
City varchar(255)
)
|
SQL CHECK
Constraint on ALTER TABLE
ALTER TABLE Student
ADD CONSTRAINT chk_student CHECK (StudentID >0 AND City=’Jaipur’) |
To DROP a
CHECK Constraint
ALTER TABLE Student
DROP CONSTRAINT chk_Student |
SQL DEFAULT
Constraint
SQL DEFAULT Constraint on CREATE TABLE
CREATE TABLE
Student
(
StudentID INT NOT NULL CHECK
(StudentID>0),
StudentName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
StudentAddress VARCHAR(255),
City VARCHAR(255) DEFAULT ‘Jaipur’
)
|
SQL DEFAULT
Constraint on ALTER TABLE
ALTER TABLE Student
Add Constraint DF_COLUMNName default 0 'jaipur' for ColumnName |
To DROP a
DEFAULT Constraint
ALTER TABLE Student
ALTER COLUMN City DROP DEFAULT |
EmoticonEmoticon