CONSTRAINTS in SQL SERVER

In this article, I am giving a quick overview about CONSTRAINTS in SQL Server. After completing this article you will understand:

What is meant CONSTRAINT in SQL Server?

What are different types of CONSTRAINTS in SQL Server?

How to create, alter and drop constraint?

Please give your valuable suggestions and feedback to improve this article.

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

·         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. I will explain both methods.

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

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

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 FROM SYS.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 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 FROM 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 FROM SYS.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 FROM SYS.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.

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

/*YOU CAN SEE UNIQUE CONSTRAINT HAS BEEN CREATED*/

SELECT NAME,TYPE,TYPE_DESC FROM SYS.KEY_CONSTRAINTS WHERE PARENT_OBJECT_ID= (SELECT OBJECT_ID FROM SYS.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

System View KEY_CONSTRAINTS

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

GO

-- 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

GO

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)

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))

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*/

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 EMPNAME IS 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

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

Syntax to DROP CHECK Constraint

ALTER TABLE Tablename DROP CONSTRAINT ConstraintName

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)

Example

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 PRIMARY KEY(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 CONSTRAINT FOREIGNKEY 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, CONSTRAINT FOREIGNKEY 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

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.