Updating a foreign key

A change or an update to a foreign key by itself is probably an error. A foreign key is a copy of the primary key. Never design the two to be independent. To allow updates of a foreign key, protect integrity by creating a trigger that checks updates against the master table and rolls them back if they do not match the primary key.

In the following example, the trigger tests for two possible sources of failure: either the title_id is not in the salesdetail table or it is not in the titles table.

This example uses nested if...else statements. The first if statement is true when the value in the where clause of the update statement does not match a value in salesdetail, that is, the inserted table will not contain any rows, and the select returns a null value. If this test is passed, the next if statement ascertains whether the new row or rows in the inserted table join with any title_id in the titles table. If any row does not join, the transaction is rolled back, and an error message is printed. If the join succeeds, a different message is printed.

create trigger forupdatetrig 
on salesdetail 
for update 
as 
declare @row int 
/* Save value of rowcount. */
select @row = @@rowcount  
if update (title_id) 
  begin 
    if (select distinct inserted.title_id 
        from inserted) is null 
      begin 
        rollback transaction 
        print "No, the old title_id must be in"
        print "salesdetail." 
      end 
    else 
      if (select count(*) 
          from titles, inserted 
          where titles.title_id = 
          inserted.title_id) != @row 
        begin 
          rollback transaction 
          print "No, the new title_id is not in"
          print "titles." 
        end 
      else
        print "salesdetail table updated" 
  end

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF