SQL Testing
DataBase
DBMS Vs RDBMS
SQL Tutorials
SQL DataTypes
SQL Operators
SQL Statements
DDL (Data Definition Language)
SQL-Create Table
SQL-Alter Table
SQL-Drop Table
SQL-Truncate Table
DML (Data Manipulation Language)
SQL-Select Statement
SQL-Insert Statement
SQL-Update Statement
SQL-Delete Statement
SQL Constraints
SQL-Not Null Constraint
SQL-Unique Constraint
SQL-Primary Key Constraint
SQL-Foreign Key Constraint
SQL-Check Constraint
SQL Special Operators
SQL-IN Operator
SQL-LIKE Operator
SQL-BETWEEN Operator
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
SQL Having
SQL Joins
SQL-Cross Join
SQL-Inner Join
SQL-Left Outer Join
SQL-RIGHT Outer Join
SQL-Self Join
SQL SubQueries
ISQL-Independent Sub Queries
SQL-Correlated Sub Queries
SQL Views
SQL Index
SQL Constraints | Types and Examples
SQL constraints are the rules which is applied to the data in tables.
- When the SQL constraints are not met by a data then INSERT/UPDATE/ DELETE etc. will be aborted.
Below are few SQL constraints:

1) NOT NULL Constraint
- It ensures column cannot store NULL value
- If column is specified as NOT NULL then ,null value can not be stored in this particular column .
Example –
CREATE TABLE Employee(
EmployeeID Number(6) NOT NULL,
Employee_FirstName VarChar2(30) NOT NULL,
Employee_LastName VarChar2(20),
)
2) UNIQUE Constraint
- Make sure that all the values in a column are unique
- UNIQUE constraint can be used either on multiple columns or on a single column with
- PRIMARY KEY constraint automatically has a UNIQUE constraint, to guarantee the uniqueness of a column.
- A table can have many UNIQUE constraints but can have a single primary key constraint.
Example:
CREATE TABLE Employee(
EmployeeID Number(6) NOT NULL UNIQUE,
Employee_FirstName VarChar2(30) NOT NULL,
Employee_LastName VarChar2(20)
);
3) PRIMARY KEY Constraint
- A combination of a NOT NULL and UNIQUE.
- No two rows in a table can have the same primary key value.
- NULL value cannot be entered in a primary key column.
Example:
CREATE TABLE Employee(
EmployeeID Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
Employee_FirstName VarChar2(30) NOT NULL,
Employee_LastName VarChar2(20),
)
4) FOREIGN KEY Constraint
- Ensure the referential integrity of the data in one table to match values in another table
- Creates a kind of link between the tables.
- It is also called as REFERENCE KEY.
- A table may have more than one FOREIGN KEY.
- FOREIGN KEY should not contain NULL values.
The following statement establishes a foreign key on the Dept_id column of the Employee table that references the Dept_id column of the Departments table.
CREATE TABLE Employee (
EmployeeID Number(6) CONSTRAINT Pkey1 PRIMARY KEY,
Employee_FirstName VarChar2(30) NOT NULL,
Employee_LastName VarChar2(20),
Dept_id INT CONSTRAINT Fkey1 REFERENCES Departments(dept_id)
);
5) CHECK Constraint
- Ensures that the value in a column meets a specific condition
- If the value does not match the condition, then it will not be inserted into a table
Check constraint on create table
Write a query to create a table Employee, with columns EmployeeID, Employee_FirstName, Employee_LastName and City and The city must be Bangalore.
CREATE TABLE Employee ( EmployeeID Number(6) CONSTRAINT Pkey1 PRIMARY KEY, Employee_FirstName VarChar2(30) NOT NULL, Employee_LastName VarChar2(20), City VarChar2(255) CHECK (City==’Bangalore’) );
CHECK constraint on multiple columns
Write a query to create a table Employee, with columns EmployeeID, Employee_FirstName, Employee_LastName and City .The City must be Mumbai, and the age of Employee >20.
CREATE TABLE Employee ( EmployeeID Number(6) CONSTRAINT Pkey1 PRIMARY KEY, Employee_FirstName VarChar2(30) NOT NULL, Employee_LastName VarChar2(20), Age int, City VarChar2(255) CONSTRAINT chk CHECK (City==’Mumbai’ AND Age>20));
Recommended Articles:
SQL Testing
DataBase
DBMS Vs RDBMS
SQL Tutorials
SQL DataTypes
SQL Operators
SQL Statements
DDL (Data Definition Language)
SQL-Create Table
SQL-Alter Table
SQL-Drop Table
SQL-Truncate Table
DML (Data Manipulation Language)
SQL-Select Statement
SQL-Insert Statement
SQL-Update Statement
SQL-Delete Statement
SQL Constraints
SQL-Not Null Constraint
SQL-Unique Constraint
SQL-Primary Key Constraint
SQL-Foreign Key Constraint
SQL-Check Constraint
SQL Special Operators
SQL-IN Operator
SQL-LIKE Operator
SQL-BETWEEN Operator
SQL Sorting (Order By)
SQL Group By
SQL Aggregate Function
SQL Having
SQL Joins
SQL-Cross Join
SQL-Inner Join
SQL-Left Outer Join
SQL-RIGHT Outer Join
SQL-Self Join
SQL SubQueries
ISQL-Independent Sub Queries
SQL-Correlated Sub Queries
SQL Views
SQL Index