Administrator
VB.NET Forum Admin
- Joined
- Jun 3, 2004
- Messages
- 1,462
- Programming Experience
- 10+
I'm racking my brain over something that should be so simple. I have a main table (see attached) "Contacts" and in this table is an INT "CategoryID" 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 "CategoryID". Pretty straight-forward.
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).
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't allow this relation to be set. I'm not sure what I'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't exist in ContactCategories.
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.
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?
Thanks for the help.
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).
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't allow this relation to be set. I'm not sure what I'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't exist in ContactCategories.
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.
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?
Thanks for the help.
