[thelist] Foreign Key constraints

[jason.handby]

Hi Andrew,



> I've been reading my 1300+ page MSSQL programming book and 
> looking at some other references and I still can't seem to 
> find the answer to this question, however I think it's not 
> that strange of a situation (I generally do MySQL 4.1, though 
> so I don't usually deal with constraints):
> 
> * We have a 'customers' table, which contains some fields 
> that are directly related to other tables. For example 
> customers.state_id obviously refers to the states.state_id.
> 
> * I would like to incorporate a foreign key constraint that 
> would simply set the customers.state_id field to "NULL" if 
> the corresponding states.state_id row is deleted.
> 
> I'm aware of the ON UPDATE / ON DELETE / CASCADE / NO ACTION 
> settings, however none of these options seem to fit the bill. 
> UPDATE is fine, but DELETE is a problem.
> 
> If trying to delete a row from the states table that is 
> referenced in a customers column, the FK DELETE being set to 
> CASCADE deletes the customer record that is affiliated with 
> the state.  Being set to NO ACTION seems to just bring up 
> MSSQL errors when trying to delete a state row like that (on 
> my SQL console anyway).
> 
> So -- do I just ditch the foreign keys in a situation like 
> this and allow orphans to creep in? I think it makes total 
> sense to use CASCADE the DELETE if it were a separate table 
> that linked customers to states, but this I'm not so sure about.


You could put a DELETE TRIGGER on your state table, to go through your
customer table, find any references to the state IDs being deleted and
set them to NULL. Something like (I haven't tested this):


  CREATE TRIGGER trNullifyStateReferences ON states FOR DELETE
  AS
  UPDATE customers SET customers.state_id = NULL
  	WHERE customers.state_id IN (SELECT state_id FROM deleted)



Jason