United Institute of Management, Prayagraj
Master of Computer Application
DBMS (KCA 204)
UNIT -1
SQL | Constraints
In a database table, we can add rules to a column known as constraints. These
rules control the data that can be stored in a column.
Constraint Description
NOT NULL values cannot be null
UNIQUE values cannot match any older value
PRIMARY KEY used to uniquely identify a row
FOREIGN KEY references a row in another table
CHECK validates condition for new value
DEFAULT set default value if not passed
Note: These constraints are also called integrity constraints.
NOT NULL Constraint
The NOT NULL constraint in a column means that the column cannot store NULL values. For
example,
CREATE TABLE Colleges (
college_id INT NOT NULL,
college_code VARCHAR(20) NOT NULL,
college_name VARCHAR(50)
);
Mr Ashutosh Pandey, UIM-MCA Page 1
Here, the college_id and the college_code columns of the Colleges table won't
allow NULL values.
UNIQUE Constraint
The UNIQUE constraint in a column means that the column must have unique value. For
example,
CREATE TABLE Colleges (
college_id INT NOT NULL UNIQUE,
college_code VARCHAR(20) UNIQUE,
college_name VARCHAR(50)
);
Here, the value of the college_code column must be unique. Similarly, the value
of college_id must be unique as well as it cannot store NULL values.
PRIMARY KEY Constraint
The PRIMARY KEY constraint is simply a combination of NOT
NULL and UNIQUE constraints. It means that the column value is used to uniquely identify the
row. For example,
CREATE TABLE Colleges (
college_id INT PRIMARY KEY,
college_code VARCHAR(20) NOT NULL,
college_name VARCHAR(50)
);
Here, the value of the college_id column is a unique identifier for a row. Similarly, it cannot
store NULL value and must be UNIQUE.
FOREIGN KEY Constraint
The FOREIGN KEY (REFERENCES in some databases) constraint in a column is used to
reference a record that exists in another table. For example,
CREATE TABLE Orders (
Mr Ashutosh Pandey, UIM-MCA Page 2
order_id INT PRIMARY KEY,
customer_id int REFERENCES Customers(id)
);
Here, the value of the college_code column references the row in another table
named Customers.
It means that the value of customer_id in the Orders table must be a value from the id column of
the Customers table.
CHECK Constraint
The CHECK constraint checks the condition before allowing values in a table. For example,
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
amount int CHECK (amount >= 100)
);
Here, the value of the amount column must be greater than or equal to 100. If not, the SQL
statement results in an error.
DEFAULT Constraint
The DEFAULT constraint is used to set the default value if we try to store NULL in a column.
For example,
CREATE TABLE College (
college_id INT PRIMARY KEY,
college_code VARCHAR(20),
college_country VARCHAR(20) DEFAULT 'US'
);
Here, the default value of the college_country column is US.
If we try to store the NULL value in the college_country column, its value will be US.
**********
Mr Ashutosh Pandey, UIM-MCA Page 3
