is it possible to delete from 2 or more sql tables using a sql datasource?

Question

I am getting an FK error when i am deleteing a row from my datagrid because i cant see a way to delete from the oher table where the FK is then the main table?

i am getting this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_Availability_Fixture". The conflict occurred in database "Rugby", table "dbo.Availability", column 'FixtureId'. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Availability_Fixture". The conflict occurred in database "Rugby", table "dbo.Availability", column 'FixtureId'. The statement has been terminated.

Answer 1

You can define your FK relationship to cascade on delete. This will result in the delete statement cascading (deleting) all child tables.

Check out page from MSDN

Answer 2

Your database is saying: "Do not allow the Availability.FixtureID column to reference values that don't exist." So, deleting a row from the Fixture table will be in violation of this when a row from the Availability table references it.

So you have two choices, either are equally vaid, but the first is probably what you're looking for:

1. Find the foreign key relationship named FK_Availability_Fixture, and then enable cascade delete on the FixtureId column.
2. Manually delete the dependancy row in the Availability table, or update the FixtureID for the particular row in question so that it references a different FixtureID value.

Answer 3

This is a "supposed to happen" to maintain data integrity: a row in another table references the row you're trying to delete.

If your delete succeeded, that other table's reference would make no sense.

The only way around this is to delete the referring row first (and this might mean deleting other rows that also refer to the row you're trying to delete.)

It's an indication that either: a) you shouldn't be deleting this row, b) or you need to delete other (potentially many other) row(s) first, or c) that your table design isn't correct for the use your trying to make of the database.