Tim

---------------------------
Dialtomdi
---------------------------
ElevateDB Error #1004 The primary key constraint PrimaryKey for the table Projects has been violated (NULL values are not allowed in a primary key)
---------------------------
OK  
---------------------------

This table has a compound primary key - _ID & _Users. Looking at the DBISAM version of the file one record has an _ID of 2 and _Users is NULL so the primary key is not null, but does this mean that all columns in a primary key have to be non null?


Roy Lambert

Roy,

<< This table has a compound primary key - _ID & _Users. Looking at the
DBISAM version of the file one record has an _ID of 2 and _Users is NULL so
the primary key is not null, but does this mean that all columns in a
primary key have to be non null? >>

Yes, see here:

http://www.elevatesoft.com/incident?action=viewrep&category=edb&release=2.01&type=f&incident=2764

The manual needs updating on this point, however.  The SQL manual did not
get rebuilt properly for 2.01 B5 with these updates.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I knew I'd seen something about it but couldn't find it.

Is that another part of the moronic standard? If so don't try and explain why I'l just grumble to myself, if not please explain because it doesn't make sense to me. I know, accept and agree that the primary key shouldn't be null and should be unique but to say that all columns can't be null baffles me.

Roy Lambert

Roy,

<< Is that another part of the moronic standard? If so don't try and explain
why I'l just grumble to myself, if not please explain because it doesn't
make sense to me. I know, accept and agree that the primary key shouldn't be
null and should be unique but to say that all columns can't be null baffles
me. >>

Basically the idea is that the primary key is all that it encompasses, so
that any column in the primary key being NULL violates that constraint.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Basically the idea is that the primary key is all that it encompasses, so
>that any column in the primary key being NULL violates that constraint.

Not wanting to start another long debate that goes nowhere I'll just say

1. my brain refuses to understand that
2. I fortunately only have one table this bollixes (all the other compound PKs are such that none of the columns can be null)
3. thankfully it doesn't apply to other indices (does it?)

Roy Lambert

Roy,

<<1. my brain refuses to understand that >>

It's an "all-or-nothing" proposition.  The primary key is all of the columns
that make up the key, therefore if any are NULL then the rule about the
primary key not being allowed to be NULL has been violated.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>It's an "all-or-nothing" proposition. The primary key is all of the columns
>that make up the key, therefore if any are NULL then the rule about the
>primary key not being allowed to be NULL has been violated.

So if any column in the PK is NULL the PK is NULL.

Does that logic also apply to other compound indices?

Roy Lambert

Roy,

<< So if any column in the PK is NULL the PK is NULL. >>

Yes.

<< Does that logic also apply to other compound indices? >>

A primary key is not an index, so your question doesn't really apply unless
you're talking about unique or foreign key constraints, in which case no,
they don't have the NOT NULL rule like primary key constraints.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>A primary key is not an index,

That's why I couldn't/can't get my brain round it - at some point I may make the distinction between a primary key and an index but don't bet on it

I can see a great opportunity for a Monty Python sketch here based on when is an index not an index.

>so your question doesn't really apply unless
>you're talking about unique or foreign key constraints, in which case no,
>they don't have the NOT NULL rule like primary key constraints.

The question, obviously badly phrased, was based on the sql dictum that NULL and anything or operated on by anything is NULL and it looks as though that's being applied here and wondering what happens in the case of a compound index when a column is NULL - does this render the whole index for that row NULL or what?

eg How would this sort if these were the columns in a compound index

1 NULL 4
2 3 5
3 7 NULL
NULL 1 2

Roy Lambert

Roy,

<< The question, obviously badly phrased, was based on the sql dictum that
NULL and anything or operated on by anything is NULL and it looks as though
that's being applied here and wondering what happens in the case of a
compound index when a column is NULL - does this render the whole index for
that row NULL or what? >>

No, not at all.  As I said, an index is completely different from a primary
key, which is a constraint.

<< eg How would this sort if these were the columns in a compound index >>

NULLs always sort at the beginning of a range of values for a given column
in an index.

--
Tim Young
Elevate Software
www.elevatesoft.com