Foreign Key

[Amsterdammy]

Good day, I have Access 2000 How do I make a field have a Fireign Key
attribute. I have looked in adding new buttons to my toolbar, but cannot
find a Foreign Key button (help suggests this)

Any advice would be appreciated
Thanks

 

[BruceM]

A foreign key is created by creating a relationship with a primary key field
in another table. You specify a field as primary key in table design view,
but you do not do the same with the foreign key field. Make the foreign key
field the same data type (in table design view) as the primary key field,
except that if the primary key is Autonumber the foreign key needs to be
Number. You cannot create a relationship between two primary key fields.

 

[Jeff Boyce]

I might be remembering wrong, but I believe I have created a relationship
between two tables' primary key fields when there's a one-to-one
relationship.

I do know that I can't create a (meaningful) relationship between two
Autonumber fields.

Regards

Jeff Boyce
<Access MVP>

 

[BruceM]

I wondered about that. I should have just stopped before I got to that last
sentence. I believe I was thinking of both fields being autonumber when I
posted my quickly-written reply. Now that I think about it, a relationship
between a PK field and another in a one-to-one will result in the other
field also being unique, so probably no reason why it couldn't be a PK also.
Hmm. Thanks for making the point.

 

[peregenem]

A foreign key is created by creating a relationship with a primary key field
in another table. You specify a field as primary key in table design view,
but you do not do the same with the foreign key field.

Neither is required to be PRIMARY KEY. For a FOREIGN KEY, the column(s)
in the referenced table must comprise a UNIQUE constraint. PRIMARY KEY
has special meaning for Jet (physical ordering on disk) and is not
related to declarative referential integrity (DRI) actions. Do not be
fooled by that bogus message in Access about a PRIMARY KEY being
required to create a 'Relationship'.

Make the foreign key
field the same data type (in table design view) as the primary key field,
except that if the primary key is Autonumber the foreign key needs to be
Number.

Minor point: autonumber is not a data type; more like an attribute
(again, don't be mislead by the caption in Access). And you probably
meant INTEGER. Remember an autonumber can be a GUID too ;-)

 

[BruceM]

I believe we have been through this sort of thing before. What do you mean
"Neither is required to be PRIMARY KEY"? Neither of which two things? Do
you mean that a PK does not need to be designated as such? My guess is that
designating a field as PK makes the appropriate settings to the field so
that it is indexed and unique -- that it is a shortcut, in effect, to
specifying the field settings. If so, what is the problem in designating a
field as PK?

What do you mean "For a FOREIGN KEY, the column(s) in the referenced table
must comprise a UNIQUE constraint"? If we call the table with the PK
(substitue another term if you prefer) the main table and the table with the
FK the related table, which one is the "referenced table".

As for autonumber not being a data type, you are probably technically
correct. However, in table design view "autonumber" is a choice in the Data
Type column. I suppose I could have said "autonumber, which in spite of the
column label in data type view is not really a data type, but rather what
could be termed an attribute of the Number data type...". And I meant
Number, which in spite of what you may regard as a lack of terminology
precision, is selected in the Data Type column in table design view.
Integer, Long Integer, etc. are selected as Field Size.

When somebody asks for help I try to provide a helpful answer. I may point
out to somebody that it is more appropriate to think of fields rather than
columns in a table, but I probably would not bother to point out that the
whole comprises the parts, not the other way around. Correct usage would be
along the lines of that the unique constraint comprises fields in the
referenced table, but it really doesn't matter. If somebody has come here
for theory (or grammar) they will probably say so. Otherwise I must assume
they are trying to solve the problem at hand, even if the answers they
receive don't meet purity standards.

 

[peregenem]

I believe we have been through this sort of thing before.
What do you mean
"Neither is required to be PRIMARY KEY"?

Apologies, it would seem I edited my reply badly. To aid clarity,
here's an example:

CREATE TABLE Main (
ID INTEGER IDENTITY(1,1) NOT NULL UNIQUE,
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) NOT NULL,
middle_name VARCHAR(35) DEFAULT '{{NK}}' NOT NULL,
PRIMARY KEY (last_name, first_name, middle_name, ID)
)
;

CREATE TABLE Related (
ID INTEGER NOT NULL
REFERENCES Main (ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
start_date DATETIME DEFAULT DATE() NOT NULL,
end_date DATETIME,
CHECK (start_date <= end_date)
)
;

If we call the table with the PK
(substitue another term if you prefer) the main table and
the table with the
FK the related table, which one is the "referenced table".

From the above example, you should be able to identify that

Related ... REFERENCES Main

So, 'Related' is the referencing table and Main is the referenced
table.

If we've been through this before, then you will know why I want to
build my clustered index (physical ordering on disk) as last_name then
first_name then middle_name then ID. In this example, the ID is clearly
being used as a surrogate/artificial key, so it makes no sense for the
FOREIGN KEY to use all the columns (fields if you prefer) that comprise
the PRIMARY KEY. What does happen is that ID gets a UNIQUE constraint
and is the sole column used in the FOREIGN KEY.

Do you now see that you make an assumption when you say, 'A foreign key
is created by creating a relationship with a primary key field'?

 

[BruceM]

For my purposes I make sure the PK is unique. If ID is unique, I am done
creating a PK. If I understand correctly, ID is a unique field, and you are
adding the other fields to the PK in the interest of physical ordering on
the disk, but only one field (ID, which is unique in any case) needs to
enter into the relationship. (By the way, I am aware that ID is a reseved
word; I am using it here for convenience.) So you would have one field (ID)
for relationships, and add the other fields to the PK for physical ordering
on the disk. Do I understand correctly? If so, is there a performance
advantage (or some other advantage) to enforcing a particular type of
physical ordering on the disk vs. the resource demands of combining multiple
fields to augment a field (ID) that is already unique? If so, is there an
advantage to using all three name fields over using, say, the last name
field alone? In particular, does it make more than a theoretical difference
in a database involving just a few thousand records?
What would you have suggested to the person who posted the original
question?

 

[peregenem]

For my purposes I make sure the PK is unique. If ID is unique, I am done
creating a PK. If I understand correctly, ID is a unique field, and you are
adding the other fields to the PK in the interest of physical ordering on
the disk, but only one field (ID, which is unique in any case) needs to
enter into the relationship.

In my example, yes.

So you would have one field (ID)
for relationships, and add the other fields to the PK for physical ordering
on the disk. Do I understand correctly?

In my example, yes.

If so, is there a performance
advantage (or some other advantage) to enforcing a particular type of
physical ordering on the disk vs. the resource demands of combining multiple
fields to augment a field (ID) that is already unique?

I'll be cautious here: assuming the database file is regularly
compacted, there will be a performance advantage associated with having
a well chosen physical ordering. How significant that advantage may be
will vary due to circumstances, even to the point of what SQL DML you
are using (e.g. GROUP BY and BETWEEN constructs particularly benefit
from sensible clustered indexes).

If you are asking whether there may be a scenario where the overhead of
maintaining the non-clustered index associated with the PK between file
compacts outweighs the performance benefits of a well chosen clustered
index on a regularly compacted file, I'd think it unlikely but wouldn't
rule it out completely (many columns in the PK, more INSERTs than
SELECTs, SQL DML necessitates many table scans, etc). As with any
performance-related issue, you just have to test, test and test again.

If so, is there an
advantage to using all three name fields over using, say, the last name
field alone?

Again, it depends on the SQL DML. If you are using a field/column in
addition to last name then it should at least be considered for
inclusion in the PK.

For my purposes I make sure the PK is unique. If ID is unique, I am done
creating a PK.

So you are not looking for performance benefits. Your choice, no
biggie. My priorities are correct design and ease of maintenance, in
that order. Performance is not a priority for me either... which is why
I'd suggest you think in terms of UNIQUE CONSTRAINT rather than PRIMARY
KEY.

By the way, I am aware that ID is a reseved word

I don't think so but I could be mistaken. I check for reserved words in
Jet 4.0, SQL Server 2000, SQL Server 'future', SQL-92, SQL-99 and
SQL-2003, and 'ID' passes all my filters. Where do you think it is
reserved?

What would you have suggested to the person who posted the original
question?

If asked, I'd suggest the OP uses a SQL DDL script to create schema
elements rather than GUI tools, but I would not proffer such advice to
someone asking about toolbar buttons.

 

[BruceM]

You are correct, ID is not a reserved word.

If you "would not proffer such advice to someone asking about toolbar
buttons", would you have answered the question at all? There is a toolbar
button for PK. It is reasonable enough to imagine there would be a button
for FK. I sought only to explain that FK is not created in the same way as
PK, but rather through a relationship with a unique, indexed field (or
combination of fields) which I referred to as the PK. I prefer to use the
familiar language, even if it is not technically accurate; therefore,
"primary key" rather than "unique constraint". I find it difficult to
believe there is a design advantage to using a combination of fields when a
single field will serve the purpose, or that the combination of fields is
correct design while the common practice of using a single field is not. If
I am in error, so be it.

I do appreciate your taking the time to address my questions. You have
raised some points I would like to investigate in more detail some day. For
now, however, several projects have first claim on my attention.

 

[peregenem]

I find it difficult to
believe there is a design advantage to using a combination of fields when a
single field will serve the purpose, or that the combination of fields is
correct design while the common practice of using a single field is not.

Could it be that 'common practice' is to opt for a 'quick and dirty'
solution, which autonumber surely is?

Ever stopped to think about the advantages of using autonumber? Here
they are:

1. Convenience, because it's provided by the 'system';
2. It's an 'efficient' data type (assuming INTEGER/Long rather than
GUID);
3. Erm...
4. That's it!

By implication, the list of disadvantages is longer <g>.

The 'intelligence' behind the autonumber functionality doesn't amount
to much either i.e. (MAX + 1) or (MAX + 10000) as a precaution when
something went wrong. Almost anything is better than this: integer
values all with the same number of digits; non-sequential integer
values, the further apart the better; inclusion of a check digit i.e.
INTEGER becomes CHAR; etc etc.

 

[BruceM]

Ah, but I like quick, dirty, and convenient.

I believe autonumber is assigned immediately upon starting a new record,
which has the advantage of preventing errors resulting from duplicate values
in a multi-user environment. It puzzles users at first because a number is
apparently skipped, even when no record was ever created, but there is no
need for error handling. The error handling is no big deal, but I for one
would prefer to avoid it when practical. But we're back to quick, dirty,
and convenient, aren't we?