Re: unique constraints

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Fri, 16 Dec 2016 17:37:57 +0200



    On 16/12/2016 17:30, Campbell, Lance
      wrote:

    
        PostgreSQL 9.5
         
        I have a table:
         
        CREATE TABLE test
        (
          id integer NOT NULL,
          tag_id integer,
          CONSTRAINT test_id_tag_id_key UNIQUE (id,
          tag_id)
        )
        WITH (
          OIDS=FALSE
        );
         
        I noticed I can insert this into the
          database:
         
        Insert into test (id, tag_id)
          values(1,null);
        Insert into test (id, tag_id)
          values(1,null);
         
        But this does not work:
         
        Insert into test (id, tag_id) values(2,1);
        Insert into test (id, tag_id) values(2,1);
          (error occurs)
         
        Select * from test;
         
        1, null
        1, null
        2, 1
         
        I understand why in the second set of
          inserts I get an error when trying to insert the same set of
          values.  But why does the first example work?  I would have
          thought that it would have only allowed the first insert to
          work.
        
      
    Because null is not a normal value you can compare against. Null
    basically is equivalent to "do not know". I haven't caught up wit
    the latest SQL flavors of pgsql, but old school you would do this by
    adding this :

    
    CREATE UNIQUE INDEX test_id_uk ON test(id) WHERE (tags_id IS
      NULL);

    
        Thanks,
         
        Lance
         
      
    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt