Required property of Primary Key fields

Jamie Collins wrote:

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/

On 4 Sep, 05:08, "Tony Toews [MVP]" wrote:
IMO nullable columns should be avoided, especailly in FKs.

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.

--

On 3 Sep, 16:45, "tina" wrote:
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

--

"Jamie Collins" wrote in message
ups.com...
On 4 Sep, 05:08, "Tony Toews [MVP]" wrote:
IMO nullable columns should be avoided, especailly in FKs.

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

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

Ken Snell
MS ACCESS MVP

Jamie Collins wrote:

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/

On 4 Sep, 18:03, "Tony Toews [MVP]" wrote:
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.

--

Jamie Collins wrote:

From what I read I understand you use spaces in metadata element names
(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/

On 6 Sep, 18:52, "Tony Toews [MVP]" wrote:
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.

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.

--

On 4 Sep, 16:15, "Ken Snell \(MVP\)"
wrote:
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

--

"Jamie Collins" wrote in message
ups.com...
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.
--

Ken Snell
MS ACCESS MVP