Sql Server 2008 check constraint

Question

I have two tables, Table1 and Table2 with same primary key (FKTestID).
If I want to delete one row in Table1 and same FKTestID are in Table2 it will not work. You can only delete a row from Table1 if Table1.FKTestID not equals any FKTestID in Table2.

Please help me with this constraint?

Answer 1

You need to set the constraint to cascade on delete.

You can do this through SQL management studio by modifying the constraint.

Or you can do this via SQL when you created the constraint by including ON DELETE CASCADE at the end

You could also do it with the ALTER TABLE command.

Answer 2

Here is a code sample implementing what Simon suggested above.

CREATE TABLE dbo.Table1 (
    FKTestID int NOT NULL PRIMARY KEY
)
GO

CREATE TABLE dbo.Table2 (
    FKTestID int NOT NULL PRIMARY KEY
)
GO

ALTER TABLE dbo.Table2
ADD CONSTRAINT FK_Table2_FKTestID
FOREIGN KEY (FKTestID)
REFERENCES dbo.Table1 (FKTestID)
ON DELETE CASCADE
GO

INSERT INTO dbo.Table1 VALUES (1)
INSERT INTO dbo.Table2 VALUES (1)
INSERT INTO dbo.Table1 VALUES (2)
INSERT INTO dbo.Table2 VALUES (2)

DELETE FROM dbo.Table1 WHERE FKTestID = 1

SELECT 'Table1' AS [Table], * FROM dbo.Table1
SELECT 'Table2' AS [Table], * FROM dbo.Table2

=============================================

Table     FKTestID
------ -----------
Table1           2

Table     FKTestID
------ -----------
Table2           2

Note that I agree with Mitch Wheat's comment about CASCADE DELETE being dangerous. This feature is interesting, but I have never, ever used it in a production system.

Answer 3

If you are asking how to get rid of the constraint so that you can delete, DO NOT consider doing that. The constraint is there for a reason. If you don't know the reason, don't delete it.

Others have suggested adding a cascade delete. I suggest that this is poor idea as you can cause performance problems on the database. It is better to write a script that deletes the records in the correct order. In this case you need to delete the matching records from tables2 first, then run the delete on table 1.

You also need to evaluate the data in table2 before deciding to delete from it either by script or by cascade delte. If you should not be deleting those records from table2, then you should not be delting the records from table1 (by removing the constraint) as this will casue the table 2 records to be orphaned and you will lose the integrity of the data (which should be your first concern in any database actions (security and performance being a very close second and third)).

Let me give you a scenario where the data in table 2 would indicate you should not delete the record. Suppose you have a customer table and and order table. You want to delete customer A, but he has an order in the past. If you delete both records, you will mess up all the accounting information on orders. If you delete the customer but not the order (by eliminating the constraint), then you have an order that you can no longer tell who it was sent to. The correct thing to do in a case like this is to have an ISactive file in the customers table and mark him as an inactive customer. You would of course need to redesign the code that searches for customer information to make sure it includes the flag to only select active customers which is why this sort of thing should be thought out at the beginning of development not later (one reason why it is worth your while to hire database specialists for the design phase as many application developers don't consider maintaining the data over time as part of their design process).