CODEWITHSUNDEEP
symfonyormunique-constraint
Clément Andraud
Clément Andraud

Reputation: 9269

ORM UniqueConstraint, null value duplicate

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.

Upvotes: 9

Views: 3302

Answers (3)

Frank Drebin
Frank Drebin

Reputation: 1083

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.

Upvotes: 0

Jekis
Jekis

Reputation: 4695

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!

Upvotes: 7

madhukar
madhukar

Reputation: 156

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.

Upvotes: 3

Related Questions