SQL Server constraints on foreign keys

Question

I have an ItemComments table that has one column with a foreign key constraint linking back to an item entry in a second table. I see this as a one-to-many relationship as each item in the second table can have many comments, but no two items can be associated with the same comment entry in the ItemComments table, so a many-to-many relationship does not apply.

PROBLEM:

I want to define a constraint on this column that will prevent the foreign key value from being updated, i.e. I want to prevent someone from accidentally changing the item ID that a specific ItemComment entry is associated with. I'm not very familiar with the expression formatting of constraint check and was curious what the syntax would be for such an action. Or is there another more straightforward way of accomplishing this? Thanks for the help.

UPDATE

Is it better to implement a cross-reference table as you would in a many-to-many relationship to enforce referential integrity in this way? Or is that adding more overhead than is necessary?

user596075 · Accepted Answer · 2011-10-19 23:26:01Z

1

You can always use a trigger. Something like:

create trigger dml_PreventUpdate
on YourTable
after update
as
    if UPDATE(ItemId)
    rollback

There's two types of Data Manipulation Language (DML) triggers. There's an INSTEAD OF and then an AFTER/FOR trigger (AFTER and FOR function the same way). An INSTEAD OF trigger, as the name implies, executes prior to the transaction taking place. An AFTER trigger, again as the name implies, executes after the triggered action.

Basically all this trigger is doing is testing to see if the ItemId column is updated from the UPDATE statement run against the table, YourTable. This trigger will fire every time there is an UPDATE against YourTable, but it will only ROLLBACK the transaction if ItemId is an updated field.

answered Oct 19, 2011 at 23:26

user596075

Sign up to request clarification or add additional context in comments.

4 Comments

kingrichard2005 Over a year ago

Hi Shark, I'll admit I'm not familiar with using triggers, but this sounds like an option. I'll read more on the subject.

user596075 Over a year ago

@kingrichard2005 I have edited my post and added a brief explanation on triggers and what exactly is happening.

kingrichard2005 Over a year ago

I see, sounds like triggers are the way to go. I will accept your answer. Thanks Shark.

Shannon Severance Over a year ago

You might want to raiserror in addition to doing the rollback.

Collectives™ on Stack Overflow

SQL Server constraints on foreign keys

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related