Truncating a table with a foreign key constraint - SQL Server

Question

I have two tables in a SQL Server Database, StudentAttendance and Attendance with the following structure:

Attendance

• AttendanceID
• TimeTableID
• AttendanceDate
• UserName

StudentAttendance

• StudentAttendanceID
• AttendanceID
• StudentID
• IsPresent

I run the following query and it works:

TRUNCATE TABLE StudentAttendance

But the following query gives me an error:

TRUNCATE TABLE Attendance

Msg 4712, Level 16, State 1, Line 4 Cannot truncate table 'Attendance' because it is being referenced by a FOREIGN KEY constraint.

Why do I get the above error even though the Attendance table is empty?

• Aaron Bertrand: Doesn’t matter if the table is empty. You need to either drop and re-create the foreign key constraint or just perform a delete instead of a truncate. How often are you really just emptying both tables?
• Asrar Ahmad Ehsan: @AaronBertrand rarely, I don't want this operation to be logged in transaction log that's why I wanted to use truncate instead of delete operation.
• Aaron Bertrand: Then drop the constraint(s), truncate, and re-create the constraints.

Answer 1

The documentation for TRUNCATE TABLE (Transact-SQL) is fairly clear on this topic.

Referencing the Restrictions:

You cannot use TRUNCATE TABLE on tables that:

Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

---

Regarding your comment

I don't want this operation to be logged in transaction log that's why I wanted to use truncate instead of delete operation.

The TRUNCATE TABLE command is fully logged, just not at the row level. Within a transaction, it can be rolled back.

Referencing the REMARKS section of the documentation.

Less transaction log space is used.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.