Constraint in sql | Sql Constraints

Constraints in sql | Sql Constraints

Constraints are the rules that we can apply on the type of data in a table.

For Practice Question www.techquestions.in

There are 6 constraints in sql.

1. Primary key

2. Foreign key

3. Unique 

4. Default

5. Check

6. Not Null

Primary key : A  primary key is a column or a group of  columns that uniquely identifies each row in a table.

How can we define primary key with create command

Method 1: 

create table emp

( empid int primary key, empname varchar(20));

Method 2:

create table emp (empid int, empname varchar(20), primary key(empid));

Method 3:

create table emp(empid int,empname varchar(20), Constraint pk primary key(empid));

primary key with alter command

Method 1.

Alter table emp

add primary key (empid);

Method 2.

Alter table emp

Add constraint pk primary key(empid);

How to drop a primary key

Alter table emp

drop primary key

Note: As we know that a single table can have only one primary key so that for deleting a primary key we do not need to mention the column name.

Screenshot:

Primary Key in sql

Foreign Key :

 A foreign key refers to a primary key in another table. A foreign key is a column in a table where that column is a primary key of another table, which means that any data in a foreign key column must have corresponding data in the other table where that column is the primary key. Foreign key is also known as referential integrity.

• A table can have more than one foreign key.

• Foreign key column can have duplicate values.

How can we define foreign key with create command

create table employee

( employeeid int primary key, employeename varchar(20));

create table department
(departmentid int prmary key, 
empid int,
foreign key(empid) references employee(employeeid)
);

or

create table department
(departmentid int prmary key, 
empid int,
constraint fk foreign key(empid) references employee(employeeid)
);

Foreign key with on delete cascade on update cascade

create table department
(departmentid int prmary key, 
empid int,
foreign key(empid) references employee(employeeid) on delete cascade
);

Note: The foreign key in this case uses ON DELETE CASCADE to specify that when a record is deleted from the parent table, child records with a matching employeeid value should be removed automatically as well.

create table dept
(deptid int prmary key, 
empid int,
foreign key(empid) references employee(employeeid) on delete cascade on update cascade

);

Note: If you write ON UPDATE CASCADE also, while defining foreign key constraint then in case a row is updated in parent table, all its related rows in the child table will automatically be updated.

Foreign key with on delete cascade on update cascade

As see above.. After inserting values both the tables. Lets check update cascade how works.

We have these two tables with given values.

Emp table and EmpDept table before on update cascade

We update the value of employeeid from 101 to 1101.

Emp table and EmpDept table after on update cascade.

Foreign key with alter command

Alter table dept

add foreign key(employeeid)
references employee(employeeid);

Alter table dept

add constraint fk foreign key(employeeid)
references employee(employeeid);

How to drop a foreign key

Alter table emp

drop foreign key fk;

Unique Key | Unique Constraint : This constraint ensures that no two rows have the same value in the specified columns.

A column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key. 

We can apply unique constraint more than one column. It means multiple unique constraints can be defined on a table.

• A table can have more than one unique constraint.
• Unique Constraint is almost similar to primary key. Primary key does not have duplicate and null values whereas Unique key does not have duplicate values but can have null values.

How to create Unique key ?

Create table students(

Rollno int primary key,

admissionNumber int unique,

name varchar(25));

Create table staff
(
teacher_id int,
teacher_name varchar(30),
contact_number varchar(12),
constraint uc unique(contact_number)
);

 We can also create multiple unique constraints in a one table.

Create table staff
(
teacher_id int unique,
teacher_name varchar(30),
contact_number varchar(12) unique
);

How to create Unique key with alter command ?

Alter table staff

add  unique(teacher_id);

or

Alter table staff

add constraint uc unique(teacher_id);

How to drop Unique Constraint ?

You can drop a Unique Constraint with the 'Index' keyword if and only if you have used constraint name.

Alter table staff

drop index uc ;      // Here 'uc' is  the unique constraint name.

Default Constraint

A default value can be specified for a column using the Default clause. When a user does not enter a value for the column(having default value), automatically the defined default value is inserted in the field.

Create table employee

(

ecode int primary key,

ename char(20) ,

grade char(2) default 'B'

);

Note According to the above command , if no value is provided for grade, the default value of 'B' will be entered. The datatype of the default value has to be compatible with the datatype of the column to which it is assigned. Insertion of NULL (as default value) is possible only if the column definition permits.Not Null columns cannot have NULL as default. A column can have only one default value. 

How to create Default Constraint with alter command ?

Alter table employee

alter grade set default 'B';

How to drop default constraint.

Alter table employee

alter grade drop default;

Note you can apply default constraint more than one in a table if you want to apply Default Constraint on Integer type column you have to write as 

Create table employee

(

ecode int default 0,

ename char(20) ,

grade char(2) default 'B'

);

Check Constraint

A Check  constraint controls the values in the associated column.It determines whether the value is valid or not from a logical expression.

It limits values that can be inserted into a column of a table. 

How to create Check Constraint ?

Create table items

(

icode int primary key,

descprition varachar(20),

qty int Check (qty>2000),

price int

);

or 

Create table items

(

icode int primary key,

descprition varachar(20),

qty int ,

price int,

Check (qty>2000)

);

or

Create table voter

(

id int,

name varchar(20),

age int,

city varchar(20),

constraint chk check(age>=18 and city='Delhi')

);

You can apply more than one check constraint in a table.

Create table product

(

pcode int primary key,

pname varchar(20),

price int,

qty1 int,

qty2 int,

check (qty1>qty2)

);

This statement compares two columns qty1 and qty2 , thus, these two columns must be defined before the check constraint.

Check Constraint can be consist of :

• A list of consist expressions specified using IN 

       Create table product

       (

        code int,

       price int,

       city varchar(20), 

       qty int,

       check(city in('Delhi','Mumbai','Agra')) 

        );

• Range of constant expressions specified using BETWEEN. The upper and lower boundary values are included in the range.

       Create table product

       (

        code int,

       price int, 

       city varchar(20)  

       qty int,

       check(price between 500 and 1000)

        );

• A pattern specified using LIKE .

       Create table product

       (

        code int,

       price int,

       city varchar(20)  

       order char(10) ,

       check (order like '--%')

        );

• Multiple conditions using OR,AND etc.

       Create table product

       (

        code int,

       price int,

       city varchar(20),  

       qty int,

       check(city='delhi' and price>500)

        );

How to create Check Constraint with alter command ?

Alter table voter

add check(age>=18);

or 

Alter table voter

add constraint chk check(age>=18);

How to drop Check constraint.

Alter table employee

drop check chk;

Here chk is a Check Constraint name.


Note:All the queries are executed without checking the condition specified through CHECK constraint.
The reason being Mysql ignores CHECK constraint internally.

Not Null Constraint

A column with a NOT NULL constraint cannot have null values. This constraint ensures that the null values(empty values) are not permitted for a specified column. This constraint can be defined at the column level but not at the table level.

By default, Null is assumed, and null values are permitted in the column.

How to create NOT NULL Constraint ?

Create table Book

(

id int not null,

Title varchar(20) not null,

price int

);

How to use NOT NULL Constraint with alter command ?

Alter table Book

modify price int not null;

How to drop Check constraint.

You can drop NOT NULL constraint directly , you can modify it.

Alter table book

modify price int;