Using SQL Server Management Studio In Object Explorer, expand the table with the constraint and then expand Keys. Right-click the constraint and then click Delete. In the Delete Object dialog box, click OK.
A referential constraint is defined for a specific column (called a foreign key) when a table is defined. A table in which a referential constraint and a foreign key are defined is called a referencing table, while a table that is referenced from a referencing table with a foreign key is called a referenced table.
The error means that you have data in other tables that references the data you are trying to delete.
You would need to either drop and recreate the constraints or delete the data that the Foreign Key references.
Suppose you have the following tables
dbo.Students
(
StudentId
StudentName
StudentTypeId
)
dbo.StudentTypes
(
StudentTypeId
StudentType
)
Suppose a Foreign Key constraint exists between the StudentTypeId
column in StudentTypes
and the StudentTypeId
column in Students
If you try to delete all the data in StudentTypes
an error will occur as the StudentTypeId
column in Students
reference the data in the StudentTypes
table.
EDIT:
DELETE
and TRUNCATE
essentially do the same thing. The only difference is that TRUNCATE
does not save the changes in to the Log file. Also you can't use a WHERE
clause with TRUNCATE
AS to why you can run this in SSMS but not via your Application. I really can't see this happening. The FK constraint would still throw an error regardless of where the transaction originated from.
Have you considered applying ON DELETE CASCADE
where relevant?
You are trying to delete a row that is referenced by another row (possibly in another table).
You need to delete that row first (or at least re-set its foreign key to something else), otherwise you’d end up with a row that references a non-existing row. The database forbids that.
To DELETE, without changing the references, you should first delete or otherwise alter (in a manner suitable for your purposes) all relevant rows in other tables.
To TRUNCATE you must remove the references. TRUNCATE is a DDL statement (comparable to CREATE and DROP) not a DML statement (like INSERT and DELETE) and doesn't cause triggers, whether explicit or those associated with references and other constraints, to be fired. Because of this, the database could be put into an inconsistent state if TRUNCATE was allowed on tables with references. This was a rule when TRUNCATE was an extension to the standard used by some systems, and is mandated by the the standard, now that it has been added.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With