Exclusive Clothing Sale On Flipkart

Get up to 50% off on our latest collection.

Shop Now

Constraints – PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK, and DEFAULT

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(sizeconstraint_name,
column_name2 data_type(sizeconstraint_name,
column_name3 data_type(sizeconstraint_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



Share this

Related Posts

Previous
Next Post »

Exclusive Clothing Sale On Flipkart

Get up to 50% off on our latest collection.

Shop Now