What is meant CONSTRAINT in SQL Server?
What are different types of CONSTRAINTS in SQL Server?
How to create, alter and drop constraint?
What is meant CONSTRAINT in SQL Server?
Constraints are used to enforce data integrity on columns. We can also enforce data Integrity on columns using DML Triggers, Rules and Default.
The SQL Server query optimizer also uses constraint definitions to build high-performance query execution plans so it is very important to define constraints on columns but it is not mandatory.
Constraints can be defined as column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column. A table constraint is declared independently from a column definition and can apply to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint. NOT NULL and Default constraint we be defined at column level but not at Table level.
What are different types of CONSTRAINTS in SQL Server?
SQL Server Supports the following types of Constraints
How to create, alter and drop constraint?
Syntax to define CONSTRAINTS at the time of Table creation
Column Level
CREATE TABLE <Tablename>
( column1 datatype datatypewidth CONSTRAINT constraintname constraint type )
Table Level
SQL Server Supports the following types of Constraints
- Not Null
- Default
- Unique
- Check
- Primary Key
- Foreign Key
How to create, alter and drop constraint?
Syntax to define CONSTRAINTS at the time of Table creation
Column Level
CREATE TABLE <Tablename>
( column1 datatype datatypewidth CONSTRAINT constraintname constraint type )
Table Level
CREATE TABLE <Tablename>
(column1 datatype width CONSTRAINT constraintname constraint type (columnlist) )
Let’s discuss each constraint with example
We will be using TEACHMESQLSERVER Database for our Exercise so please create the database using below Query and Don’t Drop the database until you complete this article. Once you complete the article you can drop it.
CREATE DATABASE TEACHMESQLSERVER
NOT NULL Constraint
If it is imposed on a column that column will not allow NULL values into it; this can be imposed on any no of columns. If you don’t define this on column that column will allow NULL by default. A NOT NULL constraint can be defined column level; it cannot be defined table level. It can be defined at the time of table creation; we can define it later using ALTER command.
Syntax to define NOT NULL constraint at the time of Table Creation
ColumnName <DataType> [Datawidth] NOT NULL
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH NOT NULL CONSRTAINT ON DEPID*/
CREATE TABLE DEP(DEPID INT NOT NULL, DEPNAME VARCHAR(MAX))
(column1 datatype width CONSTRAINT constraintname constraint type (columnlist) )
Let’s discuss each constraint with example
We will be using TEACHMESQLSERVER Database for our Exercise so please create the database using below Query and Don’t Drop the database until you complete this article. Once you complete the article you can drop it.
CREATE DATABASE TEACHMESQLSERVER
NOT NULL Constraint
If it is imposed on a column that column will not allow NULL values into it; this can be imposed on any no of columns. If you don’t define this on column that column will allow NULL by default. A NOT NULL constraint can be defined column level; it cannot be defined table level. It can be defined at the time of table creation; we can define it later using ALTER command.
Syntax to define NOT NULL constraint at the time of Table Creation
ColumnName <DataType> [Datawidth] NOT NULL
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH NOT NULL CONSRTAINT ON DEPID*/
CREATE TABLE DEP(DEPID INT NOT NULL, DEPNAME VARCHAR(MAX))
GO
/*YOU CAN SEE ISNULLABLE =0 (FALSE) FOR DEPID COLUMN WHICH MEANS IT CAN'T TAKE NULL VALUE, DEPNAME COLUMN IS NULLABLE AS YOU CAN SEE VALUE IS 1*/
SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME='DEP'
GO
/*RECORD WILL BE SUCCESSFULLY INSERTED*/
INSERT INTO DEP(DEPID,DEPNAME) VALUES(1,NULL)
GO
/*THIS WILL FAIL BECAUSE YOU ARE TRYING TO INSERT NULL VALUE FOR NOT NULL COLUMN*/
INSERT INTO DEP(DEPNAME) VALUES('IT')
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'DEPID', table 'TEACHMESQLSERVER.dbo.DEP'; column does not allow nulls. INSERT fails.
The statement has been terminated.
GO
SELECT * FROM DEP
ALTER command to ADD NOT NULL constraint
Syntax
ALTER TABLE Tablename ALTER COLUMNNAME datatype width NOT NULL
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
ALTER TABLE Tablename ALTER COLUMNNAME datatype width NOT NULL
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITHOUT NOT NULL CONSRTAINT ON DEPID*/
CREATE TABLE DEP(DEPID INT , DEPNAME VARCHAR(MAX))
GO
DELETE FROM DEP WHERE DEPID IS NULL /*Deleting previously inserted record*/
GO
/*ADDING NOT NULL CONSRTAINT ON DEPID*/
ALTER TABLE DEP ALTER COLUMN DEPID INT NOT NULL
GO
/*YOU CAN VERIFY FROM BELOW QUERY*/
SELECT * FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.objects WHERE name='DEP')
GO
/*INSERT STATEMENT WILL EXECUTE SUCCESSFULLY*/
INSERT INTO DEP(DEPID,DEPNAME) VALUES(1,NULL)
GO
/*THIS INSERT STATEMETN WILL FAIL*/
INSERT INTO DEP(DEPID,DEPNAME) VALUES(NULL,'IT')
GO
SELECT * FROM DEP
How to modify NOT NULL column to accept NULL values?
GO
ALTER TABLE DEP ALTER COLUMN DEPID INT NULL
GO
/*You can check IS_NULLABLE Colum is 1*/
SELECT * FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.objects WHERE name='DEP')
DEFAULT Constraint
GO
ALTER TABLE DEP ALTER COLUMN DEPID INT NULL
GO
/*You can check IS_NULLABLE Colum is 1*/
SELECT * FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.objects WHERE name='DEP')
DEFAULT Constraint
The default value for any column if NOT NULL constraint isn't defined then it is NULL, which can be changed to any other value by using DEFAULT constraint. You can define DEFAULT constraint for any no of columns. DEFAULT constraint can’t be defined at table level. It can be defined at the time of table creation; we can define it later using ALTER command. I will explain both methods.
Syntax to define DEFAULT constraint at the time of Table Creation
It is advised to define constraint name. In the first syntax we are explicitly giving Constraint name for DEFAULT constraint on DEPNAME column, if you use second syntax then SQL server will implicitly give a Constraint name for DEFAULT constraint on DEPNAME column
ColumnName <DataType> [Datawidth] CONSTRAINT Constraintname DEFAULT defaultvalue
Or
ColumnName <DataType> [Datawidth] DEFAULT defaultvalue
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH DEFAULT CONSTRAINT ON DEPNAME COLUMN USING FIRST SYNTAX*/
CREATE TABLE DEP(DEPID INT, DEPNAME VARCHAR(MAX) CONSTRAINT DEPNAME DEFAULT 'SALES')
GO
/*YOU CAN SEE DEFAULT_OBJECT_ID COLUMN HAS 0 (FALSE) FOR DEPID COLUMN WHICH MEANS THIS COLUMN DONT HAVE DEFAULT CONSTRAINT.DEPNAME COLUMN'S DEFAULT_OBJECT_ID HAS SOME VALUE.USING THIS DEFAULT OBJECT ID WE CAN SEE WHAT IS THE DEFAULT VALUE FOR DEPNAME COLUMN*/
SELECT * FROM SYS.COLUMNS WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.OBJECTS WHERE NAME='DEP'
GO
/*AS YOU CAN SEE IN DEFINITION COLUMN WE HAVE SALES, THAT IS OUR DEFAULT VALUE FOR DEPNAME COLUMN */
SELECT NAME,TYPE_DESC,DEFINITION FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID =(SELECT OBJECT_ID FROMSYS.TABLES WHERE NAME='DEP' )
GO
/*RECORD WILL BE SUCCESSFULLY INSERTED*/
INSERT INTO DEP(DEPID,DEPNAME) VALUES(1,'IT')
GO
/*RECORD WILL BE SUCCESSFULLY INSERTED WITH DEFAULT VALUE*/
INSERT INTO DEP(DEPID) VALUES(1)
GO
SELECT * FROM DEP
ALTER command to ADD DEFAULT constraint
Syntax
ALTER TABLE Table-name ADD CONSTRAINT Constraint-name DEFAULT Default value FOR Columnname
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITHOUT DEFAULT CONSTRAINT */
CREATE TABLE DEP(DEPID INT , DEPNAME VARCHAR(MAX))
GO
/*ALTER Command TO CREATE DEFAULT CONSTRAINT*/
ALTER TABLE DEP ADD CONSTRAINT DEPNAMEDEFAULT DEFAULT 'SALES' FOR DEPNAME
GO
SELECT name,default_object_id,OBJECT_ID FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROMSYS.objects WHERE name='DEP')
GO
/*AS YOU CAN SEE IN DEFINITION COLUMN WE HAVE SALES, THAT IS OUR DEFAULT VALUE FOR DEPNAME COLUMN */
SELECT NAME,TYPE_DESC,DEFINITION FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = (SELECT OBJECT_IDFROM SYS.TABLES WHERE NAME='DEP' ) GO
GO
/*INSERTING DEFAULT VALUES */
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITHOUT DEFAULT CONSTRAINT */
CREATE TABLE DEP(DEPID INT , DEPNAME VARCHAR(MAX))
GO
/*ALTER Command TO CREATE DEFAULT CONSTRAINT*/
ALTER TABLE DEP ADD CONSTRAINT DEPNAMEDEFAULT DEFAULT 'SALES' FOR DEPNAME
GO
SELECT name,default_object_id,OBJECT_ID FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROMSYS.objects WHERE name='DEP')
GO
/*AS YOU CAN SEE IN DEFINITION COLUMN WE HAVE SALES, THAT IS OUR DEFAULT VALUE FOR DEPNAME COLUMN */
SELECT NAME,TYPE_DESC,DEFINITION FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = (SELECT OBJECT_IDFROM SYS.TABLES WHERE NAME='DEP' ) GO
GO
/*INSERTING DEFAULT VALUES */
INSERT INTO DEP DEFAULT VALUES
GO
SELECT * FROM DEP
How to remove DEFAULT constraint?
Using ALTER Command you can successfully remove DEFAULT constraint.
GO
ALTER TABLE DEP DROP CONSTRAINT DEPNAMEDEFAULT
/*You have successfully Removed Default constraint from DEPNAME column, You can check from below query*/
GO
SELECT name,default_object_id,OBJECT_ID FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROMSYS.objects WHERE name='DEP')
GO
SELECT * FROM DEP
How to remove DEFAULT constraint?
Using ALTER Command you can successfully remove DEFAULT constraint.
GO
ALTER TABLE DEP DROP CONSTRAINT DEPNAMEDEFAULT
/*You have successfully Removed Default constraint from DEPNAME column, You can check from below query*/
GO
SELECT name,default_object_id,OBJECT_ID FROM SYS.columns WHERE OBJECT_ID = (SELECT OBJECT_ID FROMSYS.objects WHERE name='DEP')
UNIQUE Constraint
The drawback of NOT NULL constraint is even if it restricts NULL values but it will not restrict duplicates values. If they have to be restricted we use UNIQUE constraint. UNIQUE constraint can be used to any no of column. It can be defined on both Column level and Table level. UNIQUE constraint will allow a single NULL value. We can’t create UNIQUE constraint on varchar(max) data type column. When you create UNIQUE constraint it automatically creates UNIQUE NON Clustered Index on the table.
Syntax to Define UNIQUE Constraint at the time of Table Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for UNIQUE constraint, if you use second syntax then SQL server will implicitly give a Constraint name for UNIQUE constraint.
1- Column Level
CREATE TABLE TABLE-NAME
( Column-name [Datatype] [Width] CONSTRAINT Constraint-name UNIQUE)
Or
CREATE TABLE TABLE-NAME
( Column-name [Datatype] [Width] UNIQUE)
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH UNIQUE CONSRTAINT ON DEPID*/
CREATE TABLE DEP(DEPID INT CONSTRAINT DEPIDUNIQUE UNIQUE, DEPNAME VARCHAR(100))
GO
/*YOU CAN SEE UNIQUE CONSTRAINT HAS BEEN CREATED*/
SELECT NAME,TYPE,TYPE_DESC FROM SYS.KEY_CONSTRAINTS WHERE PARENT_OBJECT_ID= (SELECT OBJECT_ID FROMSYS.TABLES WHERE NAME='DEP' )
GO
/*INSERTING A NEW RECORD, COMMAND WILL EXECUTE SUCCESSFULLY*/
INSERT INTO DEP(DEPNAME) VALUES('IT')
GO
/*THIS INSERT COMMAND WILL FAIL BECAUSE YOU ARE TRYING TO INSERT NULL VALUE FOR DEPID , UNIQUE CONSTRAINT CAN ALLOW A SINGLE NULL VALUE THAT WE HAVE ALREADY INSERTED OUR PREVIOUS QUERY */
INSERT INTO DEP(DEPNAME) VALUES('SALES')
/*THE ABOVE QUERY FAILED WITH BELOW ERROR */
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'DEPIDUNIQUE'. Cannot insert duplicate key in object 'dbo.DEP'.
The drawback of NOT NULL constraint is even if it restricts NULL values but it will not restrict duplicates values. If they have to be restricted we use UNIQUE constraint. UNIQUE constraint can be used to any no of column. It can be defined on both Column level and Table level. UNIQUE constraint will allow a single NULL value. We can’t create UNIQUE constraint on varchar(max) data type column. When you create UNIQUE constraint it automatically creates UNIQUE NON Clustered Index on the table.
Syntax to Define UNIQUE Constraint at the time of Table Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for UNIQUE constraint, if you use second syntax then SQL server will implicitly give a Constraint name for UNIQUE constraint.
1- Column Level
CREATE TABLE TABLE-NAME
( Column-name [Datatype] [Width] CONSTRAINT Constraint-name UNIQUE)
Or
CREATE TABLE TABLE-NAME
( Column-name [Datatype] [Width] UNIQUE)
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH UNIQUE CONSRTAINT ON DEPID*/
CREATE TABLE DEP(DEPID INT CONSTRAINT DEPIDUNIQUE UNIQUE, DEPNAME VARCHAR(100))
GO
/*YOU CAN SEE UNIQUE CONSTRAINT HAS BEEN CREATED*/
SELECT NAME,TYPE,TYPE_DESC FROM SYS.KEY_CONSTRAINTS WHERE PARENT_OBJECT_ID= (SELECT OBJECT_ID FROMSYS.TABLES WHERE NAME='DEP' )
GO
/*INSERTING A NEW RECORD, COMMAND WILL EXECUTE SUCCESSFULLY*/
INSERT INTO DEP(DEPNAME) VALUES('IT')
GO
/*THIS INSERT COMMAND WILL FAIL BECAUSE YOU ARE TRYING TO INSERT NULL VALUE FOR DEPID , UNIQUE CONSTRAINT CAN ALLOW A SINGLE NULL VALUE THAT WE HAVE ALREADY INSERTED OUR PREVIOUS QUERY */
INSERT INTO DEP(DEPNAME) VALUES('SALES')
/*THE ABOVE QUERY FAILED WITH BELOW ERROR */
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'DEPIDUNIQUE'. Cannot insert duplicate key in object 'dbo.DEP'.
The statement has been terminated.
GO
/*INSERTING A NEW RECORD, COMMAND WILL EXECUTE SUCCESSFULLY*/
INSERT INTO DEP VALUES(1,'SALES')
GO
SELECT * FROM DEP
2- Table Level
CREATE TABLE TABLE-NAME
( Column-name [Datatype] [Width], CONSTRAINT Constraint-name UNIQUE(column-list))
Note- When we Define UNIQUE Constraint at Table Level, Combination of columns list will follow UNIQUE constraint rules.
GO
/*INSERTING A NEW RECORD, COMMAND WILL EXECUTE SUCCESSFULLY*/
INSERT INTO DEP VALUES(1,'SALES')
GO
SELECT * FROM DEP
2- Table Level
CREATE TABLE TABLE-NAME
( Column-name [Datatype] [Width], CONSTRAINT Constraint-name UNIQUE(column-list))
Note- When we Define UNIQUE Constraint at Table Level, Combination of columns list will follow UNIQUE constraint rules.
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH UNIQUE CONSRTAINT ON COMBINATION OF DEPID AND DEPNAME*/
CREATE TABLE DEP(DEPID INT , DEPNAME VARCHAR(50), CONSTRAINT DEPIDUNIQUE UNIQUE(DEPID,DEPNAME))
GO
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
/*CREATING TABLE WITH UNIQUE CONSRTAINT ON COMBINATION OF DEPID AND DEPNAME*/
CREATE TABLE DEP(DEPID INT , DEPNAME VARCHAR(50), CONSTRAINT DEPIDUNIQUE UNIQUE(DEPID,DEPNAME))
GO
/*YOU CAN SEE UNIQUE CONSTRAINT HAS BEEN CREATED*/
SELECT NAME,TYPE,TYPE_DESC FROM SYS.KEY_CONSTRAINTS WHERE PARENT_OBJECT_ID= (SELECT OBJECT_ID FROMSYS.TABLES WHERE NAME='DEP' )
GO
/*INSERTING NEW RECORDS, COMMAND WILL EXECUTE SUCCESSFULLY*/
INSERT INTO DEP VALUES(1,'IT')
INSERT INTO DEP VALUES(2,'IT')
INSERT INTO DEP VALUES(1,'SALES')
GO
/*THIS NEW RECORD WILL NOT BE INSERTED, BECAUSE WE ARE INSERTING DUPLICATE RECORD, IN OUR PREVIOUS QUERY WE HAVE INSERTED THE SAME RECORD*/
INSERT INTO DEP VALUES(1,'SALES')
/*IT FAILED WITH BELOW ERROR*/
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'DEPIDUNIQUE'. Cannot insert duplicate key in object 'dbo.DEP'.
The statement has been terminated.
GO
SELECT * FROM DEP
Violation of UNIQUE KEY constraint 'DEPIDUNIQUE'. Cannot insert duplicate key in object 'dbo.DEP'.
The statement has been terminated.
GO
SELECT * FROM DEP
System View KEY_CONSTRAINTS
You can find all UNIQUE Constraint from system Views using below query.
You can find all UNIQUE Constraint from system Views using below query.
GO
SELECT * FROM SYS.KEY_CONSTRAINTS WHERE TYPE='UK'
ALTER command to ADD and DROP UNIQUE constraint
Syntax to DROP UNIQUE Constraint
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
GO
-- DROPING UNIQUE CONSTRIANT
-- DROPING UNIQUE CONSTRIANT
ALTER TABLE DEP DROP CONSTRAINT DEPIDUNIQUE
Syntax to ADD UNIQUE Constraint by ALTER Command
Column Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname UNIQUE (Columnname)
Example
Syntax to ADD UNIQUE Constraint by ALTER Command
Column Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname UNIQUE (Columnname)
Example
GO
ALTER TABLE DEP ADD CONSTRAINT COLLEVEL_DEPIDUNIQUE UNIQUE(DEPID)
Table Level
ALTER TABLE DEP ADD CONSTRAINT COLLEVEL_DEPIDUNIQUE UNIQUE(DEPID)
Table Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname UNIQUE (columnlist)
Example
GO
ALTER TABLE DEP ADD CONSTRAINT TABLEVEL_DEPIDUNIQUE UNIQUE(DEPID,DEPNAME)
GO
ALTER TABLE DEP ADD CONSTRAINT TABLEVEL_DEPIDUNIQUE UNIQUE(DEPID,DEPNAME)
CHECK Constraint
If you want to check the values present in a column to be according to a specific value then CHECK constraint on that column. CHECK constraint can be used to any no of columns. It can be defined on both Column level and Table level.
Syntax to define CHECK Constraint at the time of TABLE Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for CHECK constraint, if you use second syntax then SQL server will implicitly give a Constraint name for CHECK constraint.
1- Column Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] CONSTRAINT Constraintname CHECK(Columnname Condition))
Or
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] CHECK(Columnname Condition))
If you want to check the values present in a column to be according to a specific value then CHECK constraint on that column. CHECK constraint can be used to any no of columns. It can be defined on both Column level and Table level.
Syntax to define CHECK Constraint at the time of TABLE Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for CHECK constraint, if you use second syntax then SQL server will implicitly give a Constraint name for CHECK constraint.
1- Column Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] CONSTRAINT Constraintname CHECK(Columnname Condition))
Or
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] CHECK(Columnname Condition))
Example- i.e Age can’t be negative so we have to create CHECK CONSTRAINT on age column.
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
/*CREATING TABLE WITH CHECK CONSRTAINT ON AGE*/
CREATE TABLE EMP(EMPID INT , EMPNAME VARCHAR(50),AGE INT CHECK(AGE >0))
GO
/*TRY INSERTING ANY VALUE WHICH IS LESS THAN 0 FOR AGE COLUMN*/
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
/*CREATING TABLE WITH CHECK CONSRTAINT ON AGE*/
CREATE TABLE EMP(EMPID INT , EMPNAME VARCHAR(50),AGE INT CHECK(AGE >0))
GO
/*TRY INSERTING ANY VALUE WHICH IS LESS THAN 0 FOR AGE COLUMN*/
INSERT INTO EMP VALUES(1,'GHANESH',-1)
/*FAILED WITH BELOW ERROR BECAUSE YOU CAN’T INSERT A VALUE WHICH IS LESS THEN 0 FOR AGE COLUMN*/
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__EMP__AGE__5BAD9CC8". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.EMP", column 'AGE'.
The statement has been terminated.
GO
/* THIS INSERT STATEMENT WILL SUCCESSFULLY EXECUTE*/
INSERT INTO EMP VALUES(2,'SAURABH',24)
GO
SELECT * FROM EMP
2- Table Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width], CONSTRAINT Constraintname CHECK(columnname Condition LOGICAL OPERATOR Columnname Condition))
Note- When we Define CHECK Constraint at Table Level, Combination of columns list will follow CHECK constraint rules.
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
/*CREATING TABLE WITH CHECK CONSRTAINT ON COMBINATION OF AGE and EMPNAME*/
CREATE TABLE EMP(EMPID INT , EMPNAME VARCHAR(50),AGE INT, CONSTRAINT CHECKNAMEAGE CHECK(AGE >0 AND EMPNAMEIS NOT NULL))
GO
/* THIS INSERT STATEMENT WILL SUCCESSFULLY EXECUTE BECAUSE IT IS SATISFYING CHECK CONDITION*/
INSERT INTO EMP VALUES(2,'GHANESH',24)
GO
/*THIS INSERT STATEMENT WILL FAIL BECAUSE IT IS NOT SATISFYING CHECK CONDTION ON COMBINATION OF BOTH COLUMNS*/
INSERT INTO EMP VALUES(1,'GHANESH',-1)
/*FAILED WITH BELOW ERROR MESSAGE*/
MSG 547, LEVEL 16, STATE 0, LINE 1
THE INSERT STATEMENT CONFLICTED WITH THE CHECK CONSTRAINT "CK__EMP__AGE__5BAD9CC8". THE CONFLICT OCCURRED IN DATABASE "TEACHMESQLSERVER", TABLE "DBO.EMP", COLUMN 'AGE'.
THE STATEMENT HAS BEEN TERMINATED.
GO
/*AGAIN CHECK CONDITION FAILED FOR EMPNAME*/
/*FAILED WITH BELOW ERROR BECAUSE YOU CAN’T INSERT A VALUE WHICH IS LESS THEN 0 FOR AGE COLUMN*/
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__EMP__AGE__5BAD9CC8". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.EMP", column 'AGE'.
The statement has been terminated.
GO
/* THIS INSERT STATEMENT WILL SUCCESSFULLY EXECUTE*/
INSERT INTO EMP VALUES(2,'SAURABH',24)
GO
SELECT * FROM EMP
2- Table Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width], CONSTRAINT Constraintname CHECK(columnname Condition LOGICAL OPERATOR Columnname Condition))
Note- When we Define CHECK Constraint at Table Level, Combination of columns list will follow CHECK constraint rules.
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
/*CREATING TABLE WITH CHECK CONSRTAINT ON COMBINATION OF AGE and EMPNAME*/
CREATE TABLE EMP(EMPID INT , EMPNAME VARCHAR(50),AGE INT, CONSTRAINT CHECKNAMEAGE CHECK(AGE >0 AND EMPNAMEIS NOT NULL))
GO
/* THIS INSERT STATEMENT WILL SUCCESSFULLY EXECUTE BECAUSE IT IS SATISFYING CHECK CONDITION*/
INSERT INTO EMP VALUES(2,'GHANESH',24)
GO
/*THIS INSERT STATEMENT WILL FAIL BECAUSE IT IS NOT SATISFYING CHECK CONDTION ON COMBINATION OF BOTH COLUMNS*/
INSERT INTO EMP VALUES(1,'GHANESH',-1)
/*FAILED WITH BELOW ERROR MESSAGE*/
MSG 547, LEVEL 16, STATE 0, LINE 1
THE INSERT STATEMENT CONFLICTED WITH THE CHECK CONSTRAINT "CK__EMP__AGE__5BAD9CC8". THE CONFLICT OCCURRED IN DATABASE "TEACHMESQLSERVER", TABLE "DBO.EMP", COLUMN 'AGE'.
THE STATEMENT HAS BEEN TERMINATED.
GO
/*AGAIN CHECK CONDITION FAILED FOR EMPNAME*/
INSERT INTO EMP VALUES(2,NULL,24)
GO
SELECT * FROM EMP
GO
SELECT * FROM EMP
System View CHECK_CONSTRAINTS
You can find all CHECK Constraint from system Views using below query.
SELECT NAME,DEFINITION,TYPE,TYPE_DESC FROM SYS.CHECK_CONSTRAINTS
ALTER command to ADD and DROP CHECK constraint
You can find all CHECK Constraint from system Views using below query.
SELECT NAME,DEFINITION,TYPE,TYPE_DESC FROM SYS.CHECK_CONSTRAINTS
ALTER command to ADD and DROP CHECK constraint
Syntax to DROP CHECK Constraint
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
Example
GO
/* DROPPING CONSTRAINT*/
ALTER TABLE EMP DROP CONSTRAINT CHECKNAMEAGE GO
Syntax to ADD CHECK Constraint by ALTER Command
Column Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname CHECK (Columnname Condition)
Example
GO
/*ADDING CHECK CONSTRAINT FOR AGE COLUMN*/
ALTER TABLE EMP ADD CONSTRAINT COLUMNLEVEL_CHECK_NAMEAGE CHECK(AGE>0)
Table Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname CHECK (Columnname Condition LOGICAL OPERATOR Columname Condition)
/* DROPPING CONSTRAINT*/
ALTER TABLE EMP DROP CONSTRAINT CHECKNAMEAGE GO
Syntax to ADD CHECK Constraint by ALTER Command
Column Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname CHECK (Columnname Condition)
Example
GO
/*ADDING CHECK CONSTRAINT FOR AGE COLUMN*/
ALTER TABLE EMP ADD CONSTRAINT COLUMNLEVEL_CHECK_NAMEAGE CHECK(AGE>0)
Table Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname CHECK (Columnname Condition LOGICAL OPERATOR Columname Condition)
Example
GO
GO
/*ADDING CHECK CONSTRAINT ON COMBINATION OF EMPNAME AND AGE COLUMN*/
ALTER TABLE EMP ADD CONSTRAINT TABLELEVEL_CHECK_NAMEAGE CHECK(EMPNAME IS NOT NULL AND AGE>0)
PRIMARY KEY Constraint
The drawback of UNIQUE constraint is even if it restricts duplicates values but it will allow a single NULL value into the column, if we want to restrict duplicate values as well as NULL values we need to use PRIMARY KEY constraint. Only one PRIMARY KEY constraint is allowed in a Table. When we create PRIMARY KEY constraint it automatically create Clustered Index on primary key column. Primary Key constraint can be created at Column level and Table level. We can’t create PRIMARY KEY constraint on varchar(max) data type column.
Syntax to Define PRIMARY KEY Constraint at the time of Table Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for PRIMARY KEY constraint, if you use second syntax then SQL server will implicitly give a Constraint name for PRIMARY KEY constraint.
1- Column Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] CONSTRAINT Constraintname PRIMARY KEY)
Or
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] PRIMARY KEY)
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
-- CREATING TABLE WITH PRIMARY KEY CONSTRIANT ON EMPID
CREATE TABLE EMP(EMPID INT PRIMARY KEY , EMPNAME VARCHAR(MAX))
GO
--INSERTING NEW RECORD WITH NULL VALUE FOR EMPID , IT WILL FAIL BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
INSERT INTO EMP (EMPNAME) VALUES ('GHANESH')
--ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EMPID', table 'TEACHMESQLSERVER.dbo.EMP'; column does not allow nulls. INSERT fails.
The statement has been terminated.
GO
--THIS INSERT COMMAND WILL EXECUTE SUCCESSFULLY
INSERT INTO EMP VALUES (1, 'GHANESH')
GO
--THIS INSERT COMMAND WILL FAIL BECAUSE WE ARE TRYING TO ENTER DUPLICATE VALUE.
INSERT INTO EMP VALUES(1,'SAURABH')
--ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE BECAUSE PRIMARY KEY CONSTRIANT CANT BE DUPLICATE
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__EMP__14CCD97D74794A92'. Cannot insert duplicate key in object 'dbo.EMP'.
The statement has been terminated.
GO
SELECT * FROM DEP
2- Table Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width], CONSTRAINT ConstraintName PRIMARY KEY(Columnlist))
Note- When we Define Primary Key Constraint at Table Level, Combination of columns list will follow Primary Key constraint rules.
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
-- CREATING TABLE WITH PRIMARY KEY CONSTRIANT ON EMPID AND EMPNAME
CREATE TABLE EMP(EMPID INT, EMPNAME VARCHAR(100) , CONSTRAINT PRIMARYKEY_EMPIDEMPNAME PRIMARYKEY(EMPID,EMPNAME))
GO
--INSERTING NEW RECORD WITH NULL VALUE FOR EMPID , IT WILL FAIL BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
INSERT INTO EMP (EMPNAME) VALUES ('GHANESH')
--ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
MSG 515, LEVEL 16, STATE 2, LINE 1
CANNOT INSERT THE VALUE NULL INTO COLUMN 'EMPID', TABLE 'TEACHMESQLSERVER.DBO.EMP'; COLUMN DOES NOT ALLOW NULLS. INSERT FAILS.
The statement has been terminated.
GO
--BOTH INSERT COMMAND WILL EXECUTE SUCCESSFULLY
INSERT INTO EMP VALUES (1, 'GHANESH')
INSERT INTO EMP VALUES(1,'SAURABH')
GO
--THIS INSERT COMMAND WILL FAIL BECAUSE WE ARE TRYING TO ENTER DUPLICATE VALUE.
INSERT INTO EMP VALUES (1,'GHANESH')
--THE ABOVE INSERT COMMAND FAILED BECAUSE WE ARE TRYING TO ENTER DUPLICATE VALUE.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PRIMARYKEY_EMPIDEMPNAME'. Cannot insert duplicate key in object 'dbo.EMP'.
The statement has been terminated.
GO
SELECT * FROM DEP
System View KEY_CONSTRAINTS
You can find all PRIMARY KEY Constraint from system Views using below query.
GO
SELECT * FROM SYS.KEY_CONSTRAINTS WHERE TYPE='PK'
ALTER Command to ADD and DROP PRIMARY KEY Constraint
PRIMARY KEY constraint can be defined only on NOT NULL column if you are using ALTER Command to Create PRIMARY KEY constraint make sure your column has NOT NULL constraint defined.
Syntax to DROP PRIMARY KEY Constraint
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
GO
ALTER TABLE EMP DROP CONSTRAINT PRIMARYKEY_EMPIDEMPNAME -- DROP PRIMARY KEY CONSTRIANT FROM ALREADY EXISTING TABLE FROM PREVIOUS EXERCISE
Syntax to ADD PRIMARY KEY Constraint by ALTER Command
Column Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname PRIMARY KEY (Columnname)
Example
GO
SELECT * FROM EMP – checking if any duplicate record is present in the table
GO
TRUNCATE TABLE EMP – truncateing the table because duplicate records per present
GO
ALTER TABLE EMP ADD CONSTRAINT PRIMARYKEY_EMPIDEMPNAME PRIMARY KEY (EMPID)
Table Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname PRIMARY KEY (Columnlist)
Example
GO
ALTER TABLE EMP ADD CONSTRAINT PRIMARYKEY_EMPIDEMPNAME PRIMARY KEY (EMPID,EMPNAME)
FOREIGN KEY Constraint
It is a column or combination of columns that is used to establish and enforce a link between the data in two tables. When a Table A contains PRIMARY KEY Column of Table B, a foreign key reference will be created. In Table A PRIMARY KEY Column will be known as FOREIGN Key. Table B will be the Parent Table and Table A will be the referenced or child Table.
Example-
In below example, DEP Table is called as Parent Table and EMP Table is called has Child Table.
· DEP.DEPID is called as Reference Key column on which primary Key Constraint or Unique Constraint has to be imposed.
· EMP.DEPID is called as Foreign Key column on which FOREIGN KEY constraint has to be imposed, with this only the link get established between DEP and EMP Table.
You can create links between two Tables at the time of table creation, but first you have to create Parent table and then child table (Foreign Key Table).You can create this link using ALTER Command If you didn’t create the link at the time of Table Creation. Foreign Key constraint can’t be imposed on Varchar(Max) data type. FOREIGN KEY constraint can be used to any no of column. It can be created on both Column Level and Table Level.
If you try to insert new row in Foreign Table but Primary Key column value is not present in Parent Table in that case Insert will fail because as you know we are creating link between two tables using Primary Key of other table, if it will not be available in Parent table then how it will create the link ? That is why it will fail, so to insert a new record into the Foreign Table, Primary Key column value must be present in the Parent Table.
You can’t delete any record from the Parent Table if Primary Key Column value is present in Foreign Table. This is because you have created link using Primary Key and if data is present in foreign table for the same Primary Key then a link gets created. When you try to delete the record from the Parent Table it will break the link so it fails. This is known as referential Integrity.
You can’t modify Parent Table data, if data is present in foreign key Table. You can't DROP Parent Table because it is referenced with Foreign Key Table.
Syntax to Define FOREIGN KEY Constraint at the time of Table Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for FOREIGN KEY constraint, if you use second syntax then SQL server will implicitly give a Constraint name for FOREIGN KEY constraint.
1- Column Level
Create Table FOREIGNKEYTABLE(Column1 [DATATYPE][WIDTH] CONSTRAINT EMPPRIMARYKEY PRIMARY KEY, COLUMN2 [DATATYPE][WIDTH] CONSTRAINT FOREIGNKEY FOREIGN KEY REFERENCES PARENTTABLE (Column1))
Or
Create Table FOREIGNKEYTABLE (Column1 [DATATYPE][WIDTH] CONSTRAINT EMPPRIMARYKEY PRIMARY KEY, Column2 [DATATYPE][WIDTH] REFERENCES PARENTTABLE (Column2))
Example
USE TEACHMESQLSERVER
-- CREATING PARENT TABLE
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
CREATE TABLE DEP (DEPID INT CONSTRAINT PRIMARYKEY PRIMARY KEY, DEPNAME VARCHAR(MAX))
-- CREATING FOREIGN KEY TABLE
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
CREATE TABLE EMP(EMPID INT CONSTRAINT EMPPRIMARYKEY PRIMARY KEY, EMPNAME VARCHAR(MAX), DEPID INT CONSTRAINTFOREIGNKEY FOREIGN KEY REFERENCES DEP (DEPID))
-- LETS TRY TO DROP DEP TABLE
GO
DROP TABLE DEP -- YOU CAN’T DROP THE DEP TABLE BECAUSE IT IS REFERENCED BY A FOREIGN KEY CONSTRAINT , IT WILL GIVE BELOW ERROR MESSAGE
GO
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'DEP' because it is referenced by a FOREIGN KEY constraint.
-- FINDING ALL FOREIGN KEYS DETAILS FROM SYSTEM TABLES.
GO
SELECT * FROM SYS.foreign_keys
GO
SELECT * FROM SYS.objects WHERE OBJECT_ID= (SELECT OBJECT_ID FROM SYS.objects WHERE name='EMP')
GO
SELECT * FROM SYS.foreign_key_columns
GO
SELECT OBJECT_ID,NAME,COLUMN_ID FROM SYS.columns
GO
-- NOW LETS INSERT FEW RECORDS IN DEP TABLE PARENT TABLE
GO
INSERT INTO DEP VALUES (1,'IT'),(2,'SALES')
GO
-- NOW WHEN WE TRY TO INSERT VALUES INTO EMP TABLE THE DEPID WHAT WE GIVE SHOULD BE PRESENT IN DEP TABLE I.E 1,2 OR A NULL VALUE, IF WE TRY TO INSERT ANY OTHER VALUE THE INSERT STATEMENT WILL FAIL
GO
INSERT INTO EMP VALUES(1,'GHANESH',1) -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(2,'PRASAD',2)-- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(3,'GAUTAM') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(4,'OJAL') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(5,'KEVIN',3) -- FAILED TO INSERT BECAUSE DEPID =3 IS NOT PRESENT IN PARENT TABLE DEP
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FOREIGNKEY". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.DEP", column 'DEPID'.
The statement has been terminated.
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
2- Table Level
CREATE TABLE FOREIGNKEYTABLE (COLUMN1 INT PRIMARY KEY, COLUMN2 VARCHAR(MAX),COLUMN3 INT, CONSTRAINTFOREIGNKEY FOREIGN KEY (COLUMN3) REFERENCES PARENTTABLE(COLUMN3))
Example
USE TEACHMESQLSERVER
GO
DROP TABLE EMP
GO
CREATE TABLE EMP (EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX),DEPID INT, CONSTRAINT FOREIGNKEY FOREIGN KEY(DEPID) REFERENCES DEP(DEPID))
-- NOW WHEN WE TRY TO INSERT VALUES INTO EMP TABLE THE DEPID WHAT WE GIVE SHOULD BE PRESENT IN DEP TABLE I.E 1,2 OR A NULL VALUE, IF WE TRY TO INSERT ANY OTHER VALUE THE INSERT STATEMENT WILL FAIL
GO
INSERT INTO EMP VALUES(1,'GHANESH',1) -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(2,'PRASAD',2)-- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(3,'GAUTAM') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(4,'OJAL') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(5,'KEVIN',3) -- FAILED TO INSERT BECAUSE DEPID =3 IS NOT PRESENT IN PARENT TABLE DEP
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FOREIGNKEY". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.DEP", column 'DEPID'.
The statement has been terminated.
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
System View
You can find all FOREIGN KEY Constraint from system Views using below query.
GO
SELECT * FROM SYS.foreign_keys
GO
SELECT * FROM SYS.foreign_key_columns
How to handle DELETE and UPDATE commands on Parent Table
The Foreign Key Constraint enforces referential integrity by guaranteeing that changes can’t be made to the data in the primary key table (Parent Table) if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a Primary key table or to change a primary key value the action will fail when the deleted or changed Primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint you must first either delete the foreign key data in foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.
By using Cascading referential integrity constraints, we can define the actions that the SQL Server takes when a user tries to DELETE or UPDATE a key value in the Parent table to which existing Foreign Keys points.
Let’s go through with example to understand the concept.
The REFERENCES CLAUSE of the create table statements supports ON DELETE and ON UPDATE clause
ON DELETE NO ACTION |CASCADE | SET NULL |SET DEFAULT
ON UPDATE NO ACTION |CASCADE | SET NULL |SET DEFAULT
NO ACTION- It is the default action, It specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, Which is referenced by the foreign Key in other table an error is raised and DELETE|UPDATE Statement will fail.
CASCADE- Specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, which is referenced by the foreign key in other tables, all the foreign key values will also be DELETED| UPDATED to the new value specified for the key.
SET NULL- Specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, which is referenced by the foreign key in other tables, all rows that contains those foreign keys in child table are set to NULL
SET DEFAULT- Specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, which is referenced by the foreign key in other tables, all rows that contains those foreign keys in child table are set to DEFAULT VALUE. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default values set NULL becomes the implicit default value of the column.
Example
GO
DELETE FROM DEP WHERE DEPID=1
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FOREIGNKEY". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.EMP", column 'DEPID'.
The statement has been terminated.
ALTER Command to ADD and DROP FOREIGN KEY Constraint
FOREIGN KEY constraint can be defined later using ALTER command.
Syntax to DROP FOREIGN KEY Constraint
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
GO
ALTER TABLE EMP DROP CONSTRAINT FOREIGNKEY-- DROP FOREIGN KEY CONSTRIANT FROM ALREADY EXISTING TABLE FROM PREVIOUS EXERCISE
Syntax to ADD FOREIGN KEY Constraint by ALTER Command
ALTER TABLE Tablename ADD CONSTRAINT Constraint-name FOREIGN KEY (Column-name) REFERENCES PARENTTABLE(PRIMARYKEY COLUMN)
Example
GO
ALTER TABLE EMP ADD CONSTRAINT FOREIGNKEY FOREIGN KEY (DEPID) REFERENCES DEP(DEPID)
ON DELETE| ON UPDATE EXAMPLE
ALTER TABLE EMP DROP CONSTRAINT FOREIGNKEY
GO
ALTER TABLE EMP ADD CONSTRAINT FOREIGNKEY FOREIGN KEY (DEPID) REFERENCES DEP(DEPID) ON DELETE CASCADE
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
GO
DELETE FROM DEP WHERE DEPID=1 -- SUCCFULLY DELETED
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP --ALSO DELETED FROM EMP TABLE
##############################################################################
#############################################################################
Primary Key Constraint :
A primary key constraint is defined on a column or a set of columns whose value uniquely identify the rows in a table. These columns are referred to as primary keycolumns. A primary key column cannot contain NULL values since it is used to uniquely identify rows in a table.
While defining a PK constraint, you need to specify a name for the constraint, If no name is specified, SQL Server automatically assigns a unique name to the constraint.
Any column or set of columns that uniquely identifies a row in a table can be a candidate for the primary key. These set of columns are referred to as candidate keys. One of the candidate keys is chosen to be the primary key, based on familiarity and greater usage. the other key, which is not chosen as the primary key is called as alternate key.
A table should contain only one primary key.
Creating a primary key is very much similar to the unique constraint. In the syntax of unique, simply replace the keyword unique with a primary key.
Syntax :
CREATE TABLE table name (ColumnName1 datatype CONSTRAINT constraint_name Primary Key,
ColumnName2 datatype......................................)
( Or)
ALTER TABLE table name ADD CONSTRAINT constraint_name PRIMARY KEY (field name)
Example :
1. create table dept (dno int constraint pk_dept_dno primary key,
dname char(10), dsize int)
2. insert into dept values(10, 'HR', 30)
3. insert into dept values(10, 'Admin', 25)
---- Error, duplicate dno value
4. insert into dept (dname, dsize) values('Tester', 30)
---- Error, cannot enter a null value into dno column.
Adding a Primary Key :
It is not possible to directly add a primary key to an existing table's column if it is having any data. At first, we must add a 'NOT NULL' option to that column. Then only, we can add the primary key.
1. create table emp1 (eno int, ename char(10))
2. insert into emp1 values(100, 'A')
3. insert into emp1 values(101, 'B')
4. alter table emp1 add constraint pk_emp1_eno primary key(eno)
--- Error, since there is no NOT NULL option for the eno column
5. alter table emp1
alter column eno int not null
--- Specifying the NOT NULL option to the eno column
6. Execute command no.4 again. Now, it will execute successfully.
Composite Primary Key :
in some tables it is not possible to provide the primary key on any single column of the table. Then check the table data to identify any column combination, by using which, we can identify different records and other data of the table. If it is found, make that column as the primary key called as 'Composite Primary Key'. A maximum of 16 columns can be combined using this constraint.
Example :
=======
CREATE TABLE OrderDetails(ordid int, ino char(2), orddate datetime, cno int, Qty int, constraint pk_od_ordid_ino Primary Key(ordid, ino))
Foreign Key Constraint :
A foreign key is a column or combination of columns whose values match the primary key of another table. A foreign key does not have to be unique. However, foreign key values must be copies of the primary key values of the master table. The foreign key can be given on a column which is having the same data type, size and data as of the primary key column. The table with foreign key is called as 'Referenceing/Child'table and the table with primary key is called as 'Regerenced/Parent/ table.
Once we apply a relation between the table, lot of restrictions will be applied to the table operations. Some of them are:
The data to be entered in a foreign key column must heve a matching value in the primary key column of the parent table.
It is not possible to delete the parent table record if it is having a reference in the child table
Syntax :
======
CREATE TABLE < TableName >
(< ColName1 > < Datatype > [(size)] [CONSTRAINT < ConstraintName >] REFERNCES < ParentTable > (< PrimarykeyCol >)
[ON DELETE CASCADE],
< ColName2 >------------------------------------------)
Example :-
=======
1. create table emp(empid int, ename char(10), sal int, dno int constriant fk_emp_dno references dept(dno))
2. insert into emp values (100, 'A', 5000, 20)
3. insert int oemp values (101, 'B', 4500, 50)
--- Error, dno 50 is not having any reference in the parent table
4. insert into emep
Select 101, 'B', 3500, 10, union all
Select 102, 'C', 4500, 20, union all
Select 103, 'D', 5000, 30
--- Entering multipple values at a time into the table.
5. delete form dept where dno=20
--- Error, Since this record is having some referenced in the child table
The rules regarding the creation of a Foregin key constraint are as follows:
The no. of columns specified in the foreign key statement must match the no. of columns in the references clause and must be specified in the same order.
The foreign key can reference the primary key or a UNIQUE constraint of another table.
The foreign key constraint that is created using the WITH NONCHECK option will prevent the foreign key constraint from validating existing data.
ON DELETE CASCADE :
In general it is not possible to delete a record from the parent table if is having any references in the child table. In this case, at first, we have to remove the child table records and then the parent table record.
With the use of 'ON DELETE CASCADE', whenever, we remove the parent table record, that record and all its child table records will be removed automatically.
Adding a Foreign Key :
ALTER TABLE < Tablename >
Add Constraint < ConstraintName >
Foreign Key(< ColName >)
REFERENCES < ParentTable >(< PKColumn >)
[ON DELETE CASCADE]
Example :-
=======
1. alter table emp drop constraint fk_emp_dno
--- removing existing constraint
2. alter table emp add constraint fk_emp_dno foreign key(dno) reference dept(dno_) on delete cascade
--- adding the foreign key with new option
3. delete from dept where dno=20
--- Deletes the records with dno 20 both from dept and emp table
Constraints Vs Triggers :
Each category of data integrity is best enforced through the following constraints.
Entity integrity is best enforced through the PRIMARY KEY constraint, UNIQUEconstraint and the IDENTITY property.
The IDENTITY property creates an identity column in the table. An identity column is a primary key column for which numeric values are generates automatically. Each generated value is different from the previous value, thus ensuring uniqueness of data in each row at the table. The IDENTITY property also ensures that while inserting data, the user need not explicitly insert a value for the identity column. Domain integrity is best enforced through the DEFAULT constraint, CHECK constraint and the FOREIGN KEY constraint.
SQL Server : Constraints
It is very important for the data in a database to be accurate, consistent and reliable. This implies that it is very crucial to enforce data integrity. Data integrity ensures the consistency and correctness of data stored in a database. It is broadly classified into the following categories.
Entity Integrity - ensures that each row in a table is unique. It enforces integrity of the data contained in the columns, which uniquely identify the rows in a table.
Domain Integrity - ensures that only valid ranges of values are allowed to be stored in a column. It can be enforced, by restricting the type of data, the range of values and the format of the data.
Referential Integrity - ensures that the data in the database remains uniformly consistent, accurate and usable even after the data in it has been changed. It maintains the integrity of data by ensuring that the changes made in the parent table are also reflected in all the dependent tables.
Enforcing Data Integrity :
Incorporating business rules and specifying the relevant constraints can enforce data integrity. Business rules refer to specific policies followed by an organization for the smooth running of its business. Business rules ensure that the database stores accurate data in relation to the business policies.
For example an organization may have a personal policy, which states that the minimum salary of an employee is $500. This is a business rule.
Constraints refer to rules, which restrict the values that are inserted in the columns of a table. A constraint can either be created at the time of creating a table or can be added later. When a constraint is created after table creation, it checks the existing data. If the existing data does not conform to the rule being enforced by the constraint, then the constraint is rejected.
A constraint can be created using either of the following statements:
CREATE TABLE statement.
ALTER TABLE statement.
CREATE TABLE Statement :
A Constraint can be defined on a column at the time of creating a table. It can be created with the CREATE TABLE statement :
Syntax :
CREATE TABLE table_name
(Col_Name1 datatype CONSTRAINT constraint_name constraint_type, Col_Name2....)
ALTER TABLE Statement :
A Constraint can also be defined on a column after a table has been created. This can be done using the ALTER TABLE statement.
Syntax :
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (fieldName)
CHECK CONSTRAINT :
A Check Contraint enforces domain integrity by restricting the values to be inserted in a column. It allows only valid values in a column. It is possible to defined multipleCHECK constraints on a single column.
Syntax :
CREATE TABLE table_name
(Col_name datatype [CONSTRAINT Const_name] CHECK( <criteria> ), Col_name datatype.....
Example :
1. Create table dept (dno int, dname char(10), Dsize int constraint ck_dept_dsize check (dsize>20))
2. Insert into dept values(10, 'HR', 30)
3. Insert into dept values(20, 'Admin', 15)
</criteria>
The rules regrading the creation ofCHECK constraint are as follows:
It can be created at the column level as well as table level.
It is used to limit the values that can be inserted into a column.
It can contain user-specified search condition.
It cannot contain sub queries. A sub query i one or more select statements embedded within another select statement.
It does not check the existing data into the table if created with the No Check Option
It can reference other columns of the same table.
A Check constraint can also be added to an existing table by using the following syntax.
Syntax :
ALTER TABLE table_name ADD CONSTRAINT const_name CHECK ( <criteria> )
Example :
1. Insert into dept values(20, 'Admin', 15)
--- Correct, since we removed the restriction in the previous example.
2. Alter TABLE dept ADD CONSTRAINT ck_dept_dsize CHECK (dsize>20)
--- Error, since one of the dsize value is not greater than 20
3. Update dept set dsize=25 where dsize<20
--- Modifying the dsize value for those records which are having dsize value less than 20
4. Execute command. no 2 again. Now, it will execute successfl
</criteria>
UNIQUE CONSTRAINT :
UNIQUE constraints are used to enforce uniqueness on non-primary key columns. APrimary Key constraint column automatically included a restriction for uniqueness. However a unique constraint allows null values. It is not advisable to have columns with null values, though these are allowed. A null value is also validated, hence there can only be one record in the table with a null value. The table can't contain another row with null value. A unique column does not allow duplicate values but allows one null value.
Syntax :
CREATE TABLE table_name (colname1 datatype CONSTRAINT < const_name > UNIQUE,
colname2 datatype ------------------------.........)
Example :
=======
1. create table emp (empid int constraint uk_emp_empid unique, ename char(10))
2. insert into emp values(100, 'A')
3. insert into emp values(100, 'B')
---- Error, duplicate value into empid column
4. insert into emp(ename) values('B')
5. insert into emp(ename) values('C')
---- Error, duplicate NULL value into empid column
Adding a UNIQUE constraint to an existing table:
Syntax:
======
ALTER TABLE < tablename > ADD CONSTRAINT < constraintname > UNIQUE ( < columnname >)
Example :
=======
Alter table emp Add constraint uk_emp_empid unique(empid)
The rules regrading the creation of UNIQUE constraint are:
It does not allow two rows to have the same non-null values in a table.
It gets enforced automatically when a UNIQUE index is created.
Multiple UNIQUE constraints can be placed on a table.
https://lostechies.com/jimmybogard/2008/11/27/viewing-all-foreign-key-constraints-in-sql-server/
http://sqlmag.com/sql-server-2008/unique-constraint-multiple-nulls
http://weblogs.sqlteam.com/joew/archive/2008/10/01/60719.aspx
Constraint to limit VARCHAR max character length in SQL Server
A business need may arise to limit a VARCHAR column to a certain length. Consider a scenario where in an application has a 10000 character limit for a column. The VARCHAR(max) is the suitable data type choice for this column. However a VARCHAR(max) column is not limited to 10000 characters. In that case we can use a constraint to limit VARCHAR(max) character length. An example is shown below
-- Add constraint to new table
CREATE TABLE tblCns
(
Sno int identity,
Col1 VARCHAR(MAX)
CONSTRAINT chk_10000 CHECK (DATALENGTH([Col1]) <= 10000)
);
-- Add constraint to existing table
ALTER TABLE TblCns Add CONSTRAINT chk_10000_1 CHECK (DATALENGTH([Col1]) <= 10000)
Constraints
Constraint
Description
Primary Key
- Set of one or multiple columns which will uniquely identify each row in the table
- All columns should be NOT NULL
- Only 1 PK is allowed
- By default Clustered index will be created.
- You can create Nonclusted index like this.
CREATE TABLE pkcheck (a int not null)
ALTER TABLE pkcheck ADD CONSTRAINT pk_pkcheck PRIMARY KEY NONCLUSTERED (a)
Foreign Key
- Set of one or multiple columns which are being used to implement the referential integrity
- Can be null
- Parent table should have a primary key which is being used as FK in child table
- datatype should match in Primary and Child table
CONSTRAINT FK FOREIGN KEY (a,b) REFERENCES PK(c,d)
- ON DELETE CASCADE option will delete from child table automatically whenever deleted from parent table
Unique
- Set of one of multiple columns which will uniquely identify each row in the table
- will automatically create NONCLUSTERD index
- If NULL values are there, only one NULL value will be allowed
Default
- Allows to specify a default value whenever a value is not passed in INSERT statement
- can be applied on NULL and NOT NULL columns
CREATE TABLE def (a INT)
ALTER TABLE def ADD CONSTRAINT def_a DEFAULT(0) FOR a
Check
- allows to specify the range
- ALTER TABLE test.OrderHeader
ADD CONSTRAINT ck_subtotal CHECK (SubTotal > 0)
Custom Error Messages in Check Constraint Violation
Introduction
When a CHECK constraint of Table objects violates it gives an error message, showing that the check constraint is violated. One of the request that I get from a developers, that he want to provide the customized Error Message when the CHECK constraint violates.
Is it Possible?
In my point of view it is NOT possible. When the CHECK constraint violates it show the system error message like this.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_STDHIGHT_HIGHT". The conflict occurred in database
"PRACTICE_DB", table "dbo.tbl_PRODSTATUS".
The statement has been terminated.
So how can we customized it
We can use TRY, CATCH and RAISERROR() to solve this problem. Here I am trying to demonstrate it.
ALTER TABLE EMP ADD CONSTRAINT TABLELEVEL_CHECK_NAMEAGE CHECK(EMPNAME IS NOT NULL AND AGE>0)
PRIMARY KEY Constraint
The drawback of UNIQUE constraint is even if it restricts duplicates values but it will allow a single NULL value into the column, if we want to restrict duplicate values as well as NULL values we need to use PRIMARY KEY constraint. Only one PRIMARY KEY constraint is allowed in a Table. When we create PRIMARY KEY constraint it automatically create Clustered Index on primary key column. Primary Key constraint can be created at Column level and Table level. We can’t create PRIMARY KEY constraint on varchar(max) data type column.
Syntax to Define PRIMARY KEY Constraint at the time of Table Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for PRIMARY KEY constraint, if you use second syntax then SQL server will implicitly give a Constraint name for PRIMARY KEY constraint.
1- Column Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] CONSTRAINT Constraintname PRIMARY KEY)
Or
CREATE TABLE TABLENAME
( Columnname [DataType] [Width] PRIMARY KEY)
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
-- CREATING TABLE WITH PRIMARY KEY CONSTRIANT ON EMPID
CREATE TABLE EMP(EMPID INT PRIMARY KEY , EMPNAME VARCHAR(MAX))
GO
--INSERTING NEW RECORD WITH NULL VALUE FOR EMPID , IT WILL FAIL BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
INSERT INTO EMP (EMPNAME) VALUES ('GHANESH')
--ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'EMPID', table 'TEACHMESQLSERVER.dbo.EMP'; column does not allow nulls. INSERT fails.
The statement has been terminated.
GO
--THIS INSERT COMMAND WILL EXECUTE SUCCESSFULLY
INSERT INTO EMP VALUES (1, 'GHANESH')
GO
--THIS INSERT COMMAND WILL FAIL BECAUSE WE ARE TRYING TO ENTER DUPLICATE VALUE.
INSERT INTO EMP VALUES(1,'SAURABH')
--ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE BECAUSE PRIMARY KEY CONSTRIANT CANT BE DUPLICATE
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__EMP__14CCD97D74794A92'. Cannot insert duplicate key in object 'dbo.EMP'.
The statement has been terminated.
GO
SELECT * FROM DEP
2- Table Level
CREATE TABLE TABLENAME
( Columnname [DataType] [Width], CONSTRAINT ConstraintName PRIMARY KEY(Columnlist))
Note- When we Define Primary Key Constraint at Table Level, Combination of columns list will follow Primary Key constraint rules.
Example
USE TEACHMESQLSERVER
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
-- CREATING TABLE WITH PRIMARY KEY CONSTRIANT ON EMPID AND EMPNAME
CREATE TABLE EMP(EMPID INT, EMPNAME VARCHAR(100) , CONSTRAINT PRIMARYKEY_EMPIDEMPNAME PRIMARYKEY(EMPID,EMPNAME))
GO
--INSERTING NEW RECORD WITH NULL VALUE FOR EMPID , IT WILL FAIL BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
INSERT INTO EMP (EMPNAME) VALUES ('GHANESH')
--ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE BECAUSE PRIMARY KEY CONSTRIANT DOESNT ALLOW NULL
MSG 515, LEVEL 16, STATE 2, LINE 1
CANNOT INSERT THE VALUE NULL INTO COLUMN 'EMPID', TABLE 'TEACHMESQLSERVER.DBO.EMP'; COLUMN DOES NOT ALLOW NULLS. INSERT FAILS.
The statement has been terminated.
GO
--BOTH INSERT COMMAND WILL EXECUTE SUCCESSFULLY
INSERT INTO EMP VALUES (1, 'GHANESH')
INSERT INTO EMP VALUES(1,'SAURABH')
GO
--THIS INSERT COMMAND WILL FAIL BECAUSE WE ARE TRYING TO ENTER DUPLICATE VALUE.
INSERT INTO EMP VALUES (1,'GHANESH')
--THE ABOVE INSERT COMMAND FAILED BECAUSE WE ARE TRYING TO ENTER DUPLICATE VALUE.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PRIMARYKEY_EMPIDEMPNAME'. Cannot insert duplicate key in object 'dbo.EMP'.
The statement has been terminated.
GO
SELECT * FROM DEP
System View KEY_CONSTRAINTS
You can find all PRIMARY KEY Constraint from system Views using below query.
GO
SELECT * FROM SYS.KEY_CONSTRAINTS WHERE TYPE='PK'
ALTER Command to ADD and DROP PRIMARY KEY Constraint
PRIMARY KEY constraint can be defined only on NOT NULL column if you are using ALTER Command to Create PRIMARY KEY constraint make sure your column has NOT NULL constraint defined.
Syntax to DROP PRIMARY KEY Constraint
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
GO
ALTER TABLE EMP DROP CONSTRAINT PRIMARYKEY_EMPIDEMPNAME -- DROP PRIMARY KEY CONSTRIANT FROM ALREADY EXISTING TABLE FROM PREVIOUS EXERCISE
Syntax to ADD PRIMARY KEY Constraint by ALTER Command
Column Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname PRIMARY KEY (Columnname)
Example
GO
SELECT * FROM EMP – checking if any duplicate record is present in the table
GO
TRUNCATE TABLE EMP – truncateing the table because duplicate records per present
GO
ALTER TABLE EMP ADD CONSTRAINT PRIMARYKEY_EMPIDEMPNAME PRIMARY KEY (EMPID)
Table Level
ALTER TABLE Tablename ADD CONSTRAINT Constraintname PRIMARY KEY (Columnlist)
Example
GO
ALTER TABLE EMP ADD CONSTRAINT PRIMARYKEY_EMPIDEMPNAME PRIMARY KEY (EMPID,EMPNAME)
FOREIGN KEY Constraint
It is a column or combination of columns that is used to establish and enforce a link between the data in two tables. When a Table A contains PRIMARY KEY Column of Table B, a foreign key reference will be created. In Table A PRIMARY KEY Column will be known as FOREIGN Key. Table B will be the Parent Table and Table A will be the referenced or child Table.
Example-
In below example, DEP Table is called as Parent Table and EMP Table is called has Child Table.
· DEP.DEPID is called as Reference Key column on which primary Key Constraint or Unique Constraint has to be imposed.
· EMP.DEPID is called as Foreign Key column on which FOREIGN KEY constraint has to be imposed, with this only the link get established between DEP and EMP Table.
You can create links between two Tables at the time of table creation, but first you have to create Parent table and then child table (Foreign Key Table).You can create this link using ALTER Command If you didn’t create the link at the time of Table Creation. Foreign Key constraint can’t be imposed on Varchar(Max) data type. FOREIGN KEY constraint can be used to any no of column. It can be created on both Column Level and Table Level.
If you try to insert new row in Foreign Table but Primary Key column value is not present in Parent Table in that case Insert will fail because as you know we are creating link between two tables using Primary Key of other table, if it will not be available in Parent table then how it will create the link ? That is why it will fail, so to insert a new record into the Foreign Table, Primary Key column value must be present in the Parent Table.
You can’t delete any record from the Parent Table if Primary Key Column value is present in Foreign Table. This is because you have created link using Primary Key and if data is present in foreign table for the same Primary Key then a link gets created. When you try to delete the record from the Parent Table it will break the link so it fails. This is known as referential Integrity.
You can’t modify Parent Table data, if data is present in foreign key Table. You can't DROP Parent Table because it is referenced with Foreign Key Table.
Syntax to Define FOREIGN KEY Constraint at the time of Table Creation
It is advised to define a Constraint Name. In the first syntax we are explicitly giving Constraint name for FOREIGN KEY constraint, if you use second syntax then SQL server will implicitly give a Constraint name for FOREIGN KEY constraint.
1- Column Level
Create Table FOREIGNKEYTABLE(Column1 [DATATYPE][WIDTH] CONSTRAINT EMPPRIMARYKEY PRIMARY KEY, COLUMN2 [DATATYPE][WIDTH] CONSTRAINT FOREIGNKEY FOREIGN KEY REFERENCES PARENTTABLE (Column1))
Or
Create Table FOREIGNKEYTABLE (Column1 [DATATYPE][WIDTH] CONSTRAINT EMPPRIMARYKEY PRIMARY KEY, Column2 [DATATYPE][WIDTH] REFERENCES PARENTTABLE (Column2))
Example
USE TEACHMESQLSERVER
-- CREATING PARENT TABLE
GO
IF OBJECT_ID('DEP','U') IS NOT NULL
DROP TABLE DEP
GO
CREATE TABLE DEP (DEPID INT CONSTRAINT PRIMARYKEY PRIMARY KEY, DEPNAME VARCHAR(MAX))
-- CREATING FOREIGN KEY TABLE
GO
IF OBJECT_ID('EMP','U') IS NOT NULL
DROP TABLE EMP
GO
CREATE TABLE EMP(EMPID INT CONSTRAINT EMPPRIMARYKEY PRIMARY KEY, EMPNAME VARCHAR(MAX), DEPID INT CONSTRAINTFOREIGNKEY FOREIGN KEY REFERENCES DEP (DEPID))
-- LETS TRY TO DROP DEP TABLE
GO
DROP TABLE DEP -- YOU CAN’T DROP THE DEP TABLE BECAUSE IT IS REFERENCED BY A FOREIGN KEY CONSTRAINT , IT WILL GIVE BELOW ERROR MESSAGE
GO
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'DEP' because it is referenced by a FOREIGN KEY constraint.
-- FINDING ALL FOREIGN KEYS DETAILS FROM SYSTEM TABLES.
GO
SELECT * FROM SYS.foreign_keys
GO
SELECT * FROM SYS.objects WHERE OBJECT_ID= (SELECT OBJECT_ID FROM SYS.objects WHERE name='EMP')
GO
SELECT * FROM SYS.foreign_key_columns
GO
SELECT OBJECT_ID,NAME,COLUMN_ID FROM SYS.columns
GO
-- NOW LETS INSERT FEW RECORDS IN DEP TABLE PARENT TABLE
GO
INSERT INTO DEP VALUES (1,'IT'),(2,'SALES')
GO
-- NOW WHEN WE TRY TO INSERT VALUES INTO EMP TABLE THE DEPID WHAT WE GIVE SHOULD BE PRESENT IN DEP TABLE I.E 1,2 OR A NULL VALUE, IF WE TRY TO INSERT ANY OTHER VALUE THE INSERT STATEMENT WILL FAIL
GO
INSERT INTO EMP VALUES(1,'GHANESH',1) -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(2,'PRASAD',2)-- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(3,'GAUTAM') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(4,'OJAL') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(5,'KEVIN',3) -- FAILED TO INSERT BECAUSE DEPID =3 IS NOT PRESENT IN PARENT TABLE DEP
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FOREIGNKEY". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.DEP", column 'DEPID'.
The statement has been terminated.
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
2- Table Level
CREATE TABLE FOREIGNKEYTABLE (COLUMN1 INT PRIMARY KEY, COLUMN2 VARCHAR(MAX),COLUMN3 INT, CONSTRAINTFOREIGNKEY FOREIGN KEY (COLUMN3) REFERENCES PARENTTABLE(COLUMN3))
Example
USE TEACHMESQLSERVER
GO
DROP TABLE EMP
GO
CREATE TABLE EMP (EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX),DEPID INT, CONSTRAINT FOREIGNKEY FOREIGN KEY(DEPID) REFERENCES DEP(DEPID))
-- NOW WHEN WE TRY TO INSERT VALUES INTO EMP TABLE THE DEPID WHAT WE GIVE SHOULD BE PRESENT IN DEP TABLE I.E 1,2 OR A NULL VALUE, IF WE TRY TO INSERT ANY OTHER VALUE THE INSERT STATEMENT WILL FAIL
GO
INSERT INTO EMP VALUES(1,'GHANESH',1) -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(2,'PRASAD',2)-- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(3,'GAUTAM') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP(EMPID,EMPNAME) VALUES(4,'OJAL') -- SUCCFULLY INSERTED
GO
INSERT INTO EMP VALUES(5,'KEVIN',3) -- FAILED TO INSERT BECAUSE DEPID =3 IS NOT PRESENT IN PARENT TABLE DEP
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FOREIGNKEY". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.DEP", column 'DEPID'.
The statement has been terminated.
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
System View
You can find all FOREIGN KEY Constraint from system Views using below query.
GO
SELECT * FROM SYS.foreign_keys
GO
SELECT * FROM SYS.foreign_key_columns
How to handle DELETE and UPDATE commands on Parent Table
The Foreign Key Constraint enforces referential integrity by guaranteeing that changes can’t be made to the data in the primary key table (Parent Table) if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a Primary key table or to change a primary key value the action will fail when the deleted or changed Primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint you must first either delete the foreign key data in foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.
By using Cascading referential integrity constraints, we can define the actions that the SQL Server takes when a user tries to DELETE or UPDATE a key value in the Parent table to which existing Foreign Keys points.
Let’s go through with example to understand the concept.
The REFERENCES CLAUSE of the create table statements supports ON DELETE and ON UPDATE clause
ON DELETE NO ACTION |CASCADE | SET NULL |SET DEFAULT
ON UPDATE NO ACTION |CASCADE | SET NULL |SET DEFAULT
NO ACTION- It is the default action, It specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, Which is referenced by the foreign Key in other table an error is raised and DELETE|UPDATE Statement will fail.
CASCADE- Specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, which is referenced by the foreign key in other tables, all the foreign key values will also be DELETED| UPDATED to the new value specified for the key.
SET NULL- Specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, which is referenced by the foreign key in other tables, all rows that contains those foreign keys in child table are set to NULL
SET DEFAULT- Specifies that if an attempt is made to DELETE|UPDATE a key value in the MASTER Table, which is referenced by the foreign key in other tables, all rows that contains those foreign keys in child table are set to DEFAULT VALUE. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default values set NULL becomes the implicit default value of the column.
Example
GO
DELETE FROM DEP WHERE DEPID=1
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FOREIGNKEY". The conflict occurred in database "TEACHMESQLSERVER", table "dbo.EMP", column 'DEPID'.
The statement has been terminated.
ALTER Command to ADD and DROP FOREIGN KEY Constraint
FOREIGN KEY constraint can be defined later using ALTER command.
Syntax to DROP FOREIGN KEY Constraint
ALTER TABLE Tablename DROP CONSTRAINT ConstraintName
Example
GO
ALTER TABLE EMP DROP CONSTRAINT FOREIGNKEY-- DROP FOREIGN KEY CONSTRIANT FROM ALREADY EXISTING TABLE FROM PREVIOUS EXERCISE
Syntax to ADD FOREIGN KEY Constraint by ALTER Command
ALTER TABLE Tablename ADD CONSTRAINT Constraint-name FOREIGN KEY (Column-name) REFERENCES PARENTTABLE(PRIMARYKEY COLUMN)
Example
GO
ALTER TABLE EMP ADD CONSTRAINT FOREIGNKEY FOREIGN KEY (DEPID) REFERENCES DEP(DEPID)
ON DELETE| ON UPDATE EXAMPLE
ALTER TABLE EMP DROP CONSTRAINT FOREIGNKEY
GO
ALTER TABLE EMP ADD CONSTRAINT FOREIGNKEY FOREIGN KEY (DEPID) REFERENCES DEP(DEPID) ON DELETE CASCADE
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP
GO
DELETE FROM DEP WHERE DEPID=1 -- SUCCFULLY DELETED
GO
SELECT * FROM DEP
GO
SELECT * FROM EMP --ALSO DELETED FROM EMP TABLE
##############################################################################
#############################################################################
Primary Key Constraint :
A primary key constraint is defined on a column or a set of columns whose value uniquely identify the rows in a table. These columns are referred to as primary keycolumns. A primary key column cannot contain NULL values since it is used to uniquely identify rows in a table.
While defining a PK constraint, you need to specify a name for the constraint, If no name is specified, SQL Server automatically assigns a unique name to the constraint.
Any column or set of columns that uniquely identifies a row in a table can be a candidate for the primary key. These set of columns are referred to as candidate keys. One of the candidate keys is chosen to be the primary key, based on familiarity and greater usage. the other key, which is not chosen as the primary key is called as alternate key.
A table should contain only one primary key.
Creating a primary key is very much similar to the unique constraint. In the syntax of unique, simply replace the keyword unique with a primary key.
Syntax :
CREATE TABLE table name (ColumnName1 datatype CONSTRAINT constraint_name Primary Key,
ColumnName2 datatype......................................)
( Or)
ALTER TABLE table name ADD CONSTRAINT constraint_name PRIMARY KEY (field name)
Example :
1. create table dept (dno int constraint pk_dept_dno primary key,
dname char(10), dsize int)
2. insert into dept values(10, 'HR', 30)
3. insert into dept values(10, 'Admin', 25)
---- Error, duplicate dno value
4. insert into dept (dname, dsize) values('Tester', 30)
---- Error, cannot enter a null value into dno column.
Adding a Primary Key :
It is not possible to directly add a primary key to an existing table's column if it is having any data. At first, we must add a 'NOT NULL' option to that column. Then only, we can add the primary key.
1. create table emp1 (eno int, ename char(10))
2. insert into emp1 values(100, 'A')
3. insert into emp1 values(101, 'B')
4. alter table emp1 add constraint pk_emp1_eno primary key(eno)
--- Error, since there is no NOT NULL option for the eno column
5. alter table emp1
alter column eno int not null
--- Specifying the NOT NULL option to the eno column
6. Execute command no.4 again. Now, it will execute successfully.
Composite Primary Key :
in some tables it is not possible to provide the primary key on any single column of the table. Then check the table data to identify any column combination, by using which, we can identify different records and other data of the table. If it is found, make that column as the primary key called as 'Composite Primary Key'. A maximum of 16 columns can be combined using this constraint.
Example :
=======
CREATE TABLE OrderDetails(ordid int, ino char(2), orddate datetime, cno int, Qty int, constraint pk_od_ordid_ino Primary Key(ordid, ino))
Foreign Key Constraint :
A foreign key is a column or combination of columns whose values match the primary key of another table. A foreign key does not have to be unique. However, foreign key values must be copies of the primary key values of the master table. The foreign key can be given on a column which is having the same data type, size and data as of the primary key column. The table with foreign key is called as 'Referenceing/Child'table and the table with primary key is called as 'Regerenced/Parent/ table.
Once we apply a relation between the table, lot of restrictions will be applied to the table operations. Some of them are:
The data to be entered in a foreign key column must heve a matching value in the primary key column of the parent table.
It is not possible to delete the parent table record if it is having a reference in the child table
Syntax :
======
CREATE TABLE < TableName >
(< ColName1 > < Datatype > [(size)] [CONSTRAINT < ConstraintName >] REFERNCES < ParentTable > (< PrimarykeyCol >)
[ON DELETE CASCADE],
< ColName2 >------------------------------------------)
Example :-
=======
1. create table emp(empid int, ename char(10), sal int, dno int constriant fk_emp_dno references dept(dno))
2. insert into emp values (100, 'A', 5000, 20)
3. insert int oemp values (101, 'B', 4500, 50)
--- Error, dno 50 is not having any reference in the parent table
4. insert into emep
Select 101, 'B', 3500, 10, union all
Select 102, 'C', 4500, 20, union all
Select 103, 'D', 5000, 30
--- Entering multipple values at a time into the table.
5. delete form dept where dno=20
--- Error, Since this record is having some referenced in the child table
The rules regarding the creation of a Foregin key constraint are as follows:
The no. of columns specified in the foreign key statement must match the no. of columns in the references clause and must be specified in the same order.
The foreign key can reference the primary key or a UNIQUE constraint of another table.
The foreign key constraint that is created using the WITH NONCHECK option will prevent the foreign key constraint from validating existing data.
ON DELETE CASCADE :
In general it is not possible to delete a record from the parent table if is having any references in the child table. In this case, at first, we have to remove the child table records and then the parent table record.
With the use of 'ON DELETE CASCADE', whenever, we remove the parent table record, that record and all its child table records will be removed automatically.
Adding a Foreign Key :
ALTER TABLE < Tablename >
Add Constraint < ConstraintName >
Foreign Key(< ColName >)
REFERENCES < ParentTable >(< PKColumn >)
[ON DELETE CASCADE]
Example :-
=======
1. alter table emp drop constraint fk_emp_dno
--- removing existing constraint
2. alter table emp add constraint fk_emp_dno foreign key(dno) reference dept(dno_) on delete cascade
--- adding the foreign key with new option
3. delete from dept where dno=20
--- Deletes the records with dno 20 both from dept and emp table
Constraints Vs Triggers :
Each category of data integrity is best enforced through the following constraints.
Entity integrity is best enforced through the PRIMARY KEY constraint, UNIQUEconstraint and the IDENTITY property.
The IDENTITY property creates an identity column in the table. An identity column is a primary key column for which numeric values are generates automatically. Each generated value is different from the previous value, thus ensuring uniqueness of data in each row at the table. The IDENTITY property also ensures that while inserting data, the user need not explicitly insert a value for the identity column. Domain integrity is best enforced through the DEFAULT constraint, CHECK constraint and the FOREIGN KEY constraint.
SQL Server : Constraints
It is very important for the data in a database to be accurate, consistent and reliable. This implies that it is very crucial to enforce data integrity. Data integrity ensures the consistency and correctness of data stored in a database. It is broadly classified into the following categories.
Entity Integrity - ensures that each row in a table is unique. It enforces integrity of the data contained in the columns, which uniquely identify the rows in a table.
Domain Integrity - ensures that only valid ranges of values are allowed to be stored in a column. It can be enforced, by restricting the type of data, the range of values and the format of the data.
Referential Integrity - ensures that the data in the database remains uniformly consistent, accurate and usable even after the data in it has been changed. It maintains the integrity of data by ensuring that the changes made in the parent table are also reflected in all the dependent tables.
Enforcing Data Integrity :
Incorporating business rules and specifying the relevant constraints can enforce data integrity. Business rules refer to specific policies followed by an organization for the smooth running of its business. Business rules ensure that the database stores accurate data in relation to the business policies.
For example an organization may have a personal policy, which states that the minimum salary of an employee is $500. This is a business rule.
Constraints refer to rules, which restrict the values that are inserted in the columns of a table. A constraint can either be created at the time of creating a table or can be added later. When a constraint is created after table creation, it checks the existing data. If the existing data does not conform to the rule being enforced by the constraint, then the constraint is rejected.
A constraint can be created using either of the following statements:
CREATE TABLE statement.
ALTER TABLE statement.
CREATE TABLE Statement :
A Constraint can be defined on a column at the time of creating a table. It can be created with the CREATE TABLE statement :
Syntax :
CREATE TABLE table_name
(Col_Name1 datatype CONSTRAINT constraint_name constraint_type, Col_Name2....)
ALTER TABLE Statement :
A Constraint can also be defined on a column after a table has been created. This can be done using the ALTER TABLE statement.
Syntax :
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (fieldName)
CHECK CONSTRAINT :
A Check Contraint enforces domain integrity by restricting the values to be inserted in a column. It allows only valid values in a column. It is possible to defined multipleCHECK constraints on a single column.
Syntax :
CREATE TABLE table_name
(Col_name datatype [CONSTRAINT Const_name] CHECK( <criteria> ), Col_name datatype.....
Example :
1. Create table dept (dno int, dname char(10), Dsize int constraint ck_dept_dsize check (dsize>20))
2. Insert into dept values(10, 'HR', 30)
3. Insert into dept values(20, 'Admin', 15)
</criteria>
The rules regrading the creation ofCHECK constraint are as follows:
It can be created at the column level as well as table level.
It is used to limit the values that can be inserted into a column.
It can contain user-specified search condition.
It cannot contain sub queries. A sub query i one or more select statements embedded within another select statement.
It does not check the existing data into the table if created with the No Check Option
It can reference other columns of the same table.
A Check constraint can also be added to an existing table by using the following syntax.
Syntax :
ALTER TABLE table_name ADD CONSTRAINT const_name CHECK ( <criteria> )
Example :
1. Insert into dept values(20, 'Admin', 15)
--- Correct, since we removed the restriction in the previous example.
2. Alter TABLE dept ADD CONSTRAINT ck_dept_dsize CHECK (dsize>20)
--- Error, since one of the dsize value is not greater than 20
3. Update dept set dsize=25 where dsize<20
--- Modifying the dsize value for those records which are having dsize value less than 20
4. Execute command. no 2 again. Now, it will execute successfl
</criteria>
UNIQUE CONSTRAINT :
UNIQUE constraints are used to enforce uniqueness on non-primary key columns. APrimary Key constraint column automatically included a restriction for uniqueness. However a unique constraint allows null values. It is not advisable to have columns with null values, though these are allowed. A null value is also validated, hence there can only be one record in the table with a null value. The table can't contain another row with null value. A unique column does not allow duplicate values but allows one null value.
Syntax :
CREATE TABLE table_name (colname1 datatype CONSTRAINT < const_name > UNIQUE,
colname2 datatype ------------------------.........)
Example :
=======
1. create table emp (empid int constraint uk_emp_empid unique, ename char(10))
2. insert into emp values(100, 'A')
3. insert into emp values(100, 'B')
---- Error, duplicate value into empid column
4. insert into emp(ename) values('B')
5. insert into emp(ename) values('C')
---- Error, duplicate NULL value into empid column
Adding a UNIQUE constraint to an existing table:
Syntax:
======
ALTER TABLE < tablename > ADD CONSTRAINT < constraintname > UNIQUE ( < columnname >)
Example :
=======
Alter table emp Add constraint uk_emp_empid unique(empid)
The rules regrading the creation of UNIQUE constraint are:
It does not allow two rows to have the same non-null values in a table.
It gets enforced automatically when a UNIQUE index is created.
Multiple UNIQUE constraints can be placed on a table.
https://lostechies.com/jimmybogard/2008/11/27/viewing-all-foreign-key-constraints-in-sql-server/
http://sqlmag.com/sql-server-2008/unique-constraint-multiple-nulls
http://weblogs.sqlteam.com/joew/archive/2008/10/01/60719.aspx
Constraint to limit VARCHAR max character length in SQL Server
A business need may arise to limit a VARCHAR column to a certain length. Consider a scenario where in an application has a 10000 character limit for a column. The VARCHAR(max) is the suitable data type choice for this column. However a VARCHAR(max) column is not limited to 10000 characters. In that case we can use a constraint to limit VARCHAR(max) character length. An example is shown below
-- Add constraint to new table
CREATE TABLE tblCns
(
Sno int identity,
Col1 VARCHAR(MAX)
CONSTRAINT chk_10000 CHECK (DATALENGTH([Col1]) <= 10000)
);
-- Add constraint to existing table
ALTER TABLE TblCns Add CONSTRAINT chk_10000_1 CHECK (DATALENGTH([Col1]) <= 10000)
Constraints
Constraint
Description
Primary Key
- Set of one or multiple columns which will uniquely identify each row in the table
- All columns should be NOT NULL
- Only 1 PK is allowed
- By default Clustered index will be created.
- You can create Nonclusted index like this.
CREATE TABLE pkcheck (a int not null)
ALTER TABLE pkcheck ADD CONSTRAINT pk_pkcheck PRIMARY KEY NONCLUSTERED (a)
Foreign Key
- Set of one or multiple columns which are being used to implement the referential integrity
- Can be null
- Parent table should have a primary key which is being used as FK in child table
- datatype should match in Primary and Child table
CONSTRAINT FK FOREIGN KEY (a,b) REFERENCES PK(c,d)
- ON DELETE CASCADE option will delete from child table automatically whenever deleted from parent table
Unique
- Set of one of multiple columns which will uniquely identify each row in the table
- will automatically create NONCLUSTERD index
- If NULL values are there, only one NULL value will be allowed
Default
- Allows to specify a default value whenever a value is not passed in INSERT statement
- can be applied on NULL and NOT NULL columns
CREATE TABLE def (a INT)
ALTER TABLE def ADD CONSTRAINT def_a DEFAULT(0) FOR a
Check
- allows to specify the range
- ALTER TABLE test.OrderHeader
ADD CONSTRAINT ck_subtotal CHECK (SubTotal > 0)
Custom Error Messages in Check Constraint Violation
Introduction
When a CHECK constraint of Table objects violates it gives an error message, showing that the check constraint is violated. One of the request that I get from a developers, that he want to provide the customized Error Message when the CHECK constraint violates.
Is it Possible?
In my point of view it is NOT possible. When the CHECK constraint violates it show the system error message like this.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CHK_STDHIGHT_HIGHT". The conflict occurred in database
"PRACTICE_DB", table "dbo.tbl_PRODSTATUS".
The statement has been terminated.
So how can we customized it
We can use TRY, CATCH and RAISERROR() to solve this problem. Here I am trying to demonstrate it.
Step-1 [ Create the Base Table ]
IF OBJECT_ID(N'dbo.tbl_PRODSTATUS', N'U')IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_PRODSTATUS];
END
GO
CREATE TABLE [dbo].[tbl_PRODSTATUS]
(
IDNO INT NOT NULL IDENTITY PRIMARY KEY,
STDHIGHT INT NOT NULL,
HIGHT INT NOT NULL
);
GO
Step-2 [ Create CHECK Constraint ]
ALTER TABLE [dbo].[tbl_PRODSTATUS]
ADD CONSTRAINT CHK_STDHIGHT_HIGHT
CHECK(STDHIGHT>=HIGHT);
Step-3 [ Making Custom Error Message ]
--- Custom Error Handeling ---
EXEC sp_addmessage
@msgnum = 50020,
@severity = 1,
@msgtext = 'Can not Insert records in Table : %s as %s can not >= columns %s';
If you need the reference of RAISERROR() function, please check my previous article.
http://www.sqlknowledgebank.blogspot.in/2014/10/raiserror.html
Step-4 [ TRY, CATCH and RAISERROR() to Solve ]
BEGIN
BEGIN TRY
DECLARE @v_TblName VARCHAR(50),
@v_ColName1 VARCHAR(50),
@v_ColName2 VARCHAR(50);
SET @v_TblName= '[dbo].[tbl_PRODSTATUS]';
SET @v_ColName1 ='[HIGHT]';
SET @v_ColName2 ='[STDHIGHT]';
INSERT INTO [dbo].[tbl_PRODSTATUS]
(STDHIGHT, HIGHT)
VALUES(30, 40);
END TRY
BEGIN CATCH
RAISERROR(50020, 1, 1, @v_TblName, @v_ColName1,@v_ColName2);
END CATCH
END
Step-5 [ Execute analyze the Output ]
(0 row(s) affected)
Can not Insert records in Table : [dbo].[tbl_PRODSTATUS] as [HIGHT] can not >= columns [STDHIGHT]
Msg 50020, Level 1, State 1
Table-level CHECK constraints
CHECK Constraint is used to maintain the domain integrity.
In this article I am not discussing related to the definition and syntax of the CHECK constraint, rather I am trying to illustrate a scenario where we can use the check constraint called Table-level CHECK Constraint.
Scenario
Table Object Name: my_TabEmpMASTER
Attributes Name
Description
EmpID
The Primary Key
EmpName
The Name of the Employee
TargetLevel
5000, 3000, 2000 etc
Table Object Name: my_TabEmpRecTarget
Attributes Name
Description
EmpID
The Primary Key/ Foreign Key reference with [my_TabEmpMASTER]
ActualTaget
The Actual Target
Now, we need to insert the record in table object named "my_TabEmpRecTarget", depends on Target Level. For example, if the Employee ID numbers comes on Target Level 5000, it should takes entry greater than or equals to 5000, not less than the 5000 and so on.
Solutions
The solutions can be easily achieved by using triggers. But being performance factors in mind we are going to achieved this by using CHECK constraints.
Let's start it by using Table-Valued CHECK Constraint
CREATE TABLE my_TabEmpMASTER
(EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL,
TargetLevel DECIMAL(4,0) NOT NULL)
GO
INSERT INTO my_TabEmpMASTER
(EmpName, TargetLevel)
VALUES ('Joydeep Das', 5000),
('Sukamal Jana', 3000),
('Sangram Jit', 1000)
GO
SELECT * FROM my_TabEmpMASTER
GO
-- Result Set
EmpID EmpName TargetLevel
1 Joydeep Das 5000
2 Sukamal Jana 3000
3 Sangram Jit 1000
/*
Now Create a UDF which can return the actual
target level of an employee. It can take the EmpID as
Parameters and return TargetLevel
*/
CREATE FUNCTION dbo.fnGetTargetLevel
(
@p_EmpID INT
)
RETURNS DECIMAL(4,0)
AS
BEGIN
DECLARE @v_TRGLVL DECIMAL(4,0)
SELECT @v_TRGLVL = [TargetLevel]
FROM my_TabEmpMASTER
WHERE EmpID = @p_EmpID
RETURN @v_TRGLVL
END
GO
-- Now create the Transaction Table.
CREATE TABLE my_TabEmpRecTarget
(EmpID INT NOT NULL PRIMARY KEY,
ActualTaget DECIMAL(4,0) NOT NULL)
GO
-- Now adding foreign key references
ALTER TABLE my_TabEmpRecTarget
ADD CONSTRAINT my_TabEmpRecTarget_FK
FOREIGN KEY(EmpID) REFERENCES my_TabEmpMASTER(EmpID)
GO
-- Now Adding CHECK Constraint with Transaction Table
ALTER TABLE my_TabEmpRecTarget WITH NOCHECK
ADD CONSTRAINT chk_my_TabEmpRecTarget
CHECK (ActualTaget >= dbo.fnGetTargetLevel(EmpID))
-- Now we are Tring to Insert record
-- Inserting Record for Emp ID=1 'Joydeep Das'
-- With Actual Taget=6000 It is Not Violate the
-- CHECK Constraint as Target Level=5000, so the
-- Actual target is gretter then the Target Level
INSERT INTO my_TabEmpRecTarget
(EmpID, ActualTaget)
VALUES (1, 6000)
-- Result Set
(1 row(s) affected)
-- Now we are Violating the CHECK constraint by
-- Inserting Actual Target=1000
-- for Emp ID=2 'Sukamal Jana' whos Target Level=3000
-- Here the Actual Target is less than the Target Level.
-- So violating the CHECK Constraint
INSERT INTO my_TabEmpRecTarget
(EmpID, ActualTaget)
VALUES (2, 1000)
-- Result Set
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "chk_my_TabEmpRecTarget".
The conflict occurred in database "MYDB", table "dbo.my_TabEmpRecTarget".
The statement has been terminated.
Don't worry about the Existing record, if you have the existing records in the transaction table named "my_TabEmpRecTarget", as we are using the "NOCHECK" switch. SQL Server does not complain about the existing rows that don't pass the check, since we decided to use the NOCHECK switch.
Hope you like it.
PRIMARY key Vs UNIQUE key
This article is created for the beginners only. I find that there are lot of bad concepts related to PRIMARY key and UNIQUE key. In my article I want to clarify all those points related to it.
1. 1. PRIMARY key and UNIQUE key both maintains the uniqueness of the columns.
2. 2. PRIMARY Key NOT allows any kind of NULL values but in UNIQUE Key allow only One NULL value in it.
3. 3. So the PRIMARY KEY = UNIQUE + NOT NULL combination of data.
4. 4. So the UNIQUE KEY = UNIQUE + One NULL Value (Allowed)
5. 5. By default PRIMAY Key takes CULSTERED INDEX.
6. 6. We can create NON CLUSTERED INDEX on UNIQUE Key
I think the article is quite informative and thanking you to provide your valuable time on it.
Foreign key references in Database
We can find the all the foreign key references by this in current database
SELECT a.name FKey,
OBJECT_NAME(a.parent_object_id) ParentTable,
OBJECT_NAME(a.referenced_object_id) RefTable
FROM sys.foreign_keys a
