SQL_Constraints

SQL Constraints

SQL constraints are used to make sure that the column, in whichever the constraint has been used, is using the right data as per  the constraint.

Below are the most common SQL constraints:

•           NOT NULL – When the column is marked as NOT NULL, then the column can not have NULL values, you will not be allowed to enter any null values.

•           UNIQUE – When the column is marked as UNIQUE, the column can not have any duplicate values.

•           PRIMARY KEY – The primary key column can not have duplicate or Null values. It is a combination of NOT NULL and UNIQUE.

•           FOREIGN KEY – The Foreign Key column references the Primary Key column and can have values from the primary key columns only. Foreign Key column can have NULL values.

•           CHECK – This constraint makes sure that the value of the column is as specified in the CHECK constraints.

•           DEFAULT – This constraint makes sure that if no value is entered, then the default value will be used.

 

The constraints have been explained in below examples.

For Primary Key and the Secondary Key, please click here .

Create table TableConstraintTest(

Name Varchar2(20) Unique,

Dept Varchar2(10) Not Null,

Salary Int Check(Salary>1000),

State Varchar(5) Default 'TX'

);

 

select * from TableConstraintTest;

Name     Dept             Salary      State

Murali    Engineer      1500        TX

 

Let’s try to insert duplicate name as below:

Insert into TableConstraintTest(Name, Dept, Salary, State) values ("Murali", "Engineer2", 1600, 'TX');

We get below error:

Error 1: could not execute statement due to a constraint failure (19 UNIQUE constraint failed: TableConstraintTest.Name)

 

Let’s try to add NULL for the Dept:

Insert into TableConstraintTest(Name, Dept, Salary, State) values ("Ram", Null, 1600, 'TX');

We get below error:

Error 1: could not execute statement due to a constraint failure (19 NOT NULL constraint failed: TableConstraintTest.Dept)

 

Let’s try to Salary less than 1000:

Insert into TableConstraintTest(Name, Dept, Salary, State) values ("Ram", "Mechanical", 900, 'TX');

We get below error:

Error 1: could not execute statement due to a constraint failure (19 CHECK constraint failed: Salary>1000)

 

Constraints can be used in a different way also as below:

create table TableConstraintTest(

Name Varchar2(20) ,

Dept Varchar2(10) ,

Salary Int ,

State Varchar(5) ,

Check (Salary>1000 and State = “TX”)

);

OR

We can give name to the constrain as below:

create table TableConstraintTest(

Name Varchar2(20) ,

Dept Varchar2(10) ,

Salary Int ,

State Varchar(5) ,

Constraint SalaryAndStateRule Check (Salary>1000 and State = “TX”)

);

 

We can add constraints in the already existing table as below:

Suppose we have to add constraints to an existing table TableConstraintTest1,

Alter TableConstraintTest1

Add Check (Salary>1000);  OR

Add Constraint SalaryConstraint Check (Salary>1000); 

 

We can DROP the constraint as below:

Alter TableConstraintTest

Drop Constraint SalaryConstraint ;