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):

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):

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

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...xes-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.

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