error: cannot truncate table...foreign key constraint

Question

User1717218719 posted

I am looking to truncate all data from this table. when I run this code I get the error: "cannot truncate table because it is being referenced by a foreign key constraint". I am unsure as to how I should fix this.

    Protected Sub ClearAllDataBase()

        '--Get Data from Database 
        Dim conConn As SqlConnection = Nothing
        Dim comComm As SqlCommand = Nothing
        Dim adpAdap As SqlDataAdapter = Nothing
        ' Dim trnTran As SqlTransaction = Nothing


        conConn = New SqlConnection(GblSqlCon)
        conConn.Open()

        '    trnTran = conConn.BeginTransaction("test")

        comComm = New SqlCommand
        With comComm
            .Connection = conConn
            '        .Transaction = trnTran
            .CommandType = CommandType.Text
            .CommandText = " TRUNCATE TABLE tbl" 'and tblRel and tblTaxs

            comComm.ExecuteNonQuery()
        End With

        conConn.Close()

    End Sub

Answer 1

User475983607 posted

The error is very clear and is telling you that another table has a reference to at least one ID in the table you are trying to truncate.  If you were to truncate the data those records , in the other table, will become orphans.  First, delete the referenced records then truncate the table.

It seems you need to take a closer look at your design.

Answer 2

User-719153870 posted

Hi E.RU,

It seems that there is a foreign key constraint between the table you want to clear data from and some other tables.

When you try to clear the data from this table, you will see upon errors.

If you still want to clear the data, you can consider removing the foreign key constraint of this table in SQL.

You can refer to the following in SQL:

First, you need to know what’s the foreign key constraint with this table. You can use 

exec sp_helpconstraint ' tbl '

to see the constraints like below:

Second, use 

alter table tb1 drop constraint fka 

to drop the constraint in tb1

Best Regard,

Yang Shen