I am looking to implement a modeled SQL category - by which I mean that an item in my main table is either an entityA or an entityB (where entityA and entityB are mutually exclusive). This is a relatively common occurence in logical ERD's - by way of example consider:
Each user in MailingListFilter is either public or a registered - they cannot be both.
Whilst it is trivial to model this on an ERD implementing the either/or aspect of the relationship for referential integrity appears to be impossible in SQL Server. I could, in principle, split the main table into two (one for public subscriptions and one for member subscriptions) but then I cannot have a mixed subscriber list for a given NoticeType without dealing with data split across two tables for no really good reason.
Thanks in advance