PK not required to create FK 'relationship'

[Jamie Collins]

I rarely use the MS Access UI but I just now created a table named
Table1 in MS Access and I got a message saying, "A table must have a
primary key for you to define a relationship between this table and
other tables in the database".

'Relationships' is MS Access-speak for DRI, foreign keys and all that,
right? However, despite what that message says, I can indeed use my
table in a 'relationship' because it has a column with a unique index.
I copied Table1, pasted and named the new table Table2, then
successfully created a 'relationship' between these two tables, using
that GUI drag-and-drop 'relationships' thing. Afterwards, I checked
the DB's constraints using ADO's OpenSchema method and found that MS
Access/Jet had created a foreign key named Table2Table1 for me.

So this is a 'heads up' to not believe this message (unless I'm
missing the point, of course). It's issues such as this that make me
less than enamoured with the MS Access UI <g>.

Jamie.

--

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

I rarely use the MS Access UI but I just now created a table named
Table1 in MS Access and I got a message saying, "A table must have a
primary key for you to define a relationship between this table and
other tables in the database".

I think this simply means Access tries to encourage the user to define a PK.
However, Access (JET actually) is quite happy if you don't define a PK.

'Relationships' is MS Access-speak for DRI, foreign keys and all that,
right?

No. Access / JET allows you to define a relationship without DRI. In fact,
I just tried and Access allows me to create a relationship between 2
unindexed Fields. In this case, the relationship is deemed as
"Indeterminate". What we need to check is the type of relationship Access
determines from the nominated corresponding pairs of Fields. If one side is
a PK (or uniquely indexed), Access will determine this relationship as
1-to-M. If both sides are PK, Access will determine this relationship as
1-to-1.

 

[david epsom dot com dot au]

Yes, Access/Jet will use any primary key field as
a primary key field, even if the index is not labelled
as the 'primary key' index (which sets the default
sort order and is the clustering index).

On the other hand, even with message, it is easy to
miss that fact that a unique index is required as a
pre-requisite for updateable queries.

I wonder if the message dates from a time when the
indexing rules were different? Access 1.0 might not
have had support for multiple unique indexes other
than the 'primary key' index. I just looked, and
I can't find ANYTHING about the changes made between
Access 1.0 and 2.0

:~)

(david)

 

[Jamie Collins]

I just tried and Access allows me to create a relationship between 2
unindexed Fields.

Thanks Van. Wow, I hadn't appreciated quite how incorrect that "must
have a primary key to define a relationship" message is! It may be
well intentioned but disinformation it certainly is.

Jamie.

--