Обсуждение: 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 >