Required property of Primary Key fields

[samah]

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]

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?

 

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

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

 

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

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

 

[Jamie Collins]

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.

--

 

[samah]

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

 

[Jamie Collins]

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.

--

 

[tina]

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]

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


It makes no difference.

IMO nullable columns should be avoided, especailly in FKs.

Jamie.

Thank you for your time and guidance, Jamie.

-samah

 

[samah]

Thank you, tina.

-samah

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.


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

 

[Tony Toews [MVP]]

IMO nullable columns should be avoided, especailly in FKs.

Are there some constraints to that statement I'm missing?

Let's use the classic example of a second address line which is seldom
used but required in some situations. For example a c/o or some other
oddball situation. Or some date that isn't present yet.

What if you simply don't have a phone number?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

Are there some constraints to that statement I'm missing?

Of course, that being the nature of any 'rule of thumb'...

Let's use the classic example of a second address line which is seldom
used but required in some situations.

What does a NULL second address line mean? Known not to have one,
known to have one but don't know what it is, cannot apply, etc? I
prefer to use meaning placeholders e.g. '{{NONE}}', '{{NK}}',
'{{NA}}', etc with validation rules in context and meanings defined in
the data dictionary. In the case of second address line I would allow
'{{NONE}}' or a value specifying the allowable characters (NOT ALIKE '%
[! 0-9A-Z-,etc]%'), disallowing NULL, empty string, leading (white)
spaces, trailing spaces and multiple spaces.

Addresses are a tricky area. Are they attributes of an entity or are
they entities in their own right in to be shared in 1:m relationships
with entities of another type (e.g. people)? Are they simply the lines
of text required for the postal service to physically deliver mail or
must they be verifiable in reality e.g. to be able to serve a legal
person with court notices therefore disallowing post office boxes?

For example a c/o or some other
oddball situation.

It's a design principle (not my invention): a table models one kind of
thing. Have a table of c/o addresses (or an address_type column,
perhaps) and simply don't create a row if no such entity exists.

Or some date that isn't present yet.

I'll give you that one in the case of a subatomic value where an
actual value is to be reasonably expected at some future point
(transaction time), the classic example being a start_date and
end_date pair where the NULL value end_date signifies the period in
the current state (valid time). However, I've see some people use an
actual far future date value (e.g. #9999-12-31 23:59:59#) as the
'magic' date to signify infinity.

What if you simply don't have a phone number?

Have an 'all key' table of people (or whatever) known to have no phone
number.

Jamie.

--

 

[Jamie Collins]

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

No, it isn't possible.

Consider this simple example:

CREATE TABLE Test1
(
col1 INTEGER NOT NULL,
col2 INTEGER
)
;
ALTER TABLE Test1 ADD
PRIMARY KEY (col1, col2)
;
INSERT INTO Test1 (col1, Col2) VALUES (1, NULL)
;

While the ALTER TABLE (PK creation) with the NULLable column succeeds,
it's a bit pointless because the INSERT will fail with a "primary key
cannot contain a Null value" error.

....unless by 'primary key' you meant a logical key rather than the
arbitrary PRIMARY KEY designation. While you could use a UNIQUE
constraint (index no duplicates), be warned that Jet allows multiple
NULL values e.g.

CREATE TABLE Test2
(
col1 INTEGER NOT NULL,
col2 INTEGER
)
;
ALTER TABLE Test2 ADD
UNIQUE (col1, col2)
;
INSERT INTO Test2 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test2 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test2 (col1, Col2) VALUES (1, NULL)
;

All the above statements succeed but would you consider this table to
have a logical 'primary key'? I do not.

Also consider that those NULL values would all change in a single
CASCADE referential action e.g.

CREATE TABLE Test3
(
col1 INTEGER NOT NULL,
col2 INTEGER
)
;
ALTER TABLE Test3 ADD
UNIQUE (col1, col2)
;
ALTER TABLE Test3 ADD
FOREIGN KEY (col1, col2)
REFERENCES Test2 (col1, col2)
ON UPDATE CASCADE
;
INSERT INTO Test3 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test3 (col1, Col2) VALUES (1, NULL)
;
INSERT INTO Test3 (col1, Col2) VALUES (1, NULL)
;
UPDATE Test2
SET col2 = 1
;

The final statement fails with a "Cannot perform cascading operation.
It would result in a duplicate key in table ''." error (yes, that's
verbatim and yes the table name Test3 appears is blank due to a bug in
Jet/ACE).

Jamie

--

 

[Ken Snell \(MVP\)]

Of course, that being the nature of any 'rule of thumb'...

An example from one of my applications where a foreign key often is a Null
value -- a table that lists all items being ordered from a vendor on a
purchase order, with a foreign key to show the record entry in a
"backordered items" table if that item was originally a backordered item; if
not, the foreign key is Null, but if it is, the foreign key contains the
primary key value from that "backordered items" table.

Having a Null value in a foreign key field is a common feature in many of my
databases where that attribute is an optional one for the record.

 

[Tony Toews [MVP]]

Are there some constraints to that statement I'm missing?

Of course, that being the nature of any 'rule of thumb'...

Let's use the classic example of a second address line which is seldom
used but required in some situations.

What does a NULL second address line mean? Known not to have one,
known to have one but don't know what it is, cannot apply, etc? I
prefer to use meaning placeholders e.g. '{{NONE}}', '{{NK}}',
'{{NA}}', etc with validation rules in context and meanings defined in
the data dictionary. In the case of second address line I would allow
'{{NONE}}' or a value specifying the allowable characters (NOT ALIKE '%
[! 0-9A-Z-,etc]%'), disallowing NULL, empty string, leading (white)
spaces, trailing spaces and multiple spaces.

Addresses are a tricky area. Are they attributes of an entity or are
they entities in their own right in to be shared in 1:m relationships
with entities of another type (e.g. people)? Are they simply the lines
of text required for the postal service to physically deliver mail or
must they be verifiable in reality e.g. to be able to serve a legal
person with court notices therefore disallowing post office boxes?

For example a c/o or some other
oddball situation.

It's a design principle (not my invention): a table models one kind of
thing. Have a table of c/o addresses (or an address_type column,
perhaps) and simply don't create a row if no such entity exists.

Or some date that isn't present yet.

I'll give you that one in the case of a subatomic value where an
actual value is to be reasonably expected at some future point
(transaction time), the classic example being a start_date and
end_date pair where the NULL value end_date signifies the period in
the current state (valid time). However, I've see some people use an
actual far future date value (e.g. #9999-12-31 23:59:59#) as the
'magic' date to signify infinity.

What if you simply don't have a phone number?

Have an 'all key' table of people (or whatever) known to have no phone
number.

Your solutions would cause a lot more work for me the developer and
for the user. For, to me, next to no value. Thus I wouldn't
implement any of those solutions. I'd happily leave the fields null.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

Your solutions would cause a lot more work for me the developer and
for the user. For, to me, next to no value. Thus I wouldn't
implement any of those solutions. I'd happily leave the fields null.

Yes, I see this taken to its logical conclusion i.e. 100+ nullable
columns in a single table.

From what I read I understand you use spaces in metadata element names

(e.g. viewed tables) and you don't user level security at all, which I
(and others writing SQL against you database) would find very annoying
and simply unworkable respectively. Each to their own, I guess

Jamie.

--

 

[Tony Toews [MVP]]

(e.g. viewed tables)

No, I do not use spaces in table or field names. I do use them in
queries though.

and you don't user level security at all,
Correct.

which I
(and others writing SQL against you database) would find very annoying
and simply unworkable respectively. Each to their own, I guess

I don't understand what user level security has to do with queries.

And I find that it works quite well. Nothing irritating about it at
all.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

I do not use spaces in table or field names. I do use them in
queries though.


I don't understand what user level security has to do with queries.

Hint: use it to revoke permissions from base tables and instead grant
them on Jet VIEWs and PROCEDUREs (Access Query objects).

And I find that it works quite well. Nothing irritating about it at
all.

That your personal style does not irritate you is not news <g> nor is
it my point.

To clarify:
Spaces = very annoying for me.
No user level security = unworkable for me.

Imagine we were designing by a committee made up of all the regulars.
Do you think you'd manage to get you "No user level security" policy
ratified? You think you could impose spaces in object names (and the
resulting square brackets) on everyone, even if you limited it to
VIEWs and PROCEDUREs? I do not. Likewise I don't think I'd get my "No
nullable columns except for sub atomic elements" through (which would
include the second line of an address, incidentally) but I would lobby
to minimize the use of nullable columns.

We are allowed our individual styles only when we operate as
individuals or autocrats. This is my point.

Jamie.

--

 

[Jamie Collins]

An example from one of my applications where a foreign key often is a Null
value -- a table that lists all items being ordered from a vendor on a
purchase order, with a foreign key to show the record entry in a
"backordered items" table if that item was originally a backordered item; if
not, the foreign key is Null, but if it is, the foreign key contains the
primary key value from that "backordered items" table.

There is a design principle (not my invention) that a table models
either an entity or a relationship between entities but not both. You
appear to have a one-to-zero-or-one relationship between a 'purchase
order item' and a 'backordered item' which, in principle, should be
modelled using a relationship table (a.k.a. junction table, join
table, et al). Rather than making the key from the 'backordered item'
table nullable in this relationship table and redundantly adding a row
for *every* 'purchase order item', instead both keys should be
required and only add a row to the relationship table when a 'purchase
order item' actually has a corresponding 'backordered item'.

Jamie

--

 

[Ken Snell \(MVP\)]

You
appear to have a one-to-zero-or-one relationship between a 'purchase
order item' and a 'backordered item' which, in principle, should be
modelled using a relationship table (a.k.a. junction table, join
table, et al). Rather than making the key from the 'backordered item'
table nullable in this relationship table and redundantly adding a row
for *every* 'purchase order item', instead both keys should be
required and only add a row to the relationship table when a 'purchase
order item' actually has a corresponding 'backordered item'.

No, I am using the table for items on purchase orders to record every item
ordered on a purchase order. In each record is a field that either is Null
(not an item that was in the backorder table, awaiting to be put on a
purchase order) or is the value of the primary key in the backorder items
table (foreign key). Not all purchase order items were in the backordered
items table, so think of it as an optional attribute that just happens to be
a foreign key field too.

While usnig another table to be the junction between the backordered items
table and the purchase order items table is certainly feasible, it seemed to
be an unnecessary structure for my design.