SQL CONSTRAINTS

A constraint is a rule or limitation that we place on the data in a database that we can put into a column(s). A constraint is part of the table. we can apply constraints when we create the table or afterwards. Constraints could be apply to Column and to table also. Constraints that applied to column are called column level constraints whereas constraints that applied to table are known as table level constraints .we can apply column level constraints only to one column, but other than column level constraints we can apply table level constraints to the whole table.

SQL CONSTRAINT Syntax

CREATE TABLE table-name
(
column1 data-type(size) constraint-name,
column2 data-type(size) constraint-name,
column3 data-type(size) constraint-name,
....
);

Below are some commonly used constraints in SQL:-

1. NOT NULL
2. DEFAULT
3. UNIQUE Constraint
4. PRIMARY Key
5. FOREIGN Key
6. CHECK Constraint

NOT NULL

NOT NULL constraint denotes that we have to provide some value for that column . We cannot left it blank, we must have to provide some value to it.

Below is the SQL query which enforces the "employee-Id" column and the "Name" column to not accept NULL values:-

CREATE TABLE Employee Not Null ( employee-Id int NOT NULL, Name varchar(255) NOT NULL, Address varchar(255), City varchar(255) )

DEFAULT Constraint

By using DEFAULT constraint we can provide a default value for that particular column on which DEFAULT CONSTRAINT is apply.

If no other value is given than default value is apply to all the record by using Default constraint.

SQL DEFAULT Constraint syntax:-

Below query will create a default constraint on city field:-

CREATE TABLE Employee
(
employee-Id int NOT NULL,
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT 'Delhi'
)

SQL UNIQUE Constraint UNIQUE constraint is used to uniquely identified a field or column in a database system. The PRIMARY KEY and UNIQUE constraints both give a assurance for uniqueness of a column(s). It should be noted that their can be two or more than two unique constraint, but only one PRIMARY KEY constraint in a table.

SQL UNIQUE Constraint syntax Below SQL query will creates a UNIQUE constraint on the "employee-Id" column:-

CREATE TABLE Employee
(
employee-Id int NOT NULL UNIQUE,
Name varchar(255),
Address varchar(255),
City varchar(255)
)

SQL PRIMARY KEY Constraint The primary constraint is just like unique constraint both uniquely identifies a field or record in database, but the major difference is that primary key should not be null but unique constraint can be null.

SQL PRIMARY KEY Constraint syntax:-

Below SQL query will make a PRIMARY KEY on the "employee-Id" column ;-

CREATE TABLE Employee
(
employee-Id int NOT NULL,
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
PRIMARY KEY (employee-Id)
)

SQL FOREIGN KEY Constraint

FOREIGN KEY constraint is used to setup relationship between two tables in a database. A foreign key is a values that appear in both the tables in database system. A FOREIGN KEY in one table is reference of a PRIMARY KEY in another table.

SQL FOREIGN KEY Constraint syntax :-

Below SQL query will creates a FOREIGN KEY on the "employee-Id" column:-

CREATE TABLE Department
(
D-Id int NOT NULL,
D-name
employee-Id int,
PRIMARY KEY (D-Id),
FOREIGN KEY (employee-Id) REFERENCES Persons(P-Id)
)

SQL CHECK Constraint

If we define a CHECK constraint for only to one column it allows only a particular values for that column.

SQL CHECK Constraint syntax:- The following SQL creates a CHECK constraint on the "employee_Id" column. The CHECK constraint describe that the column "employee_Id" should only have values those are greater than 0.

 CREATE TABLE employee
    (
    employee-Id int NOT NULL,
    Name varchar(255),
    Address varchar(255),
    City varchar(255),
    CHECK (employee-Id>0)
    )