Re: Dual Primary Keys

Null can and does mean many things.

One possible meaning is as a placeholder for a (non-null)
value that you expect to arrive. Other common meanings
are (ii) value does not exist, and (iii) value is not applicable
Historically, nulls have also been zero, false, and empty strings.

Of course, many people think that all nulls should be avoided,
or that multiple null types should be defined, but the debate
was by no means conclusive.

Since I'm not in his shoes, I can't tell why he has only
one table: if, as suggested, he uses two tables, he won't
have nulls.

(david)



"Jamie Collins" <jamiecollins@xxxxxxxxxx> wrote in message
news:1149581411.274400.176620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

david epsom dot com dot au wrote:
you can create a unique
index on the two fields (allowing nulls), as well as indexes
on each of the two fields.

You can't use the unique index (allowing nulls) on the two fields
as a primary key because primary keys can't allow nulls.

Null may not be appropriate here, anyhow. A null value is a placeholder
for a (non-null) value that you expect to arrive some time in the
future, for example a start_date and end_date pair where a null
end_date represents a current period. If you know the value is
'unknown' or 'does not apply' or 'the witness was unresponsive' then
use an appropriate (encoded) value.

This from Celko: "For example, the International classification of
Disease uses 999.999 for miscellaneous illness. It means we have
diagnosed the patient, know that he or she has an illness, and cannot
classify it - a scary condition for the patient - but this is not
quite the same things as a missing disease code (just admitted, might
not even be sick), an inapplicable disease code (pregnancy
complications in a male), an unknown disease code (sick and awaiting
lab results), or an error in the diagnosis (the patient's temperature
is recorded as 100 degrees Celsius, not Fahrenheit)."

So, if we either have a value or we know it is 'unknown' without
reasonable expectation of it becoming available then the 'unknown'
state can be encoded (also not that unknown in both columns would be
absurd and therefore should be prevented):

CREATE TABLE Things (
order_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT order_nbr__values
CHECK (order_nbr = '{{NK}}' OR order_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

inventory_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT inventory_nbr__values
CHECK (inventory_nbr = '{{NK}}'
OR inventory_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

CONSTRAINT order_nbr__inventory_nbr__interaction
CHECK (order_nbr <> '{{NK}}' OR inventory_nbr <> '{{NK}}'),

PRIMARY KEY (order_nbr, inventory_nbr)
);

Of course, you can make things easier on the front end developer (which
could be you <g>) by creating a PROCEDURE (parameter query) to replace
nulls and empty strings with '{{NK}}', pad the values with leading
zeros, etc.

It could be that nulls are appropriate, for example a row can be
inserted into this table before an order is generated, therefore a null
order_nbr is a placeholder for the forthcoming value, then a
'nullbuster' column can be added (note the now nullable order_nbr does
not need to be explicitly tested for null in the CHECK constraint):

CREATE TABLE Things2 (
order_nbr VARCHAR(10),

CONSTRAINT order_nbr__values
CHECK (order_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

inventory_nbr VARCHAR(10)
DEFAULT '{{NK}}' NOT NULL
UNIQUE,

CONSTRAINT inventory_nbr__values
CHECK (inventory_nbr = '{{NK}}'
OR inventory_nbr LIKE
'[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'),

CONSTRAINT order_nbr__inventory_nbr__interaction
CHECK (order_nbr IS NOT NULL OR inventory_nbr <> '{{NK}}'),

nullbuster VARCHAR(10) DEFAULT '{{NK}}' NOT NULL,
CONSTRAINT nullbuster__values
CHECK (nullbuster = IIF(order_nbr IS NULL, '{{NK}}', order_nbr)),

PRIMARY KEY (nullbuster, inventory_nbr)
);

The order_number and nullbuster columns must be kept in synch but again
the database developer could write procedures to assist (perhaps a
calculated column can be created in Access? I really don't know).

Jamie.

--



.

Relevant Pages

  • Re: So whats null then if its not nothing?
    ... >> According to the 3vl rules, whether two nulls are distinct or equal is ... it means effectively that unknown is treated as ... Clearly, you have a duplicate. ... the result is unknown and therefore the constraint is not violated. ...
    (comp.databases.theory)
  • Re: Dual Primary Keys
    ... You can't use the unique index (allowing nulls) on the two fields ... 'unknown' or 'does not apply' or 'the witness was unresponsive' then ... CONSTRAINT inventory_nbr__values ... nullbuster VARCHARDEFAULT '}' NOT NULL, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: UNIQUE constraint problem
    ... > Another mistake in the standard (and T-SQL, for being consistent). ... It's not the case that NULLs are treated differently in CHECK constraints. ... "A table check constraint is satisfied if and only if the specified <search ... the CHECK constraint allows the UNKNOWN case. ...
    (microsoft.public.sqlserver.programming)
  • Re: So whats null then if its not nothing?
    ... two nulls are not distinct; ... it means effectively that unknown is treated as ... constraints leads to tuples being rejected if false; ... The SQL unique constraint does not consider two ...
    (comp.databases.theory)
  • Re: So whats null then if its not nothing?
    ... >>> A SELECT DISTINCT (it is really too kind to call this SQL ... >> According to the 3vl rules, whether two nulls are distinct or equal is ... Now strip all columns not used in the UNIQUE constraint. ... Clearly, you have a duplicate. ...
    (comp.databases.theory)