Thread: UNIQUE KEY with null value...

Hi,
I´m a brazilian programmer!

I have a question about Postgres UNIQUE KEY. Look:

(Step 1)
CREATE TABLE test (
id integer not null primary key,
id_uni integer not null,
cod integer,
name varchar(40),
constraint test_uk unique(id_uni, cod)
)

(Step 2)
insert into test (id, id_uni, cod, name)
values (1, 1, null, 'A')

(Step 3)
insert into test (id, id_uni, cod, name)
values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]

Why postgresql don´t break insertion in this case????

Thanks
Diego Ziquinatti
Brazil

Diego wrote:
> Hi,
> I´m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don´t break insertion in this case????
>
> Thanks
> Diego Ziquinatti
> Brazil


Diego,

In PostgreSQL, NULL is not considered a unique value, as it represents
an "unknown" value.  From the docs regarding unique constraints:

"...null values are not considered equal in this comparison. That means
even in the presence of a unique constraint it is possible to store an
unlimited number of rows that contain a null value in at least one of
the constrained columns."

See:
http://www.postgresql.org/docs/8.0/interactive/ddl-constraints.html#AEN1996

Regards,
Eric Faulhaber

On Tue, 1 Nov 2005, Diego wrote:

> Hi,
> I´m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don´t break insertion in this case????

AFAICS, SQL (at least 92) says that those do not violate UNIQUE.

The unique predicate on which the UNIQUE constraint is defined says:

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

The unique constraint for the above is effectively
 UNIQUE (SELECT id_uni, col FROM test)
The two rows in question look like
(1,null) and (1,null).

There are no rows in that would make the predicate return false AFAICS.

http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html

 For the purpose of a unique constraint, null values are not considered
 equal.

On Tue, Nov 01, 2005 at 03:09:24PM -0300, Diego wrote:
> Hi,
> I?m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don?t break insertion in this case????
>
> Thanks
> Diego Ziquinatti
> Brazil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

there are answers why it is that, here is how you can surround the problem
you can create the unique index after you create the table, something like

CREATE TABLE test (
id integer not null primary key,
id_uni integer not null,
cod integer,
name varchar(40));

CREATE UNIQUE INDEX test_id_uni_cod ON test (id_uni, cod) WHERE cod IS
NOT NULL;
CREATE UNIQUE INDEX test_id_uni ON test (id_uni) WHERE cod IS NULL;


Diego wrote:
> Hi,
> I´m a brazilian programmer!
>
> I have a question about Postgres UNIQUE KEY. Look:
>
> (Step 1)
> CREATE TABLE test (
> id integer not null primary key,
> id_uni integer not null,
> cod integer,
> name varchar(40),
> constraint test_uk unique(id_uni, cod)
> )
>
> (Step 2)
> insert into test (id, id_uni, cod, name)
> values (1, 1, null, 'A')
>
> (Step 3)
> insert into test (id, id_uni, cod, name)
> values (2, 1, null, 'B') =========> [Have the same UNIQUE KEY]
>
> Why postgresql don´t break insertion in this case????
>
> Thanks
> Diego Ziquinatti
> Brazil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>