Foreign Key Questions

[kramer31]

Hi. Suppose that I have a field table1.field1 that might sometimes be
null but when it has a value will be a value found in table2.field1.
Now table2.field1 is guaranteed to be unique and can't have null
values. Can table1.field1 be a foreign key constrained to
table2.field1? If so, is it good design?

Second question: Suppose I have a table table3 with fields field1,
field2, and field3 where field1 and field2 are together the primary
key (thus guaranteed to be unique in combination) and I have a table
table4 with fields field1 and field2 where field1 is the primary key,
is it possible to make table3.field1 a foreign key linking to the
primary key table4.field1? If so, is it there anything wrong with
doing this?

 

[Allen Browne]

A1:
You are talking about a one-to-many relation from:
Table2.Field1 (primary key, or at least unique required field)
to:
Table1.Field1 (not unique not required.)

Yes: you can have an enforced relation like that.
Referential integrity does not require that the field on the "many" side of
the relation has a value - just that if it has a value it is a valid one.

A2:
Yes: you can have a relation based on a combination of fields.
Since the combination of Field1 and Field2 is unique and required in Table3,
it is valid for the "one" side of the relation.

In the Relationships window, when you drag a field from the primary table
and drop it onto the matching field in the related table, Access opens a
dialog that contains multiple rows. You can add the subsequent field(s) of
the relation in subsequent row(s) of the dialog.

 

[Pat Hartman \(MVP\)]

An example might be department. Assume that new hires spend their first
week in training before being assigned to a department. You can't assign
them to the training department since that has a different meaning. You
could create a "dummy" department for trainees but creating "dummy" values
isn't good practice. In fact the business rule is no department until after
training is complete so null should be allowed. However once a department
is assigned, it must be valid.

 

[Pat Hartman \(MVP\)]

I like to use natural keys where I can because it helps me, the developer,
when I am testing. However, I find that multi-field primary keys are more
trouble than they are worth so I've caved on this issue. I use an
autonumber primary key and create a unique index to enforce the uniqueness
business rule on the multi-field "natural" key. Don't let anyone tell you
that the unique index on the "natural" key isn't necessary. You do not want
to enforce this business rule via code. Let the database engine enforce all
business rules that you can define with declarative referential integrity is
my motto.