Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 10 total |
Migration & Compound Primary Key & NULLs |
Tue, Sep 16 2008 6:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 16 2008 6:35 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Sep 16 2008 7:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 16 2008 9:03 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Sep 17 2008 2:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 17 2008 5:59 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Sep 17 2008 6:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 17 2008 6:39 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Sep 17 2008 7:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 17 2008 7:23 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |