Last post Jul 10, 2019 02:57 AM by Yang Shen
Jul 09, 2019 01:32 PM|E.RU|LINK
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)
' trnTran = conConn.BeginTransaction("test")
comComm = New SqlCommand
.Connection = conConn
' .Transaction = trnTran
.CommandType = CommandType.Text
.CommandText = " TRUNCATE TABLE tbl" 'and tblRel and tblTaxs
Jul 09, 2019 01:53 PM|mgebhard|LINK
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.
Jul 10, 2019 02:57 AM|Yang Shen|LINK
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:
alter table tb1 drop constraint fka
to drop the constraint in tb1