SQL Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the
table is created with the ALTER TABLE statement.
Syntax:-
CREATE TABLE table_name
(
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. If there is any violation between the constraint and the data action,
the action is aborted.

The following constraints are commonly used in SQL:

1. NOT NULL - Ensures that a column cannot have a NULL value
2. UNIQUE - Ensures that all values in a column are different
3. PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row
in a table
4. FOREIGN KEY - Prevents actions that would destroy links between tables
5. CHECK - Ensures that the values in a column satisfies a specific condition
6. DEFAULT - Sets a default value for a column if no value is specified
7. CREATE INDEX - Used to create and retrieve data from the database very quickly
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT
accept NULL values. This enforces a field to always contain a value, which means that you cannot
insert a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE:-

Create table Student#90
(
Regd_No int NOT NULL,
Name Varchar(255) NOT NULL,
Ph_No Int,
Age int NOT NULL
);
SQL NOT NULL on ALTER TABLE:-
Alter Table Student#90 modify Ph_No int NOT NULL;
 SQL UNIQUE Constraints
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column
or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint
per table.

SQL UNIQUE Constraint on CREATE TABLE

# ------For a single Column # ------For Multiple Column
Create Table CGU#1 Create Table CGU#2
( (
ID int NOT NULL,
ID int NOT NULL UNIQUE,
Name Varchar(255),
Name Varchar(255),
City Varchar(255),
City Varchar(255) CONSTRAINT UC_CGU UNIQUE (ID,Name)
); );
SQL UNIQUE Constraint on ALTER TABLE
#-----For single column
Alter table CGU#1 Add Unique (Name);

#-----For Multiple column

Alter table CGU#1 Add Constraint UC_CGU1 UNIQUE (Name, City);

DROP a UNIQUE Constraint

Alter table CGU#1 Drop Constraint UC_CGU1;
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single or
multiple columns (fields).

SQL PRIMARY KEY Constraint on CREATE TABLE

# Single Column # Multiple Columns
Create Table Dept#12 Create Table Dept#1
( (
ID int PRIMARY KEY, ID int,
Name varchar(50), Name varchar(50),
Address varchar(20),
Address varchar(20)
CONSTRAINT PK_Dept PRIMARY KEY (ID,Name)
); );
SQL PRIMARY KEY on ALTER TABLE
#--------Single Column
Alter table Stud
Add primary key (ID);

#-------Multiple Column
ALTER TABLE Stud#1
ADD CONSTRAINT PK_Stud#1 PRIMARY KEY (Name, Address);

DROP a PRIMARY KEY Constraint

ALTER TABLE Stud#1
DROP CONSTRAINT PK_Stud#1;
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in
another table.
The table with the foreign key is called the child table, and the table with the primary key is called the
referenced or parent table. Primary
Primary Key
Student#56 Key Project#57
Sid Sname Sregd Sadd Pid Pname Sid
S001 Sai 20231 CTC P01 DBE S001
S002 Rohan 20232 BLS P02 IWT S002
Foreign
P03 DS S001 Key
SQL FOREIGN KEY on CREATE TABLE
Create table Student#56
(
Sid varchar(5) primary key,
Sname varchar(120),
Sregd varchar(356),
Sadd varchar(245)
);

Create table Project#57

(
Pid varchar(5) primary key,
Pname varchar(120),
Sid varchar(356),
CONSTRAINT FK_project FOREIGN KEY (Sid) REFERENCES Student#56(Sid)
);
SQL FOREIGN KEY on ALTER TABLE
ALTER TABLE Project#57 ADD CONSTRAINT FK_pj FOREIGN KEY (Sid) REFERENCES
Student#56(Sid);

DROP a FOREIGN KEY Constraint

ALTER TABLE Project#57 DROP CONSTRAINT FK_project;
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 column it will allow 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 on CREATE TABLE

#----For single column #----For Multiple Column
CREATE TABLE CGU#5
CREATE TABLE CGU#7
(
( ID int NOT NULL,
ID int NOT NULL, LastName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL, FirstName varchar(255),
FirstName varchar(255), Age int,
Age int CHECK (Age>=18) City varchar(255),
); CONSTRAINT CHK_CGU CHECK (Age>=18 AND
City=‘BBSR')
);
SQL CHECK on ALTER TABLE
#----For Single column
ALTER TABLE CGU#7 ADD CHECK (LastName=‘Rout’);

#---For Multiple Column

ALTER TABLE CGU#7
ADD CONSTRAINT CHK_CGU CHECK (Age>=18 AND City=‘BLS’);

DROP a CHECK Constraint

ALTER TABLE CGU#7 DROP CONSTRAINT CHK_CGU;
The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.

SQL DEFAULT on CREATE TABLE

CREATE TABLE Student#53 (
ID int NOT NULL,
Name varchar(255) NOT NULL,
RegdNo varchar(255),
Age int,
City varchar(255) DEFAULT ’BBSR’
);

SQL DEFAULT on ALTER TABLE

ALTER TABLE Student#53 MODIFY Age DEFAULT 20;
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database more quickly than otherwise.
The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX Syntax:- Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax:- Creates a unique index on a table. Duplicate values are not
allowed:
CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);
CREATE INDEX Example
#-------For a single Column
Create Index Idx_Sname on Student#56 (Sname);

#------For Multiple column

Create Index idx_info on Student#56 (SName, Sid);

DROP INDEX Statement

Drop Index idx_Sname;