Constraints in SQL are used to define rules for the table and columns in a database and also make sure that data are accurate and trusted in the database. Constraints are responsible for the termination of action if any violation is found.
Structured Query Language is used to manage the stored data of RDBMS.
We can define a constrains for existing table by ALTER TABLE or at time of new table creation.
Constraints are two types:
Column level: This type of constrains are applied only on a column
Table level: This type of constrains are applied only on whole table
Constraints are used to make sure that the unity of data is managed in the database. The most commonly used SQL constraints are described as below...
PRIMARY KEY constraint: A Primary key is a type of restriction which uniquely define each record in a database. It is a combination of a UNIQUE & NOT NULL constraint. It also helps to find a particular record from database thats why we make primary key as index.
#PRIMARY KEY constraint at Table Level
CREATE table users (uid int PRIMARY KEY, fist_name varchar(255) NOT NULL, mobile int);
#PRIMARY KEY constraint at Column Level
ALTER table users add PRIMARY KEY (uid);
FOREIGN KEY constraint: A Foreign key is basically used to create a relation between two tables.It is also used to restrict actions that would delete relation between tables.
#FOREIGN KEY constraint at Table Level
CREATE table user_detail(user_id int PRIMARY KEY, user_city varchar(255) NOT NULL, uid int FOREIGN KEY REFERENCES user(uid));
#FOREIGN KEY constraint at Column Level
ALTER table user_detail add FOREIGN KEY (uid) REFERENCES user_detail(uid);
NOT NULL constraint: NOT NULL constraint is a type of restriction which restricts a field to having a NULL value. It means NOT NULL field can not accept a null type value. NOT NULL constraint only defines at column level not a table level constraint.
#NOT NULL constraint
CREATE table user(uid int NOT NULL, first_name varchar(255), mobile int);
UNIQUE Constraint: An UNIQUE constraint is a type of restriction which restricts a field that will have only unique values that means UNIQUE constraint field will never accept duplicate data. It can be defined at column level or table level.
#UNIQUE constraint on table creation
CREATE table user(uid int NOT NULL UNIQUE, first_name varchar(255), mobile int);
#UNIQUE constraint at Column Level
ALTER table user add UNIQUE(uid);
CHECK constraint: CHECK constraint is used to apply a condition on field and it works like a condition which applied before saving data into field of table.
#CHECK constraint at Table Level
create table user(uid int NOT NULL CHECK(uid > 0), first_name varchar(255) NOT NULL, mobile int);
#CHECK constraint at Column Level
ALTER table user add CHECK(uid > 0);
DEFAULT constraint: It is used to set default value for all new records of a column into table, if no any value is passed.
CREATE table users (uid int PRIMARY KEY, fist_name varchar(255) NOT NULL, mobile int(11) DEFAULT '0000000000' );