[thelist] Foreign Key constraints

Andrew Kamm akamm at demicooper.com
Fri Jun 9 09:01:06 CDT 2006


Hey all,

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.

Thanks!


-- 
Andrew Kamm





More information about the thelist mailing list