locked
error: cannot truncate table...foreign key constraint RRS feed

  • 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

    Tuesday, July 9, 2019 1:32 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 10, 2019 2:57 AM

All replies

  • 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.

    Tuesday, July 9, 2019 1:53 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 10, 2019 2:57 AM