Constraints are rules to control the data in a column. SQL constraints are rules applied to columns or tables to enforce data integrity and limit the type of data that can be stored. These constraints help maintain accuracy and reliability in the database by ensuring that the data adheres to specified rules.
Common types of constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK and AUTO INCREMENT.
Constraints can be defined at the time of table creation using the CREATE TABLE statement or added later using the ALTER TABLE statement.
Importance of Constraints
- Constraints help maintain data integrity and accuracy in the database.
- They prevent invalid data entry, ensuring that the data adheres to the defined rules, which is crucial for reliable database operations.
Not Null
The NOT NULL constraint ensures that a column cannot have a NULL value. It ensures that the user always type the data for that column.
NOT NULL ensures that a column cannot contain NULL values, meaning it must always have a value.
Example:
create table student( rollNo int(4) primary key, name varchar(15) not null, address varchar(10));
Unique:
Ensures that all values in a column are different. It prevents duplicate entries. i.e., no duplicate values are allowed.
Example:
create table student( rollNo int(4) primary key, name varchar(15), address varchar(10), pin varchar(8) unique);
Primary Key:
A combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table and cannot contain NULL values. The primary key constraint ensures that the column cannot be null and the values in the column will be unique.
Example:
create table student( rollNo int(4) primary key, name varchar(15), address varchar(10));
Default
Default constraint is used to define default values for a column. It provides a default value for a column if no value is specified during insertion.
Example:
create table student( rollNo int(4) primary key, name varchar(15), address varchar(10) default ‘Tinsukia’);
insert into student(rollNo, name) values(1, ‘Eliza’);
Check
The check constraint ensures that all values in a column satisfy a specific condition. It ensures that when the data is entered, the data in the column is limited to specific values.
create table employee(emp_id int(4) primary key,Name varchar(20), age int(3) check(age>=18));
Auto Increment
Auto Increment constraint autoincrementing a value of a field in the table. Note: autoincrement can only be used with integer primary key.
create table student( rollNo integer(4) primary key autoincrement, name varchar(15), address varchar(10));
insert into student(name, address) values(‘Eliza’,’Tinsukia’);
insert into student(name, address) values(‘Moidur’,’Dibrugarh’);