Lookup column problem - relation issue

[Administrator]

<div class="bbWrapper">I&#039;m racking my brain over something that should be so simple. I have a main table (see attached) &quot;Contacts&quot; and in this table is an INT &quot;CategoryID&quot; for which a user can assign a Category to this contact (emulates Outlook Contacts). So we have two tables, one is Contacts with a CategoryID column and then a ContactCategories table with the PK of &quot;CategoryID&quot;. Pretty straight-forward.<br /> <br /> What I want to have happen here, the end result, is that if a user deletes a Category (from ContactCategories) that the Contacts table either sets the CategoryID column to NULL or the DEFAULT value (0). <br /> <br /> For whatever reason, I cannot figure out how to do this! I have tried setting a relation between these two tables, no biggy there, but then set the Cascade Delete option to Set Null or Set Default but SQL Server won&#039;t allow this relation to be set. I&#039;m not sure what I&#039;m doing wrong. If I do NOT set a relation then the CategoryID in the Contacts table becomes orphaned, i.e. it holds the value to a PK that doesn&#039;t exist in ContactCategories.<br /> <br /> Now to make this all a little more complex I cannot use triggers or stored procedures as this application targets various back-ends from SQL Server to SQL Server Compact Edition 3.5. <br /> <br /> So what is the best/right way to configure this (and I use a DataSet in the application) so that my Contacts table is being updated properly when a user decides to delete a ContactCategory?<br /> <br /> Thanks for the help.</div>