SQL Constraints specifies the rules/limitations/restrictions for data present in table. SQL Constraints are specified at the time of table creation or after table creation using ALTER command. There are two types of constraints
column-level constraint and
table-level constraint.
As the name column level specifies, it specifies a particular column. In a table level constraint, it specifies more than one column or all columns in the table.
NOT NULL: It ensures that specified column value cannot be NULL.
UNIQUE: It ensures that each value in the column is different.
PRIMARY KEY: Primary key ensures both NOT NULL and UNIQUE. It means value cannot be null and it should be different from each other. Therefore, primary key uniquely identifies each record in the table.
FOREIGN KEY: It uniquely identifies record from other table. Foreign key is a reference to the other table and it is like Primary key from other table.
DEFAULT: It sets default value when value is not given.
CHECK: It ensures that all values in the column satisfy a condition.
INDEX: Index is created and used to retrieve data from database.
NOT NULL
Default value for every column is NULL. Not Null constraints ensures column value should not null. This constraint always make sure column has some value. It does not allow inserting or updating record without mentioning value to this field.
User can add this constraint to single or multiple or all columns of the table.
Syntax:
CREATE TABLE table_name (column 1 data type NOT NULL,………);
Example:
Create table STUDENT
(student_id int NOT NULL,
student_name varchar(10),
student_dept varchar(10) NOT NULL,
classyear int);
In the above example,
NOT NULL constraint is applied on two columns student_id and student_dept in STUDENT table while creating table. Description of STUDENT table shows which columns can be NULL or cannot be NULL. See the output screen at the time of inserting values in table, not null constraint does not allow entering NULL value to those fields on which constraint is applied.
UNIQUE
Unique constraint avoid duplicate entries in column. One table can have multiple unique keys.
Syntax:
CREATE TABLE table_name (column 1 data type UNIQUE,………);
Example:
create table student
(student_id int Unique,
student_name varchar(10) unique,
student_dept varchar(10) not null,
classyear int );
You can write above query in other form.
Create table student
(student_id int,
student_name varchar(10) unique,
student_dept varchar(10) not null,
classyear int ,
unique (student_id));
# define multiple columns unique in one time
Syntax:
CREATE TABLE table_name
(column 1 data type, ……… column n, constraint constraint_name Unique (column1,..));
Example:
create table student
(student_id int,
student_name varchar(10) unique,
student_dept varchar(10) not null,
classyear int ,
Constraint cs_student
unique (student_id, student_name));
# Add UNIQUE Constraint Using ALTER Statement
Syntax:
Alter table table_name
Add Unique (column_name);
Example:
Alter table Student
Add Unique (student_id);
Syntax:
Alter table table_name
Add Constraint constraint_name Unique (column1,column2);
Example:
Alter table Student
Add Constraint cs_student Unique (student_id,student_name);
Primary Key
Primary key uniquely identifies each record in the column, one table have single Primary key.
Primary key can be a single column or group of columns.
Syntax:
Create table table_name
( column data type Primary key,
column2 data type,………);
Example:
Create table STUDENT
(student_id int Primary Key,
student_name varchar(20),
student_dept varchar(20),
classyear int);
Syntax:
Create table table_name
( column1 data type,
column2 data type,………
column n data type,
Primary key (column_name));
Example:
Create table Student
(student_id int,
student_name varchar(10),
student_dept varchar(10),
classyear int,
Primary key (student_id));
# Using ALTER Statement
Syntax:
alter table table_name
add primary key (column_name);
Example:
alter table student
add primary key (student_id);
Syntax:
alter table table_name
drop primary key;
Example:
alter table student
drop primary key;
DEFAULT
The DEFAULT constraint is used to set the default value to the column. This constraint can be applied on single column or multiple columns. NULL is a default value to all the columns in table. When user apply not null or primary constraint on any column then its default value is changed.
Syntax:
Create table table_name (
Column data type,…….
Column data type default value);
Example :
Create table student (
student_id int,
student_name varchar(20),
student_dept varchar(20) default 'IT',
classyear int);
Output screen shows default value
‘IT’ assigned to column student_dept.
‘IT’ value set to the column
student_dept when no value is provided for the column
student_dept.
# Adding DEFAULT constraint on column using ALTER Statement
Syntax :
alter table table_name
alter column_name
set default value;
Example :
alter table student
alter student_dept
set default 'CS';
Syntax:
alter table table_name
alter column_name
drop default;
Example:
alter table student
alter student_dept
drop default;
Foreign Key
Foreign key is a link between two tables. Primary key of one table can be reference/foreign key of another table. The table, which contains foreign key, called as child table and other table whose primary key referred as reference that table be a parent table. User has to enter records in parent table before child table if there no data present in parent table. One table can have single primary key.
DEPARTMENT Table
| DEPT_ID |
DEPT_NAME |
DEPT_HEAD |
DEPT_LOC |
| 0001 |
IT |
Mr. S.K. Mishra |
06 |
| 0002 |
CS |
Mr. D.P. Patil |
04 |
| 0003 |
EXTC |
Mrs. S.K. Bhosale |
05 |
| 0004 |
MECH |
Mr. A.B. Jha |
07 |
STUDENT Table
| STUDENT_ID |
STUDENT_NAME |
DEPT_NAME |
CLASSYEAR |
| 10001 |
RAM |
IT |
02 |
| 10002 |
KIRAN |
IT |
04 |
| 10003 |
NEHA |
CS |
01 |
| 10004 |
ROOPA |
MECH |
01 |
STUDENT
Primary key student_id
Foreign key dept_name
DEPARTMENT
Primary key dept_name
Here, DEPARTMENT is parent table and STUDENT is child table. Primary key of DEPARTMENT table referenced as foreign key in student table named as dept_name.
It may be possible that two table can have column with same name. Suppose in student table dept_name is present with different name like student_dept or anyting it does not create any problem. Table may contain one or more foreign key.
# Apply constraint while table creation
Syntax:
// Parent table must contain a primary key
Create table table_name
(column data type Primary key,
Column ……..,
Column n data type);
//child table contain foreign key
Create table table_name
(column data type,
…………,
Foreign key(column_name)
References parent_table_name(column_name of primary key));
Example:
Create table department
(dept_id int,
dept_name varchar(30) Primary key,
dept_head varchar(50),
dept_loc int);
Create table student
(student_id int primary key,
student_name varchar(30),
dept_name varchar(30),
classyear int,
Foreign key (dept_name)
References department (dept_name));
# Apply foreign key constraint using Alter statement
Syntax:
alter table table_name
add foreign key (column_name)
references parent_table_name (column_name of primary key);
Example:
alter table student
add foreign key (dept_name)
references department(dept_name);
Syntax:
alter table table_name
add constraint constraint_name
foreign key (column_name)
references parent_table_name (column_name of primary key);
Example:
alter table student
add constraint fk_student
foreign key (dept_name)
references department(dept_name);
# DROP constraint
Syntax:
alter table table_name
drop foreign key constraint_name;
Example:
alter table student
drop foreign key fk_student;
Create table student (student_id int, student_name varchar(50) not null, student_dept varchar(30) default 'IT',classyear int check (classyear<5 and classyear >0), primary key (student_id));
