How to use Constraints?

How To Use Constraints In SQL

Introduction:
In this article, you will learn what constraints are?

Constraints are used to define the guideline of the data. We use generally these constrains in SQL server:

• Not Null Constraint

• Default Constraint

• UNIQUE Constraint

• Primary key Constraints

• Foreign key Constraint

• Index Constraints

• Check Constraint

Not Null Constraint :

In SQL every column is automatically defined as a Null value. We can change this, for defining 'Not Null' in the Queries of SQL.

Here, we create a table of Employee with Not Null Constraint.

create table emmployee(

Id int not null

name varchar(..) not null,

address char(..),

salary decimal (..),

primary key (Id)

);

Default Constraint :

This constraint provides a default value to a column, when our statement does not provide the specific value.

Syntax:

create table employee(

Id int not null,

name varchar (..) not null,

Address char (..) ,

salary deccimal (..),

primary key (Id)

);

Drop Default Constraint:

When we want to drop a default constraint, use the syntax, given below:

Syntax :

table customer alter column salary drop default;

UNIQUE Constraint:

Unique Constraint has two same records in one table.

Here, we set address as a unique constraint. Thus, it includes two records:

Syntax :

create table employee(

Id int not null,

Name varchar (..) not null,

Address char (..) not null unique ,

Salary decimal (..),

primary ker (Id)

);

We also use this syntax, with naming constraint in many columns:

Syntax :

alter table employee add constraint myUniqueConstraint unique(age, salary);

Drop a Unique Constraint:

To drop a unique constraint, use the SQL, given below:

Syntax :

alter table employee drop constraint myUniqueConstraint;

Primary key Constraints:

In our SQL tables, Primary Key accept unique value and it not accept any null value in the column. A primary key is separately describe in each row in the SQL tables. In one table we set only one Primary Key it is possible that it accept many fields.

• Create Primary Key:

Here, we set Id as a Primary Key.

Syntax :

create table employee(

Id int not null,

name varchar (..) not null,

age int not null,

salary decimal (..),

primary key (Id)

);

If, we use Primary Key in many columns then use this syntax:

Syntax :

create table employee(

Id int not null,

name varchar (20) not null,

age int not null,

salary decimal (..),

primary key (Id, name)

);

• Delete Primary Key:

If, we want to delete a primary key in our table then use this syntax.

alter table employee drop primary key;

Foreign key Constraint :

Foreign Key is used to join the two tables. In the single database tables which columns value are primary key that is foreign Key column. Foreign Key is also know as referencing key.

Here, we create two tables is as follows:

Employee table:

Syntax :

create table employee(

Id int not null,

name varchar (20) not null,

age int not null,

salary decimal (18, 2),

primary key (Id)

);

Order table:

Syntax :

create table order (

Id int not null,

Date datetime,

employee_Id int references employee(Id),

amount double,

primary key (Id)

);

• Drop a Foreign Ker Constraint:

If, we want to Drop a Foreign Key then use this syntax.

Syntax :

alter table orders drop foreign key;

Check Constraint :

With the help of Check Constraints we check the value of all tables in database according to our condition. For Check Constraint we use this syntax.

Syntax :

create table employee(

Id int not null,

name varchar (..) not null,

age int not null check (condition),

salary decimal (18, 2),

primary key (ID)

);

• Drop a Check Constraint:

If, we want to Drop the Check constraint then use this syntax.

Syntax :

alter table customer drop constraint myCheckConstraint;

Index Constraints:

Index Constraint is use to find the data easily in multi tables. We does not see the index in SQL, we see only its work. With the help of constraints we done our work easily and fastly.

Syntax :

create table employee(

Id int not null,

name varchar (..) not null,

age int not null,

salary decimal (..),

primary key (Id)

);

Now, you can create index on single or multiple columns using the following syntax:

create index index_name on table_name ( column1, column2.....);

Drop an Index Constraint:

To drop an Index constraint, use the following SQL:

alter table employee drop index _age;

Summary:
Thus, we learned that how define the guideline of data in SQL.