Can't put multiple relationships between 2 tables in MS SQL 2008
I have a table (app_pages) that has 2 different foreign keys (fk_page_status_eid & fk_page_subdomain_eid), each of which share the same primary key in another table (app_entries).
Both app_pages.fk_page_status_eid and app_pages.fk_page_subdomain_eid allow for NULL values.
Well I can setup a relationship for 1, say between app_pages.fk_page_status_eid and app_entries.entry_id so that ON DELETE, the fk_page_status_eid is set to NULL.
Upon saving the relationship, all is good. Now I attempt to do likewise with the other FK.
I setup the second relationship between app_pages.fk_page_subdomain_eid and app_entries.entry_id so that ON DELETE, the fk_page_subdomain_eid is set to NULL.
Well, when I go to save, I now get an error that states:
Introducing FOREIGN KEY constraint 'FK_app_pages_app_entries1' on table 'app_pages' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
This makes no sense to me because an 'ON UPDATE NO ACTION' exists already. In my mind, if someone deletes from the app_entries, it would set NULL in either fk_page_status_eid or fk_page_subdomain_eid accordingly, but that isn't how it is.