Geek asks and answers

Lines that break unique constraints

I have a postgres 8.3.4 database. There is a table of names with a unique restriction on the UNIQ triplet (name, identifier, age). Somehow there are several rows that have been added to the database that cause a violation of this restriction.

My question is how is this possible? Should the database not cause an error when the first row was added that would violate the restriction?

name: text
id: integer is not null (fk to table id)
age: integer

+3
source share
2 answers

, , NULL UNIQUE.
(NULL, 1, 20) (name, id, age) , . NULL "".

NOT NULL ( NULL ).

NULL ( "" NULL). , name NULL:

CREATE UNIQUE INDEX tbl_id_age_name_null_idx ON my_table (id, age)
WHERE name IS NULL;

( "pete", 1, 20) ( "jane", 1, 20) (NULL, 1, 20) (, , ) , . dba.SE.

BTW: PostgreSQL . 9.1. , , 3.0.18. .

+5

.

, - .

, .

, :

SELECT  q2.*
FROM    (
        SELECT  name, id, age
        FROM    mytable
        GROUP BY
                name, id, age
        HAVING  COUNT(*) > 1
        ) q
JOIN    mytable q2
ON      (q2.name, q2.id, q2.age) IS NOT DISTINCT FROM (q.name, q.id, q.age)

, .

+4

More articles:


All Articles