Required property of Primary Key fields

Access Novice - WinXp/Access 2003.

I am a self taught access newbie. I have spent almost a year browsing
these newsgroups as a daily ritual. Although these newsgroups have given a
lot of enlightenment to a Access dummy, I am still not able to get a
complete grip on it. So here is what seems to be another stupid question
(at least after spending so much time on Access!).

All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

Thank you in advance.

-samah

I would have thought a primary key is always required if a table is related
to another (if that is what you are asking). Otherwise how can the records be
related to each other?

--

http://www.ready4mainstream.ny911truth.org/index.html


"samah" wrote:

Access Novice - WinXp/Access 2003.

I am a self taught access newbie. I have spent almost a year browsing
these newsgroups as a daily ritual. Although these newsgroups have given a
lot of enlightenment to a Access dummy, I am still not able to get a
complete grip on it. So here is what seems to be another stupid question
(at least after spending so much time on Access!).

All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

Thank you in advance.

-samah

scubadiver wrote:
I would have thought a primary key is always required if a table is related
to another (if that is what you are asking). Otherwise how can the records be
related to each other?


Thank you scubadiver. In Northwind sample database there are 8 tables.
In 6 of the tables Primary Key is set to autonumber, so the Required
Property is not applicable. Please take a look at the other 2 tables.

Table Primary Key Data Type Required Property
Customers CustomerID Text No

OrderDetails OrderID Number No
ProductID Number Yes

-samah

scubadiver wrote:
I would have thought a primary key is always required if a table is related
to another (if that is what you are asking). Otherwise how can the records be
related to each other?


.... and you misunderstood my question. It is not about the Primary Key
itself. It is about the Required Property of the Primary Key. Both are
different if I am not mistaken.

Thank you.

-samah

On 3 Sep, 07:53, samah wrote:
All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

While you can indeed use PRIMARY KEY designation for nullable columns
(Required = No) in Jet, you will not be able to set any of the the
PRIMARY KEY columns to be the NULL value. IMO this is a bug e.g. SQL
Server will generate an error when attempting to create a PK on
nullable columns.

If you need columns to be nullable, use a UNIQUE constraint (Index no
duplicates) instead, which can still be used for FOREIGN KEY
references.

Jamie.

--

Jamie Collins wrote:
On 3 Sep, 07:53, samah wrote:
All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

While you can indeed use PRIMARY KEY designation for nullable columns
(Required = No) in Jet, you will not be able to set any of the the
PRIMARY KEY columns to be the NULL value. IMO this is a bug e.g. SQL
Server will generate an error when attempting to create a PK on
nullable columns.

If you need columns to be nullable, use a UNIQUE constraint (Index no
duplicates) instead, which can still be used for FOREIGN KEY
references.

Jamie.

--



Jamie, thank you for the insight.

If I don't need columns to be nullable, can I assume that setting the
PRIMARY KEY field's Required Property to YES or NO doesn't make any
difference?

What about a composite PRIMARY KEY consisting of two FOREIGN KEYS? If I
set the keys' Required Property both to YES, both to NO or one to YES
and the other to NO, will it make any difference?

Thank you in advance.

-samah

On 3 Sep, 14:27, samah wrote:
Jamie Collins wrote:
On 3 Sep, 07:53, samah wrote:
All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

While you can indeed use PRIMARY KEY designation for nullable columns
(Required = No) in Jet, you will not be able to set any of the the
PRIMARY KEY columns to be the NULL value. IMO this is a bug e.g. SQL
Server will generate an error when attempting to create a PK on
nullable columns.

If you need columns to be nullable, use a UNIQUE constraint (Index no
duplicates) instead, which can still be used for FOREIGN KEY
references.

Jamie.

--

Jamie, thank you for the insight.

If I don't need columns to be nullable, can I assume that setting the
PRIMARY KEY field's Required Property to YES or NO doesn't make any
difference?

The difference is between the SQL update (INSERT/UPDATE) failing a NOT
NULL constraint and failing a PRIMARY KEY constraint. In either case
the net result is the same i.e. the SQL update fails.

I say, make the columns NOT NULL (Required = Yes).

What about a composite PRIMARY KEY consisting of two FOREIGN KEYS? If I
set the keys' Required Property both to YES, both to NO or one to YES
and the other to NO, will it make any difference?

It makes no difference.

IMO nullable columns should be avoided, especailly in FKs.

Jamie.

--

All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'.

i would agree that single-field primary keys should always be Required =
Yes. as for composite keys, i suppose it's possible that a valid value for
all but one of the individual fields in the key might be Null, but that
seems odd to me - and difficult to enforce. my recommendation would be that
you do not use fields that may contain a Null value as single or composite
primary keys.

But after started
exploring a few sample databases in depth, I realized it is not so.

keep in mind that Microsoft's sample databases (and no doubt other sample
dbs as well) are not always written perfectly. just because you see
something in a sample db, that doesn't necessarily mean it's the best - or
even correct - design.

hth


"samah" wrote in message
...
Access Novice - WinXp/Access 2003.

I am a self taught access newbie. I have spent almost a year browsing
these newsgroups as a daily ritual. Although these newsgroups have given a
lot of enlightenment to a Access dummy, I am still not able to get a
complete grip on it. So here is what seems to be another stupid question
(at least after spending so much time on Access!).

All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

Thank you in advance.

-samah

Jamie Collins wrote:
On 3 Sep, 14:27, samah wrote:
Jamie Collins wrote:
On 3 Sep, 07:53, samah wrote:
All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.
May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.
While you can indeed use PRIMARY KEY designation for nullable columns
(Required = No) in Jet, you will not be able to set any of the the
PRIMARY KEY columns to be the NULL value. IMO this is a bug e.g. SQL
Server will generate an error when attempting to create a PK on
nullable columns.
If you need columns to be nullable, use a UNIQUE constraint (Index no
duplicates) instead, which can still be used for FOREIGN KEY
references.
Jamie.
--
Jamie, thank you for the insight.

If I don't need columns to be nullable, can I assume that setting the
PRIMARY KEY field's Required Property to YES or NO doesn't make any
difference?

The difference is between the SQL update (INSERT/UPDATE) failing a NOT
NULL constraint and failing a PRIMARY KEY constraint. In either case
the net result is the same i.e. the SQL update fails.

I say, make the columns NOT NULL (Required = Yes).

What about a composite PRIMARY KEY consisting of two FOREIGN KEYS? If I
set the keys' Required Property both to YES, both to NO or one to YES
and the other to NO, will it make any difference?

It makes no difference.

IMO nullable columns should be avoided, especailly in FKs.

Jamie.

--





Thank you for your time and guidance, Jamie.

-samah

Thank you, tina.

-samah


tina wrote:
All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'.

i would agree that single-field primary keys should always be Required =
Yes. as for composite keys, i suppose it's possible that a valid value for
all but one of the individual fields in the key might be Null, but that
seems odd to me - and difficult to enforce. my recommendation would be that
you do not use fields that may contain a Null value as single or composite
primary keys.

But after started
exploring a few sample databases in depth, I realized it is not so.

keep in mind that Microsoft's sample databases (and no doubt other sample
dbs as well) are not always written perfectly. just because you see
something in a sample db, that doesn't necessarily mean it's the best - or
even correct - design.

hth


"samah" wrote in message
...
Access Novice - WinXp/Access 2003.

I am a self taught access newbie. I have spent almost a year browsing
these newsgroups as a daily ritual. Although these newsgroups have given a
lot of enlightenment to a Access dummy, I am still not able to get a
complete grip on it. So here is what seems to be another stupid question
(at least after spending so much time on Access!).

All this time I was under the misconception that Required Property of
primary keys and composite primary keys consisting of two or more
foreign keys, should always be set to 'Yes'. But after started
exploring a few sample databases in depth, I realized it is not so.

May be I am missing a very important concept here and shall be thankful
if someone could shed some light on it.

Thank you in advance.

-samah