SQL CONSTRAINTS

tutorial and example
tutorial and example

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));