#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    13

    Unique Indeces and null values


    Hi all. I have a table with the following structure (NOTE: I'm only describing the columns affected by the indeces in question):

    Code:
    cms=# \d cms_access;
    Table "cms_access"
        Column     | Type              | Modifiers                      
    ---------------+--------------------------------+---------------------------------------
     x_id          | integer           | not null default nextval('"cms_access_seq"'::text)
     a_id          | integer           | not null
     p_id          | integer           | not null
     c_id          | integer           | default null
     g_id          | integer           | not null
     u_id          | integer           | not null
    x_id is the primary key, so by default has a unique index created.

    I've also created a unique index on the a_id, p_id, c_id, g_id, and u_id columns.

    However, I was recently able to insert two identical columns into this table (x_id = 2 and x_id = 4):

    Code:
    cms=# select * from cms_access;
     x_id | a_id | p_id | c_id | g_id | u_id |
    ------+------+------+------+------+------+
        1 |    1 |    1 |      |    1 |    1 |
        2 |    1 |    2 |      |    1 |    1 |
        4 |    1 |    2 |      |    1 |    1 |
    Now, the default value for c_id is null, so I'm going to make what I think is a safe assumption in that unique indexes cannot include a column which contains a null value.

    However, to get all the functionality I need, I REALLY need to have c_id be allowed to be null.

    Can anyone advise me if this is indeed what's causing the problem, and if so if there is any way around it?

    Thanks very much in advance,

    Pablo
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    From the documentation:

    When an index is declared unique, multiple table rows with equal indexed values will not be allowed. NULL values are not considered equal.

    http://www.postgresql.org/docs/view....es-unique.html

    So the two records are both "unique" because the NULL associated with x_id = 2 does not "equal" the NULL associated with x_id = 4.

    The only solution I can think of is to have a shadow column that indicates the state of c_id. For example if c_id is NULL or a positive value, you would put a -1 into c_id_shadow if it was NULL or its actual postive value. Then you would create a unique index using c_id_shadow instead of c_id. Or you could create c_id_shadow as a varchar and insert character representations of c_id into it like "NULL", "1", "2", etc.

    This way c_id_shadow would always have a non-null value even when c_id is NULL and would also be useful for creating a unique index.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    13
    Hi there. Thanks for your reply. I've actually decided that I can change things such that c_id in cms_access will not be null, thereby solving the unique index problem.

    This table is part of the back-end to a CMS I'm building, and I wanted it null such that I could indicate via one record in the access table whether or not a user had access to all content object (referenced by c_id) for a specific page object (referenced in p_id). I've changed things such that when a user initially creates a page object, there is a default content object created as well, which solves the problem altogether.

    Thanks for the reference to the docs. Makes everything much clearer.

    Cheers,
    Pablo

IMN logo majestic logo threadwatch logo seochat tools logo