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.

Answer 1

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!