March 13, 2006 at 1:32 pm
Is there any way to enforce an 'optional' relationship short of using triggers? I have a situation where a record in table C has a foreign key column referencing table A and another foreign key column referencing table B. The record in C may have a reference to A, or a reference to B, or both. Is there a way to do this with referential integrity or a check contraint? Or will I have to use triggers? (I am using Sql Server 2000)
Thanks,
Joel Daniels
March 13, 2006 at 2:11 pm
Joel,
Can you tell us what do you mean by "enforce an 'optional' relationship"
Do you mean that the record have to have at least one of 2 references either to A or to B or to both but can not have none defined? Is it correct? In this case I would enforce it on the front-end level: I would design the validation controls that would check that at least one field is not null or is not empty. But than you will need the additional checks when bulk-importing data.
Regards,Yelena Varsha
March 13, 2006 at 2:15 pm
You are correct. At least one of the foreign key fields must have an entry.
March 13, 2006 at 2:43 pm
To think of it, the design with 2 repeating fields does not put a database into the 1st normal form, see
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q100139
it would be better to put reference type description into its own table and then in your main table C have one field with reference and one field with the reference type ID. In this case you will be able to have a constraint that the reference is not null
Regards,Yelena Varsha
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply