CODEWITHSUNDEEP
phppostgresqlsymfonydoctrine-orm
shapeshifter
shapeshifter

Reputation: 2967

Postgres Partial Unique Indexes in Symfony

Postgres can do partial unique indexes but I can't find any doctrine documention suggesting any support for this.

http://www.postgresql.org/docs/current/interactive/indexes-partial.html#INDEXES-PARTIAL-EX3

How in Symfony can I have an entity for example email. Where I want to store a boolean flag for the default email.

emails table looks like,

id,user_id,email,default
1,1,[email protected],false
2,1,[email protected],true
3,1,[email protected],false

I want a unique constraint on user_id and default = true.

SQL I'd do something like, 

CREATE UNIQUE INDEX default_constraint ON emails (user_id)
    WHERE default;

Is there another way to do this? I considered a prePersist function in the entity however this would require a db call to check.

Edit: I'm also considering a event listener, http://symfony.com/doc/current/cookbook/doctrine/event_listeners_subscribers.html

I'm not sure I want to listen to every prePersist event, can I listen to a specific entity instead of having to filter with an if statement. eg if ($entity instanceof Product) {

Upvotes: 3

Views: 1662

Answers (3)

Oleksii
Oleksii

Reputation: 31

write UniqueConstraint annotation

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(
 *     name="my_table",
 *     uniqueConstraints={
 *         @ORM\UniqueConstraint(name="uc__my_table__field", columns={"field"}),
 *     },
 * )
 */

and then try to generate migration

./bin/console doctrine:migrations:diff

Upvotes: 0

Lucas Granberg
Lucas Granberg

Reputation: 46

I think doctrine does support just that now! Look in the doctrine annotation reference, specifically at the @index section.

<?php
/**
 * @Entity
 * @Table(name="ecommerce_products",indexes={@Index(name="search_idx", columns={"name", "email"}, options={"where": "(((id IS NOT NULL) AND (name IS NULL)) AND (email IS NULL))"})})
 */
class ECommerceProduct
{
}

Upvotes: 1

Shane
Shane

Reputation: 96

There is a pretty neat construct in Postgres 9.0+ called the exclude constraint. It is briefly explained in CREATE TABLE's documentation. Since in this example we are only relying on the predicate portion of the exclude constraint and not the flexibility of the operators, I would use a B-tree index, despite the documentation's recommendation. You can read a bit about the different index types if you search for 'postgres index types'. Currently I can't post more than one link in an answer since I'm new here, so you'll just have to google it. :)

This fiddle is an example of how it works. Basically, the predicate at the end of the EXCLUDE turns the constraint into a UNIQUE that is only applied WHERE "default" = true.

This might be the best path if there may be other applications interacting with the table, but it might be quicker to provide feedback to your clients if the information is available and a constraint is applied on the front end.

Upvotes: 5

Related Questions