SQL Constraints are the rules defined on tables and columns in SQL Server to maintain data correctness and consistency of data stored in database.
Constraints are parameters that are used while creating table to define column validation to accepts the values in column. and maintain accurate data.
Table of Contents
What is Constraint?
SQL constraints are used to prevent invalid data entry, duplicate data and maintain data uniqueness, data accuracy, checking valid data etc.
Constraints are parameters that are used while creating table to define column validation to accepts the values in column. and maintain accurate data.
SQL constraints
SQL constraints in dbms
SQL constraints with example
what is SQL constraints?
SQL constraints in MySQL
SQL constraints with examples
what are sql constraints
SQL constraints examples
Let’s see All SQL constraints in detail with example.
Types of Constraints
following are the types of SQL constraints.
1.Primary Key
2.Foreign Key
3.Unique
4.NOT NULL
5.Check
6.Default
Let’s Check all SQL constraints one by one in Details with example.
Primary Key Constraint:
The Primary key constraint is used to maintain Unique values in the table.
Primary Key will oppose to insert duplicate data in to the table.Primary Key defines each records stored in the table is unique or distinct.
This constraints will oppose to insert null values and same values in to the table.
If you insert duplicate value in table with column defined as primary key, you will see a error like
‘Violation of PRIMARY KEY constraint ‘Table_namepKey’. Cannot insert duplicate key in object ‘dbo.Table_name’.
The duplicate key value is your value’.
Let’s see Example of Primary key in SQL server
Primary Key Example:
CREATE TABLE Developer (
Dev_ID int Primary key,
Dev_name varchar(50),
Dev_address varchar(50),
Dev_Designation varchar (50),
Dev_DOB date
);In this example, the Dev_ID column is defined as the primary key. Dev_ID column must have unique record to insert, if there is duplicate value it will not accept or insert data in Developer table.
SQL Sequence | SQL Sequence 2 Easy Examples
Foreign Key Constraint:
Foreign Key will create relationship with Table_one and Table_two.
Foreign Key constraint establish relational connection between two or more tables in SQL.
The Foreign key can link one table with another table, using first tables foreign key column can link with another table’s primary key column or unique key column.
Foreign Key Example:
CREATE TABLE Dev_KYC (
Dev_Kyc_ID int,
Dev_name varchar(50),
Dev_adharId nvarchar (12),
Dev_PAN nvarchar(10),
Dev_voterId nvarchar(10)
FOREIGN KEY (Dev_Kyc_ID) REFERENCES Developer(Dev_ID)
);In this example, the Dev_Kyc_ID column in the Dev_KYC table references the (Dev_ID) column in the Developer table.
In Above example Dev_Kyc_ID (Foreign key) from the Dev_KYC linked with the (Dev_ID) (primary Key) in the Developer table.
UNIQUE Constraint:
The UNIQUE constraint guarantees that all values in a column EmpolyeeID are different, except for NULL values.
These Unique constraints avoid inserting duplicate values like Primary key constraints.
Constraints allow us to insert unique values in the column EmpolyeeID.
UNIQUE Constraint Example:
CREATE TABLE Employees (
EmployeeID INT UNIQUE,
Name VARCHAR(50),
Department VARCHAR(50)
);
CREATE TABLE Dev_Trans (
Dev_ID int UNIQUE ,
Dev_Salary float(10),
Dev_Pay_month date,
Dev_DOJ date,
Dev_RetireDate varchar,
);
Here Dev_ID column shold have unique data for every developer data in Dev_Trans table.
UNIQUE constraint will help us to avoid inserting duplicate value and same Dev_ID values.
NOT NULL Constraint:
The NOT NULL constraint ensures that a column does not contain NULL values.
If we apply NOT NULL constraints to columns while creating the new table, it will not accept null values while inserting or not accept blank value. you must have to specify the value to column.
If you specify the NULL constraint while creating table, then your column will accept null values or not validate for blank values.
NOT NULL Constraint Example:
CREATE TABLE Dev_Edu (
Edu_RollNo INT PRIMARY KEY,
Edu_Clg_name VARCHAR(50) NOT NULL,
Edu_branch DECIMAL(10) NOT NULL,
Edu_university varchar(102) NULL
);In this case, both Edu_Clg_name and Edu_branch columns must contain values; they cannot be left empty.
Here we used NULL and NOT null constraints, NULL constraints will accept empty columns data whereas NOT NULL will not accept blank or empty data.
if you are not specifying any constraints the column can accept null values default.
In above example Edu_university is null and can have blank or null values , and Edu_Clg_name Edu_branch these both the columns must have a value it cannot be accepted empty or blank or NULL records in it.
Check Constraint:
The Check constraint specifies a condition that must be satisfied for the values entered into a column.
The Check Constraints defines condition which should be true for values entered into a columns.
Check constraints used to verify the values entered into a column, if the vales are not met to be specified Condition, then it will not accept the value to insert.
Check constraint Example:
CREATE TABLE Dev_personal (
Dev_Per_ID int,
Dev_Age int Check (Dev_Age>25),
Dev_Email nvarchar (12),
Dev_Mobile int check (len(Dev_Mobile)=10),
Dev_Phone nvarchar(10)
);
In this example, the Dev_Age column must have values greater than or equal to 25. and Dev_Moble should contain values who’s length equal to 10. it will not accept less thyan 10 or greater than 10.
In above example, we have defined Check constraints for column Dev_Age as Age should be Greater than 25, if we insert data into Dev_Age column less than 25 it will show error like below.
‘The INSERT statement conflicted with the CHECK constraint “CK__Dev_perso__Dev_A__1995C0A8”. The conflict occurred in database “DB_Name”, table “dbo.Dev_personal”, column ‘Dev_Age’.’
In Other column Dev_Mobile we have given condition where length of mobile number should be equal to 10,If you are inserting data in Dev_mobile column with Less than 10 or greater than 10 length then it will shows the same error like Dev_age Check Constraints.
You can define Checks as per your requirements in columns while creating table or you can also alter table to add constraints using alter table SQL command.
Default Constraint:
Default constraint can be used to put default values in column. you don’t need to specify the value for column while inserting, for column which is defined as default constraint.
Value in default constraint column will automatically be inserted into the column which is defined.
Default Constraint Example:
CREATE TABLE Dev_info (
ID INT PRIMARY KEY,
Title VARCHAR(50),
Name varchar(50),
Estd_Date date default '07-Oct-1993'
);Here, we have created table with column name ‘Estd_date’ which has given default establishment date ’07-oct-1993′, so here you don’t need to specify value for column Estd_date. This column will take default value ’07-oct-1993′ or every row.
FAQ Constraint in SQL
Q. What are SQL Server constraints?
Ans: SQL Server constraints are used to define or apply rules on specific column to maintain data accuracy data correctness and consistency.
Q. Why are constraints important in SQL Server?
Ans: SQL constraint is required to maintain valid and correct data, it will be helpful for preventing usual data, wrong data, invalid data etc.
if you provide SQL constraint to column then it will accept data as per the constraint rules defined on column.
Q. What are the Types of Constraint in SQL Server?
Ans: SQL server consist of different constraint like Primary Key, Foreign Key, Unique, NOT NULL, and Check Constraint, Default Constraint
Q. What is a PRIMARY KEY constraint in SQL Server?
Ans: Primary key constraint defines rule on column to accept only unique or distinct values into a column.
Q. How can we add a constraint to existing table?
Ans: Yes, you can add SQL constraint for already present table using Alter table SQL command with Add constraint clause.
Q. Can constraints be disabled or dropped in SQL Server?
Ans: Yes, we can disable constrains using Alter Table command in SQL with No Check Option or constraint can be dropped using Drop Constraint SQL statement.
Q. What is Difference in UNIQUE and PRIMARY KEY constraints
Ans: Unique and Primary key working is same which are allows us to insert unique or distinct data, but Unique constraint can be apply for many columns in same table, but Primary constraint can be applied for only one column in table.
Q. Can a table have multiple FOREIGN KEY constraints in SQL Server?
Ans: Yes, a table can have multiple FOREIGN KEY constraints, each establishing a relationship with a different table.
Q. How do you view the constraints applied to a table in SQL Server?
Ans: You can view constraint applied for existing table using Below SQL Query or can be viewed using sys.
‘sp_Table_name’
Example : sp_Developer
'sp_Table_name'
Example : sp_DeveloperReferences:
Developers Tutorial (itdevelopement.com)
ALL SQL Constraints Example
---primary key----
CREATE TABLE Developer (
Dev_ID int Primary key,
Dev_name varchar(50),
Dev_address varchar(50),
Dev_Designation varchar (50),
Dev_DOB date
);
---UNIQUE key---
CREATE TABLE Dev_Trans (
Dev_ID int UNIQUE ,
Dev_Salary float(10),
Dev_Pay_month date,
Dev_DOJ date,
Dev_RetireDate varchar,
);
---foreign key
CREATE TABLE Dev_KYC (
Dev_Kyc_ID int,
Dev_name varchar(50),
Dev_adharId nvarchar (12),
Dev_PAN nvarchar(10),
Dev_voterId nvarchar(10)
FOREIGN KEY (Dev_Kyc_ID) REFERENCES Developer(Dev_ID)
);
---NOT NULL and NULL constraint--
CREATE TABLE Dev_Edu (
Edu_RollNo INT PRIMARY KEY,
Edu_Clg_name VARCHAR(50) NOT NULL,
Edu_branch DECIMAL(10) NOT NULL,
Edu_university varchar(102) NULL
);
---CHECK constraint---
CREATE TABLE Dev_personal (
Dev_Per_ID int,
Dev_Age int Check (Dev_Age>25),
Dev_Email nvarchar (12),
Dev_Mobile int check (len(Dev_Mobile)<5),
Dev_Phone nvarchar(10)
);
---Default Constraint -------
CREATE TABLE Dev_info (
ID INT PRIMARY KEY,
Title VARCHAR(50),
Name varchar(50),
Estd_Date date default '07-Oct-1993'
);