ORM UniqueConstraint, null value duplicate

Question

I have an entity with an unique constraint on 3 fiels.

2 fields are not nullable, and the third is nullable.

My problem, when the third field is null, the unique constraint doesn't work, so i can have in my database some duplicate values like (5,1,null)(5,1,null)

For me, (5,1,null) is an unique value, with null too.

Do you have any ideas ?

This is my ORM line :

* @ORM\Table(name="table", uniqueConstraints={@ORM\UniqueConstraint(name="table_idx", columns={"field1", "field2", "field3"})})

Edit : The nullable value is a foreign key to an other entity, so i can't put a custom value. It's an entity or null.

Not sure what you mean, but quoting the docs `a UNIQUE index permits multiple NULL values for columns that can contain NULL`: http://dev.mysql.com/doc/refman/5.0/en/create-index.html — Alberto Fernández, Apr 02 '14 at 10:14
I don't want permits multiple null value. For me null is a real value. — Clément Andraud, Apr 02 '14 at 10:18
That is because NULL is an unknown value and the result of comparing two unknown values is also unknown. Maybe you could use a special value instead of NULL? — Eelke, Apr 02 '14 at 10:18
i can't, the third value is a foreign key to an other entity. so it is an entity or null — Clément Andraud, Apr 02 '14 at 10:20

score 6 · Answer 1 · answered Jun 22 '17 at 17:24

This could be achieved using two partial indexes. But doctrine annotations is not enough. We should add some SQL to solve the issue.

NOTE! I'm using PostgreSQL and migrations in my project.

First create 2 unique indexes (I use YAML):

uniqueConstraints:
    table_idx_2_fields:
        columns: [ field1, field2 ]
    table_idx_3_fields:
        columns: [ field1, field2, field3 ]

Then generate migration class using console:

php app/console doctrine:migrations:diff

SQL will be generated, but must be modified a little bit (WHERE clauses added)

class Version20170622165834 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // ...

        $this->addSql('
            CREATE UNIQUE INDEX table_idx_2_fields ON tbl (field1, field2)
            WHERE field3 IS NULL;
        ');
        $this->addSql('
            CREATE UNIQUE INDEX table_idx_3_fields ON tbl (field1, field2, field3)
            WHERE field3 IS NOT NULL;
        ');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // ...
    }
}

Execute generated SQL (migrate):

php app/console doctrine:migrations:migrate -n

Done!

score 3 · Answer 2 · answered Oct 07 '14 at 14:15

Am also faced the same issue, didn't find any way to resolve it.

Finally added one more varchar column to my table, its value is combination of 3 fields. ex:5_1_0(manually checking and adding 0 for null constraint otherwise constraint Id) and added unique constraint on new column and removed existing constraint.

score 0 · Answer 3 · edited Jan 29 '21 at 10:59

I've had a similar case where A was always set, B and C were nullable but one of them always set.

So I had these possibilities:

A.  B.   C.
123-null-789
123-456-null

I've tried to create a UniqueConstraint as a combination of A-B-C, but this didn't work.

What has worked was two UniqueConstraints with the where option:

 @ORM\UniqueConstraint(
      name="unique-key-1",
      columns={"a", "b"},
      options={"where": "b IS NOT NULL"}
   ),
 @ORM\UniqueConstraint(
      name="unique-key-2",
      columns={"a", "c"},
      options={"where": "c IS NOT NULL"}
   )

That way the unique keys worked for the correct cases.

ORM UniqueConstraint, null value duplicate

3 Answers3

Linked