Primary Keys

Yeah, there can and will be null values in at least one of the fields. That's
why I'm working with the Indexes.

"Smartin" wrote:

Nanette wrote:
I'm getting an error message when I attempt to change one of the fields to
unique that says: The changes you requested to the table were not successful
because they would create duplicates values in the index, primary key or
relationship. Change the data in the field or fields that contain duplicate
data (I checked and there is none), remove the index, or redefine the index
to permit duplicate entries and try again.

Does one of the fields contain Null values? (Per earlier suggestion that
you could possibly allow Null FK fields...)

--
Smartin

A foreign key is so called because it is related to a primary key field.
Unlike the primary key field, you do not define a foreign key field in table
design view. When you create a relationship between a PK field in one table
and a field in another table, that other field is considered the foreign key
field. The field that is to serve as the FK field must be the same data
type as the PK field, or Long Integer if the PK field is autonumber
(autonumber is a type of Long Integer).

"Nanette" wrote in message
...
Hi Ken,

They are actually Foreign Key fields, I mis-spoke earlier. How do I set
two
fields in one table to be Foreign Keys?

I thought I'd used the primary key function in access, but that doesn't
work.

"Ken Snell (MVP)" wrote:

Primary Keys cannot contain a Null value because they must always contain
unique values.

But, perhaps you want to allow Foreign Key fields to have a Null value
(foreign key fields are fields in a child / related table that link that
table back to a parent or lookup table)? If that is what you seek, be
sure
that the Required property for the field in the table is set to No.

--

Ken Snell
MS ACCESS MVP

"Nanette" wrote in message
...
I have a table with two primary keys (its a linking table). I'd like
both
the
fields to be able to accept null values. Is this possible, and if so,
how
do
I do that?

"BruceM" wrote:

A foreign key is so called because it is related to a primary key field.

Sounds like you are aware Ted Codd coined the pharse in his 1970 paper but
things have moved on since then and it is now recognised that a foreign key
can reference any candidate key, not just the primary key.

Hence, in Access/Jet a FOREIGN KEY (SQL keywords in uppercase) can REFERENCE
a UNIQUE constraint and a UNIQUE CONSTRAINT can comprise NULL values.

Unlike the primary key field, you do not define a foreign key field in table

That may be true of the Access GUI but in Jet SQL DDL a FOREIGN KEY may be
created via the CREATE TABLE syntax...

When you create a relationship between a PK field in one table
and a field in another table, that other field is considered the foreign key
field.

Note that a key can comprise more than one field. Further note that a
FOREIGN KEY can reference the same table e.g.

CREATE TABLE OrgChart (
employee_ID INTEGER NOT NULL UNIQUE,
manager_employee_ID INTEGER,
CONSTRAINT fk__OrgChart__OrgChart
FOREIGN KEY (manager_employee_ID)
REFERENCES OrgChart (employee_ID)
);

Hence 'foreign' cannot mean 'foreign to the table'.

Jamie.

--

Nope, didn't know about Ted Codd. Yes, I did know that a key can comprise
more than one field. I probably misunderstood "How do I set two
fields in one table to be Foreign Keys?" I was thinking of a junction table
(or whatever more appropriate terminology you prefer) which is on the "many"
side of relationships with two separate tables. However, I realize now that
the OP seems to have been referring to a multi-field PK. Let's say it's two
fields. That means two corresponding fields in the Child table, but the
point remains that only the PK is defined in table design view.
Relationships establish the foreign keys (except for what you said about
using CREATE TABLE and unique constraints and all that). In fact, I'm sure
there is all sorts of inaccurate terminology in what I have posted. My only
intention was to try to help the OP with the problem at hand.

"Jamie Collins" wrote in message
...


"BruceM" wrote:

A foreign key is so called because it is related to a primary key field.

Sounds like you are aware Ted Codd coined the pharse in his 1970 paper but
things have moved on since then and it is now recognised that a foreign
key
can reference any candidate key, not just the primary key.

Hence, in Access/Jet a FOREIGN KEY (SQL keywords in uppercase) can
REFERENCE
a UNIQUE constraint and a UNIQUE CONSTRAINT can comprise NULL values.

Unlike the primary key field, you do not define a foreign key field in
table

That may be true of the Access GUI but in Jet SQL DDL a FOREIGN KEY may be
created via the CREATE TABLE syntax...

When you create a relationship between a PK field in one table
and a field in another table, that other field is considered the foreign
key
field.

Note that a key can comprise more than one field. Further note that a
FOREIGN KEY can reference the same table e.g.

CREATE TABLE OrgChart (
employee_ID INTEGER NOT NULL UNIQUE,
manager_employee_ID INTEGER,
CONSTRAINT fk__OrgChart__OrgChart
FOREIGN KEY (manager_employee_ID)
REFERENCES OrgChart (employee_ID)
);

Hence 'foreign' cannot mean 'foreign to the table'.

Jamie.

--

"BruceM" wrote:

Nope, didn't know about Ted Codd. Yes, I did know that a key can comprise
more than one field. I probably misunderstood "How do I set two
fields in one table to be Foreign Keys?" I was thinking of a junction table
(or whatever more appropriate terminology you prefer) which is on the "many"
side of relationships with two separate tables. However, I realize now that
the OP seems to have been referring to a multi-field PK. Let's say it's two
fields. That means two corresponding fields in the Child table, but the
point remains that only the PK is defined in table design view.
Relationships establish the foreign keys (except for what you said about
using CREATE TABLE and unique constraints and all that). In fact, I'm sure
there is all sorts of inaccurate terminology in what I have posted. My only
intention was to try to help the OP with the problem at hand.

Fair enough.

FWIW I think it is the case that Ted Codd was making up the terminology as
he went along and 'foreign' is the term he chose, based on who knows what
reasons. I'm pretty sure he didn't have Access UI Table Design View in mind,
though ;-)

Jamie.

--

Sometimes the accepted terminology is inaccurate, but is so ingrained that
the inaccuracy lives on. In a standard two-wire electrical circuit one of
the wires is often called the neutral wire, even though it is not quite
accurate to do so. The advantage of using the term is that it is widely
understood. So it is with Foreign Keys, I think.
Having said that, is there a generic terminology to address the primary key
and foreign key issues in relatively few words? I wonder if Parent Key and
Child Key would make more sense (not that I expect any terminology to
change). After all, there are Link Parent and Link Child fields in a
subform control. Then again, these are not necessarily the so-called key
fields. For that matter, they aren't necessarily fields at all (or at least
the Parent field isn't).
To me, Primary and Foreign keys are understood widely enough that it is
pretty easy to move on to what one does with the fields.


"Jamie Collins" wrote in message
...


"BruceM" wrote:

Nope, didn't know about Ted Codd. Yes, I did know that a key can
comprise
more than one field. I probably misunderstood "How do I set two
fields in one table to be Foreign Keys?" I was thinking of a junction
table
(or whatever more appropriate terminology you prefer) which is on the
"many"
side of relationships with two separate tables. However, I realize now
that
the OP seems to have been referring to a multi-field PK. Let's say it's
two
fields. That means two corresponding fields in the Child table, but the
point remains that only the PK is defined in table design view.
Relationships establish the foreign keys (except for what you said about
using CREATE TABLE and unique constraints and all that). In fact, I'm
sure
there is all sorts of inaccurate terminology in what I have posted. My
only
intention was to try to help the OP with the problem at hand.

Fair enough.

FWIW I think it is the case that Ted Codd was making up the terminology as
he went along and 'foreign' is the term he chose, based on who knows what
reasons. I'm pretty sure he didn't have Access UI Table Design View in
mind,
though ;-)

Jamie.

--

BruceM wrote:

Sometimes the accepted terminology is inaccurate, but is so ingrained that
the inaccuracy lives on.

is there a generic terminology to address the primary key
and foreign key issues in relatively few words? I wonder if Parent Key and
Child Key would make more sense (not that I expect any terminology to
change). After all, there are Link Parent and Link Child fields in a
subform control. Then again, these are not necessarily the so-called key
fields. For that matter, they aren't necessarily fields at all (or at least
the Parent field isn't).
To me, Primary and Foreign keys are understood widely enough that it is
pretty easy to move on to what one does with the fields.

Good on you for taking an interest, BruceM.

I think it's a shame that most regulars in these groups seem to be
uninterested in using (or perhaps are not aware of) the SQL industry standard
terms (i.e. those terms used in the SQL standards and SQL literature), being
'referencing' and 'referenced' respectively.

I think 'referencing' and 'referenced' are particularly important because
they promote the correct mental model. 'Parent' and 'child' are not
appropriate because they relate to a hierarchy, a totally different concept.
The terms 'foreign key' and 'primary key' (or 'primary table') are not
directly equivalent to 'referencing' and 'referenced' respectively because a
referenced constraint can be any candidate key. Using the word 'primary' IMO
promotes the erroneous idea that only a PRIMARY KEY can be referenced when it
is actually the case that a UNIQUE constraint can also be referenced. This in
turn leads IMO to the erroneous belief that you only need to worry about
having a PRIMARY KEY on your table, to the point where candidate keys are
disregarded or not considered at all. And when you have regulars promoting
the use of an autonumber as the PRIMARY KEY with no candidate keys... well,
you can perhaps begin to appreciate how I feel like that small child pointing
at the Emperor...

I don't wish to appear too 'preachy' on this one, though. While I think it's
a shame, in all honesty I don't think it's a big deal. After all,
'Parent/child' and 'primary/foreign' are easily understood and when the
incentive to provide a 'correct' answer is a green tick next to your name
(and consider that those dishing out the green ticks may not be motivated by
promoting the correct mindset), then in some respects it is the system which
is to blame for the fact that not everyone goes the extra mile to ask, "Was
that the 'best' answer?"

Jamie.

--

I take it that a unique constraint may be a single field, indexed with no
duplicates, but that it may also be a combination of fields. Further, I
take it that any unique constraint may be referenced. I suppose a candidate
key is any field (or combination?) that can be referenced, or does it apply
to referencing fields (or combinations) as well, or none of the above.
If this understanding is correct, there must be occasions when a field or
combination other than the designated PK may be referenced by a field or
fields in another table. I suppose this means there is no need to actually
designate a field as the PK (although the bold type in the relationships
window is pretty handy).
Assuming the above points are correct, I still prefer to use the simplest
means of explaining to somebody who is just trying to solve the problem at
hand. However, I am also interested in a more generalized vocabulary that
may help me understand a wider range of writings.
Having said that, when I respond to a question in this forum I try to
understand the level of the person asking the question, and to respond
appropriately to that level. Sometimes that means, for instance, that I
will suggest using a control wizard, even though I am unlikely to do so
myself now that I have a bit more information under my belt. Similarly, I
will say that a PK is a single field, and leave compound keys for another
time, unless the situation demands a compound key. As you suggested, some
things just aren't that big a deal, at least not in some contexts.

"Jamie Collins" wrote in message
...

BruceM wrote:

Sometimes the accepted terminology is inaccurate, but is so ingrained
that
the inaccuracy lives on.

is there a generic terminology to address the primary key
and foreign key issues in relatively few words? I wonder if Parent Key
and
Child Key would make more sense (not that I expect any terminology to
change). After all, there are Link Parent and Link Child fields in a
subform control. Then again, these are not necessarily the so-called key
fields. For that matter, they aren't necessarily fields at all (or at
least
the Parent field isn't).
To me, Primary and Foreign keys are understood widely enough that it is
pretty easy to move on to what one does with the fields.

Good on you for taking an interest, BruceM.

I think it's a shame that most regulars in these groups seem to be
uninterested in using (or perhaps are not aware of) the SQL industry
standard
terms (i.e. those terms used in the SQL standards and SQL literature),
being
'referencing' and 'referenced' respectively.

I think 'referencing' and 'referenced' are particularly important because
they promote the correct mental model. 'Parent' and 'child' are not
appropriate because they relate to a hierarchy, a totally different
concept.
The terms 'foreign key' and 'primary key' (or 'primary table') are not
directly equivalent to 'referencing' and 'referenced' respectively because
a
referenced constraint can be any candidate key. Using the word 'primary'
IMO
promotes the erroneous idea that only a PRIMARY KEY can be referenced when
it
is actually the case that a UNIQUE constraint can also be referenced. This
in
turn leads IMO to the erroneous belief that you only need to worry about
having a PRIMARY KEY on your table, to the point where candidate keys are
disregarded or not considered at all. And when you have regulars promoting
the use of an autonumber as the PRIMARY KEY with no candidate keys...
well,
you can perhaps begin to appreciate how I feel like that small child
pointing
at the Emperor...

I don't wish to appear too 'preachy' on this one, though. While I think
it's
a shame, in all honesty I don't think it's a big deal. After all,
'Parent/child' and 'primary/foreign' are easily understood and when the
incentive to provide a 'correct' answer is a green tick next to your name
(and consider that those dishing out the green ticks may not be motivated
by
promoting the correct mindset), then in some respects it is the system
which
is to blame for the fact that not everyone goes the extra mile to ask,
"Was
that the 'best' answer?"

Jamie.

--