CODEWITHSUNDEEP
sqlsql-server
TMNuclear
TMNuclear

Reputation: 1175

How to apply integrity constraint on foreign key table

I just started using SQL Server and have a question about integrity constraints with UPDATE and DELETE operations. To make it easier to explain, consider this simple example below, where the Contract_Header gives an overall information on the highest level about a contract, Contract_LineItem, which gives the annual information about the contract (each year = 1 entry), and Contract_LI_Status, a status of the LineItem, like 'In contract', 'Not paid', etc.

enter image description here

My problem is that I could update ContractID within Contract_LineItems to a whole different ContractID from Contract_Header, which would mean that the Contract_LineItem will refer to a whole different Contract than it is supposed to. The Contract_LineItem is supposed to be created automatically and not updated to any other ContractID afterwards. Though it should be delete-able.

Is it possible - probably it is - in SQL Server 2010 to restrict a certain value within a FK to be changed?

Upvotes: 1

Views: 69

Answers (1)

Robby Cornelissen
Robby Cornelissen

Reputation: 97150

Your best bet is probably to create a trigger that raises an error and rolls back the transaction if the foreign key column is updated. Something along the lines of:

CREATE TRIGGER trgAfterContractIdUpdate ON dbo.Contract_LineItem
FOR UPDATE AS
IF UPDATE(ContractID)
BEGIN 
    RAISERROR ('ContractID cannot be updated', 16, 1);
    ROLLBACK TRANSACTION
END

Upvotes: 1

Related Questions