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