0

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?

  • 3
    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? – Aaron Bertrand Sep 29 '18 at 4:10
  • @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. – Asrar Ahmad Ehsan Sep 29 '18 at 9:19
  • Then drop the constraint(s), truncate, and re-create the constraints. – Aaron Bertrand Oct 1 '18 at 13:24
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.