I'm in the middle of something a bit tedious and not very database
related so to relieve the boredom I thought I'd stir it up a bit here.

Almost all SQL DBMSs permit you to declare that a column or set of
columns is a logical key (i.e. PRIMARY KEY or UNIQUE). These keys can
be refererenced by foreign keys in other tables (or the same table).

So far so good.

The ANSI/ISO standard requires that all the components of a primary key
are implied to be NOT NULL. That is, the columns of the primary key
will be constrained to be not null whether you declare that constraint
or not. Ingres supports this behaviour as you can easily prove by
doing HELP on the table created like so:

create table pktest ( pk integer primary key );

Again, so far so good.

But the standard takes a different view when the key is declared UNIQUE.
The standard permits the columns of a unique "key" to be nullable.
Ingres does not. Again, do a help on the table created by:

create table uktest ( uk integer unique );

I don't actually have a problem with Ingres' behaviour here. I don't
see how it can ever make sense to allow multiple rows with the same
logical key (i.e. null). I say the standard is wrong to permit it, and
I've never found a case where I wanted to do it.

Of course Ingres making up its own rules creates a portability problem
because I know there are several SQL DBMSs that implement the
"correct" ANSI/ISO behaviour faithfully (right or wrong). There has
been talk of relaxing Ingres' implied NOT NULL constraint for UNIQUE
keys, presumably to make migration to Ingres easier.

So what?

I have to confess that I don't see why you'd ever want a "key" that
isn't a key. I can see why you'd want to port a database and data with
such existing keys from product X to Ingres. What I can't see is what
sort of real-world concept is modelled by doing that. Am I not being
imaginative enough?

The only possible reason I've come up with is the case where two or more
fact types are conflated in the same table, but that's no reason at all.
Am I being thick? Or is a non-unique key the logical blunder I think
it is?

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

Think of DBMS that consider an empty string as a NULL value.
Then a key like (first_name, middle_name, last_name) would end up in
values containing NULL values (as I don't have a middle name).


--
Bodo

I'd argue that allowing nullable fields in unique constraints was approaching a contradiction in terms.

But I could see that in the case of a multicolumn constraint demanding that all the columns must be non nullable may be overkill.

Marty


-----Original Message-----
From: Roy Hann [mailto:specially (AT) processed (DOT) almost.meat]
Sent: 17 August 2011 10:40
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Invitation to a quarrel

I'm in the middle of something a bit tedious and not very database
related so to relieve the boredom I thought I'd stir it up a bit here.

Almost all SQL DBMSs permit you to declare that a column or set of
columns is a logical key (i.e. PRIMARY KEY or UNIQUE). These keys can
be refererenced by foreign keys in other tables (or the same table).

So far so good.

The ANSI/ISO standard requires that all the components of a primary key
are implied to be NOT NULL. That is, the columns of the primary key
will be constrained to be not null whether you declare that constraint
or not. Ingres supports this behaviour as you can easily prove by
doing HELP on the table created like so:

create table pktest ( pk integer primary key );

Again, so far so good.

But the standard takes a different view when the key is declared UNIQUE.
The standard permits the columns of a unique "key" to be nullable.
Ingres does not. Again, do a help on the table created by:

create table uktest ( uk integer unique );

I don't actually have a problem with Ingres' behaviour here. I don't
see how it can ever make sense to allow multiple rows with the same
logical key (i.e. null). I say the standard is wrong to permit it, and
I've never found a case where I wanted to do it.

Of course Ingres making up its own rules creates a portability problem
because I know there are several SQL DBMSs that implement the
"correct" ANSI/ISO behaviour faithfully (right or wrong). There has
been talk of relaxing Ingres' implied NOT NULL constraint for UNIQUE
keys, presumably to make migration to Ingres easier.

So what?

I have to confess that I don't see why you'd ever want a "key" that
isn't a key. I can see why you'd want to port a database and data with
such existing keys from product X to Ingres. What I can't see is what
sort of real-world concept is modelled by doing that. Am I not being
imaginative enough?

The only possible reason I've come up with is the case where two or more
fact types are conflated in the same table, but that's no reason at all.
Am I being thick? Or is a non-unique key the logical blunder I think
it is?

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

On 17/08/2011 10:39, Roy Hann wrote:
snip

I can see a case for a unique key with null, but not based on a single
column. I might be barking up the wrong tree altogether.

Lets say you wanted a unique id for somebody - fred bloggs. In an ideal
world you would also have a social security number, passport number,
driving licence, blockbuster membership number, library card number -
you get the idea. Many Fred Bloggs, but the others are unique.

In practice, you might have only some of these fields, but whatever the
combination of missing fields you should be able to establish a unique
key even with values missing(null).

So lets say that for ID purposes I have a primary key based upon SS
Number, Passport Number, Driving Licence Number.

I might have a unique key for each of these fields, yet it may contain
null entries. So to identify my person, I want any or all of these
fields to be present, and if they are present the value must be unique
in the given column and therefore the value for the primary key is also
unique too because I need my primary key to have one of these three
columns with a non-null value.

It seems to me to be good because it allows keys to be incomplete yet
unique.

Do I have this all wrong?

On 17/08/2011 12:30, On net wrote:
That should have been more like

I can see a case for a unique key with null, but not a primary key based
on a single column with null, but a primary key with nullable columns is
fine (provided one column is not null and is unique).

Who could pass up a forum title like that? ;-)

Sounds like a major manufacturer had some undue influence and got
something silly in the standard to which everyone followed along.

There are those that absolutely hate null in all its forms. I'm OK
with null as an "I haven't a clue" value but I don't agree with the idea
of null being part of a unique value for the very same reasons it can't
be in a key. Designers should find a natural, simple key to tables.

Sadly, Ingres should probably support it because of standards and
compatibility. We can classify the feature under the category: "Here,
let me play out some more rope for you to hang yourself with"


--
daryl.monge (AT) ingres (DOT) com
------------------------------------------------------------------------
daryl.monge (AT) ingres (DOT) com's Profile: http://community.ingres.com/forum/member.php?userid=736
View this thread: http://community.ingres.com/forum/sh...ad.php?t=13811

Ingres Forums wrote:

There is (at least) one well-known product that makes that error. Let's
ignore products that do that. I think that's a fair exclusion because
I am interested in some ideal ANSI/ISO implementation of SQL.

I don't think that's a very satisfying example. In the first place no
designer would ever assume the first_name, middle_name, last_name is a
logical key. It certainly isn't in my family. In fact my mother was
recently showing me some old records that show a child died and later
its parents gave another baby the same name.

Another reason I don't find it very satisfying is because the problem
arises from parsing the name, rather than from having three
fundamentally distinct attributes.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

On net wrote:

I completely agree people design tables like that. I'd even go so far
as to say they usually/always design tables like that. But my question
was "what real world concept is modelled by doing that?" Reading
between the lines, you'd be right to infer that I think such a table
does not in fact model anything in the real world.

That's not a logical key. A logical key is irreducible. That's three
candidate keys.

If you don't know values for all the columns, at the same time, that is
a good clue that you are dealing with multiple fact types. They may
all be facts about the same key, but that doesn't make them attributes
of the same fact.

On the basis that there is no limit on the number of tables you can
have, and following the principle that the database design should be as
simple as possible but no simpler (or to put it differently, the
design should reveal complexity without creating any) those "fields"
belong in three different tables, within which each of them is a key
(and hence non-nullable--so that problem goes away).

I ain't gonna say that but I've set out all dots if you want to join
them up. :-)

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

On net wrote:

The desired characteristics of a logical key are:
irreducibility, uniqueness, stability, familiarity and simplicity. A
"key" that can be reduced and remain unique is not a key.

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

Martin Bowes wrote:

Really? What if the only difference between two rows in a projection
over those columns was the presence of a value in one and a null in the
other. Would you be confident there were really are two rows at all?

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk