Constraints
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.
Constraints can be column level or table level. Column level constraints apply
to a column, and table level constraints apply to the whole table.
Types of SQL Constraints
1. NOT NULL
Ensures that a column cannot contain NULL values. Every row must have a
value in this column.
It is used to avoid null values:
Commonly used for mandatory fields like name, ID, etc.
Null means empty space if user doesn’t pass any data into cell, it will considered
as null value
EXAMPLE:
CREATE TABLE Employees (
EmpID INT,
FullName VARCHAR(100) NOT NULL
);
2. UNIQUE Constraint
Ensures that all values in a column (or set of columns) are different.
Key Points:
Prevents duplicate entries in the column.
Allows only one NULL value
Example:
CREATE TABLE Employees (
EmpID INT,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE
);
3. PRIMARY KEY Constraint
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).
Example:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE
);
Can be single or composite (multiple columns)
Composite Primary Key Example:
CREATE TABLE CourseRegistrations (
StudentID INT,
CourseID INT,
RegistrationDate DATE,
PRIMARY KEY (StudentID, CourseID) -- Composite Primary Key
);
4. FOREIGN KEY Constraint
A FOREIGN KEY constraint links a column in one table to the primary key in
another table.
This relationship helps maintain referential integrity by ensuring that the
value in the foreign key column matches a valid record in the referenced
table.
Can accept NULL if not marked as NOT NULL.
Example:
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);
Modified Employees Table:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID)
);
5. CHECK Constraint
The CHECK constraint allows us to specify a condition that data must satisfy
before it is inserted into the table.
(Or)
The CHECK constraint is used to limit the value range that can be placed in a
column.
I condition I satisfied it allows to insert the data, else it will reject the data
Example:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE,
Age INT CHECK (Age >= 18 AND Age <= 60)
);
5. DEFAULT Constraint
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.
Example:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
DATATYPES
Data type is used to specify the type of date to be stored in the table column.
It used while creating a table.
Data types are mandatory while creating a table.
Types
1. Char
2. Varchar/ varchar2
3. Numeric(p,s)
4. Date
5. Large Objects
1. Char
It helps to store alpha numeric values along with some special
character [‘A-z’,’a-z’,0-9, !,@,#,$,&]
It Stores up to 2000 characters. Characters must be enclosed within
(‘ ‘) single quotes.
It follows fixed length memory allocation.
When the size is fixed, we can store till the size mentioned , if we try
to store more than that their may be data loss.
If we try to store less than the given size, there will be memory loss.
Syntax:
Column-name char(size)
Ex: cfsc char(10)
2. Varchar / varchar 2
It helps to store alpha numeric values along with some special
character [‘A-z’,’a-z’,0-9, !,@,#,$,&]
It Stores up to 2000 characters. Characters must be enclosed within
( ‘ ‘ )single quotes.
It follows variable length memory allocation.
When the size is fixed, we can store till the size mentioned , if we try to
store more than that there may be data loss.
If we try to store less than the given size, there will be no memory
wastage.
Syntax: Column-name varchar(mention-size)
Ex: cfsc Varchar(10)
3. Number
Used to Store numeric values
Syntax: number (p,s)
Precision: Used to determine the number of values stored as integer
value.
Scale :used to determine the number of values stored after decimal.
E.g.: number(5,10)
4 Date
Used to store date type of data
Syntax; DD-MM-YYYY
E.g.: 18-Jun-2023
5.Large objects
1.CLOB [Character Large Objects]
2. BLOB [Binary Large Objects]
1. Character large objects:
It helps to store the textual information that are too long.
It can store up to 4gb
Ex: Script, Xml etc.
2. Binary Large object
It helps to Store the images, mp3, mp4 and gif of binary
values in binary stream.
It can store up to 4gb
