SQL Interview: 18: Table Truncation and Foreign Keys

SQL Interview: 18: Table Truncation and Foreign Keys

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

You have two tables:

  • dbo.Customers
  • dbo.Orders

The dbo.Orders table has a column CustomerID that is declared as a foreign key to the CustomerID column in the dbo.Customers table. In the dbo.Customers table, CustomerID is the primary key.

There are no rows in the dbo.Orders table and 100 rows in the dbo.Customers table.

You execute the following code:

TRUNCATE TABLE dbo.Customers;

What happens when the statement is executed?

Answer:

An error is returned.

You cannot truncate a table that is the target of a foreign key, even if the referencing table is empty.

2025-02-06