Currently -- two optional foreign keys to one primary key

  • Thread starter Thread starter Paolo
  • Start date Start date
P

Paolo

Hello,

I have three tables from a Purchase Order database:

tblComponents
ComponentID (PK)

tblPODetails
LineItemID (PK)
Purchase OrderID (PK)
ComponentID
Material
ComponentIDRef

tblPurchaseOrder
PurchaseOrderID(PK)

Could someone please advise me how to rearrange this to take into
consideration that ComponentID and ComponentIDRef in tblPODetails are
optional fields and foreign keys to ComponentID in tblComponents? I
know I am going about this poorly. Each record in tblPODetails can
either contain a component or a material. If it is a component, the
field ComponentID is filled in (Material and ComponentIDRef are null).
If it is a material, then Material and ComponentIDRef are filled in
(ComponentID is null).

Thanks in advance,

(e-mail address removed)
 
Paolo said:
If it is a component, the
field ComponentID is filled in (Material and ComponentIDRef are null).
If it is a material, then Material and ComponentIDRef are filled in
(ComponentID is null).
Click to expand...

Make both columns nullable (Required = False).

Create a 'table' level Validation Rule (or row-level CHECK constraint)
for the two valid states e.g.

(
ComponentID is NOT NULL
AND Material IS NULL
AND ComponentIDRef IS NULL
)
OR
(
Material IS NOT NULL
AND ComponentIDRef IS NOT NULL
AND (ComponentID IS NULL
)

Jamie.

--
 
Back
Top