Category Archives: Errors

Cannot truncate table ‘xyz’ because it is being referenced by a FOREIGN KEY constraint – Sql Server

Errors, Scripts, Sql Server, , , , , , , , , , , ,

In this article we will discuss on when we get the error like below in Sql Server and how to resolve it.

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘Customer’ because it is being referenced by a FOREIGN KEY constraint.

To demonstrate this error let us first create a demo db ‘SqlhintsTruncateDemo’, two tables Customer and Order.

CREATE DATABASE SqlhintsTruncateDemo
GO
USE SqlhintsTruncateDemo
GO
CREATE TABLE [dbo].[Customer] (
	[CustID] [int] IDENTITY (1, 1) NOT NULL ,
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	) 
) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [int] NOT NULL ,
	CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	),
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Customer] (
		[CustID]
	) 
) 
GO

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

In the above script we have created a foreign key constraint FK_Order_Customer on the CustID column of the Order table which is referring to the CustID primary key column of the Customer table.

Disclaimer: As TRUNCATE table removes all the records from the table. Be careful before issuing this command.

Now try to truncate the Order Table

TRUNCATE TABLE [dbo].[Order]

RESULT:
Truncate Table Successful

As per the above result truncation of the Order table is successful. Now try to truncate the Customer Table

TRUNCATE TABLE [dbo].[Customer]

RESULT:
Truncate Table Failure

As per the result the truncation of the Customer table is failing, because the
CustID column of the Customer table is referenced by the CustID column of the Order Table.

If we still want to Truncate the table, then we have to drop all the foreign key
constraints which are referring to the table to be truncated.

So now drop the foreign key constraint FK_Order_Customer and then try truncating
the Customer table.

ALTER TABLE [dbo].[Order]
DROP CONSTRAINT FK_Order_Customer
GO
TRUNCATE TABLE [dbo].[Customer]
GO

RESULT:
Truncate Table Successful After Dropping of Foreign Key Constraint

As per the above result it is clear that now the table truncation is successful after dropping all the foreign key constraints which are refering to the table to be truncated.

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

In the above script replace the string ‘Enter Table Name’ with the table name for which you want to find out the referencing tables and the refering foreign key constraint name.

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ …

Errors, Sql Server, , , , , , , , ,

In this article we will discuss on when we get the errors like below in Sql Server and how to resolve them.

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

First to reproduce this scenario we will create a demo db, table, populate sample data in the table and create a stored procedure to add the data to table:

CREATE DATABASE DemoSQLHints
GO
USE DemoSQLHints
GO
SET ANSI_NULLS ON
GO
CREATE TABLE dbo.Employee(EmployeeId int identity(1,1),
                   FirstName VARCHAR(50),LastName  VARCHAR(50))
GO
--Insert 1k records using GO statement as below
INSERT INTO dbo.Employee(FirstName,LastName) VALUES(NEWID(),NEWID())
GO 1000

GO
--Create Stored Procedure to insert record in Employee Table with
--QUOTED_IDENTIFIER setting set to "OFF"

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Below statement to insert record in the Employee table, successfully executes and inserts a record in the employee table:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now try to create a filtered index (New Feature introduced in Sql Server 2008) on the employee table as shown below. To create filtered index, sql server requires it to be created with SET QUOTED_IDENTIFIER setting as ON.

SET QUOTED_IDENTIFIER ON
GO
CREATE NONCLUSTERED INDEX IX_Emplyoee_EmployeeId
 ON Employee(EmployeeId) WHERE EmployeeId > 500
GO

After creating the above filtered index, try executing the below statement which was executed successfully prior to the creation of the filtered index.

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

This time the execution of the above statement returns the below error:

Msg 1934, Level 16, State 1, Procedure AddEmployee, Line 5

INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Reason for this error is:  Employee table has filtered index and due to this any DML statement on this table which is executed with SET QUOTED_IDENTIFIER setting as OFF will result in failure. Here as the stored procedure AddEmployee is created with SET QUOTED_IDENTIFIER setting as OFF, so whenever this sp is executed it will use this setting stored in the meta data.

To solve this issue, we need to re-create the SP AddEmployee  with QUOTED_IDENTIFIER setting as ON as shown below:

DROP PROCEDURE dbo.AddEmployee
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE DBO.AddEmployee(@FirstName VARCHAR(50),@LastName VARCHAR(50))
AS
BEGIN
 SET NOCOUNT ON 
 INSERT INTO dbo.Employee(FirstName,LastName)
 VALUES (@FirstName, @LastName )
END
GO

Now, try executing the below statement:

EXEC DBO.AddEmployee 'Basavaraj','Biradar'
GO

Now, the above statement executes successfully and inserts a record in the employee table 🙂

You would also like to gothrough the article SET Options with their setting values required while working with filtered index

Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. It is better practice to use SET QUOTED_IDENTIFIERS ON setting.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version

[ALSO READ] Difference Between SET QUOTED_IDENTIFIER ON and OFF setting