a question about foreign keys

[OhioSteve]

The "many" side of a one:many relationship can be a field that allows nulls. That is quite unusual, but it is legal in SQL. Now consider this scenario:

I have tableA, tableB, and tableC.
TableB has a one:many relationship with tableA. That relationship is NOT mandatory.
TableC has a one:many relationship with tableA. That relationship is NOT mandatory.
There is no way to fuse tableB and tableC. They have different fields.

Now, here is the kicker:

Each record in tableA MUST have a corresponding record in tableB OR a corresponding record in tableC. Moreover, the record in tableA may NOT have a corresponding record in both B and C.

I understand that I could implement this using triggers. But that is less than elegant. Is there a way that I can implement the either/or logic through the schema itself?

 

[bborissov]

I don't get it.
For me these statements contradict each other:
1. Each record in tableA MUST have a corresponding record in tableB OR a corresponding record in tableC. T

2. The record in tableA may NOT have a corresponding record in both B and C.

How at the same time the record in A MUST have corresponding record in B or C but it could not have corresponding record in B and C?

Also Foreign Keys didn't insert records. They only Check for Data Integrity and validate the business rule you put in your tables.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[OhioSteve]

TABLEA
a_id(pk)
b_id(fk)
c_id(fk)

TABLEB
b_id(pk)

TABLEC
c_id(pk)

VALID SAMPLE RECORDS IN TABLEA:
a_id/b_id/c_id
0/NULL/0
1/NULL/1
2/17/NULL

Every record in TABLEA will have NULL in either b_id or c_id. Every record in TABLEA will have a value in either b_id or c_id.

 

[JoeAtWork]

I think you could enforce this with a table constraint.

Joe Schwarz
Custom Software Developer

http://www.plandev.net