Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table like the following:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);

And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);

But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.

How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?

share|improve this question

migrated from stackoverflow.com Dec 27 '11 at 14:13

This question came from our site for professional and enthusiast programmers.

    
INSERT INTO my_table VALUES (NULL, NULL, 1, 2) will return Error because id_A cannot be Null. If you mean INSERT INTO my_table VALUES (NULL, 1, 2, NULL), yes, you are right, both Inserts will pass fine. –  ypercube Dec 27 '11 at 9:14
    
What does NULL stand for in column id_C? Will you later be changing these NULLs into actual values? –  ypercube Dec 27 '11 at 9:17
    
my mistake, question edited. id_C stand for an optionnal relation to another table (foreign key). –  Manuel Leduc Dec 27 '11 at 9:23
    
So, say you have values (1,2,1) and (1,2,2) in the (A,B,C) columns. Should a (1,2,NULL) be allowed to be added or not? –  ypercube Dec 27 '11 at 9:27
1  
Are there any values that will never appear in those columns (like negative values?) –  a_horse_with_no_name Dec 27 '11 at 10:43

3 Answers 3

up vote 25 down vote accepted

You can do that in pure SQL. Create a partial unique index in addition to the one you have:

CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;

This way you can have (1, 2, 1) and (1, 2, 2) and (1, 2, NULL) for (a, b, c) in your table, but none of these a second time.


Additional notes

  • No use for mixed case identifiers without double quotes in PostgreSQL.

  • You might consider using a serial column as primary key:

    CREATE TABLE my_table (
      my_table_id bigserial PRIMARY KEY
     ,id_a int8 NOT NULL
     ,id_b int8 NOT NULL
     ,id_c int8
     ,CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
    );
    

If you do not expect more than 2 billion rows (> 2147483647) over the course of time, you might want to use a plain int4 serial column as primary key. Similar considerations for the rest of the columns. Plain integers (int4) are smaller and a bit faster.

share|improve this answer
1  
I can confirm that the partial index is working very well :) I'm actually not dba on my projet but I will tramsit advices to relevant people. –  Manuel Leduc Dec 27 '11 at 14:28

A Null can mean that value is not known for that row at the moment but will be added, when known, in the future (example FinishDate for a running Project) or that no value can be applied for that row (example EscapeVelocity for a black hole Star).

In my opinion, it's usually better to normalize the tables by eliminating all Nulls.

In your case, you want to allow NULLs in your column, yet you want only one NULL to be allowed. Why? What kind of relationship is this between the two tables?

Perhaps you can simply change the column to NOT NULL and store, instead of NULL, a special value (like -1) that is known never to appear. This will solve the uniqueness constraint problem.

share|improve this answer
    
This is is a good point. Often when you are trying to limit a column to one and only one null, you're abusing null, and using it like a value. –  Scott Marlowe Dec 28 '11 at 6:13
    
In my case NULL is really NULL (id_C is a foreign key to table_c for exemple so it can't have -1 value), it means their is no relationship between "my_table" and "table_c". So it has a functional signification. By the way [(1, 1,1,null), (2, 1,2,null), (3,2,4,null)] is a valid list of inserted data. –  Manuel Leduc Dec 28 '11 at 9:40
1  
It's not really a Null as used in SQL because you want only one in all rows. You could change your database schema either by adding the -1 to table_c or by adding another table (which would be supertype to subtype table_c). –  ypercube Dec 28 '11 at 9:48
1  
I'd just like to point out to @Manuel that the opinion on nulls in this answer is not universally held, and is much debated. Many, like me, think that null can be used for any purpose you wish (but should only mean one thing for each field and be documented, possibly in the field name or a column comment) –  Jack Douglas Dec 29 '11 at 7:03
1  
+1 I am with you: if we want some combination of columns to be unique, then you need to consider an entity in which this combination of columns is a PK. The OPs' database schema should probably change to a parent table and a child one. –  A-K Nov 11 '13 at 22:23

I had the same problem and I found another way to have unique NULL into the table.

CREATE UNIQUE INDEX index_name ON table_name( COALESCE( foreign_key_field, -1) )

In my case, the field foreign_key_field is a positive integer and will never be -1.

So, to answer Manual Leduc, another solution could be

CREATE UNIQUE INDEX  u_constrainte UNIQUE(COALESCE(id_a, -1), COALESCE(id_b,-1),COALESCE(id_c, -1) )

I assume that ids won't be -1.

What is the advantage on creating a partial index ?
In case where you don't have the NOT NULL clause, id_a, id_b and id_c can be NULL together only once.
With a partial index, the 3 fields could be NULL more than once.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.