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.

 

[Jamie Collins]

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?

Yes it is possible...

If so, is it good design?

On face value I would say no (but give me some more details and I may
change my mind <g>).

It's like saying, "I know this entity will be in a relationship with
another entity but I don't yet know which other entity that will be,"
for which the more obvious approach is to *defer* creating the
relationship until it is known. For inspiration, see the FIXTURE/
RESULT example used here:

ON POFN* AND POOD* - TWO COMPLEMENTARY DATABASE DESIGN PRINCIPLES
with Fabian Pascal, Hugh Darwen and David McGoveran

* POFN: Principle of Full Normalization; POOD: Principle of Orthogonal
Design

http://www.dbdebunk.com/page/page/3010532.htm

Jamie.

--

 

[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.

 

[Jamie Collins]

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.

In your example, I assume that both 'department' and employee' are
entities and that the business rule is that an incumbent is assigned
to zero or one department. On the design principle that a table models
either an entity or a relationship (but not both) departmental
assignments would be modelled using a relationship table (a.k.a.
junction table et al) with both columns NOT NULL UNIQUE. To model that
an employee has commended employment but has not been assigned a
department, you would create a row in the 'incumbents' table but defer
creating a row in the 'assignments' table until the department is
known. If you wanted to explicitly model 'unassigned' incumbents then
rather than a dummy department, which I agree is a poor design, you
could have a table for this purpose e.g. 'trainees'. However, as a
pragmatist I'd be happy enough for this relationship to be implicit,
which would avoid the need for an inter-table constraint (i.e. to
ensure all incumbents are either assigned a department or are a
trainee) which Access/Jet doesn't handle well, and use an outer join
to identify unassigned incumbents.

Jamie.

--