How to make composite uniqueness constraints?

[hipertracker]

I am surprised Piccolo can set only a single column to have a unique value. How to make composite unique constraints? It's a common task to have a unique index on more than one column. Is calling a raw SQL the only option at the moment?

[dantownsend]

@hipertracker It's not supported currently.

It seems like a popular request, so it's definitely on my radar. I imagine the API will look something like:

class MyTable(Table):
    name = Varchar()
    address = Text()
    
    @classmethod
    def extra_constraints(cls) -> t.List[Constraint]:
        return [UniqueTogether(cls.name, cls.address)]

Here's an issue for it: #172

[scott2b]

In the mean time, it is pretty straightforward to do this in a manual migration.

First, create a migration: piccolo migrations new myapp

Then edit the migration and implement the run function:

    async def run():
        q = "CREATE UNIQUE INDEX unique_column1_column2 ON mytable(column1, column2)"
        await DomainLabel.raw(q)

[scott2b]

Update on my manual fix:

When the new migration is created, the run function shows up as a non-async function nested under the async forwards function. I found that it works simply to make run async and fill in the function definition.

I am now using a migration syntax that looks like this:

from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from myapp.tables import MyTableClass

ID = "2023-12-08T15:28:22:019141"
VERSION = "0.121.0"
DESCRIPTION = ""

async def forwards():
    manager = MigrationManager(
        migration_id=ID, app_name="", description=DESCRIPTION
    )

    async def run():        
        q = "CREATE UNIQUE INDEX mytablename_unique_column1_column2 ON mytablename(column1, column2)"
        await MyTableClass.raw(q)

    manager.add_raw(run)
    return manager

[scott2b]

fwiw. I suppose it may be alternatively possible to run_sync on the raw query .. I just didn't do it that way.

[remyz17]

any update on this feature ?

[vinicius-oa]

Any update?

[sinisaos]

@vinicius-oa Here is a PR that will allow adding and droping unique constraints through migrations in the correct way. The usage will be

from piccolo.columns import Varchar
from piccolo.table import Table
from piccolo.constraint import UniqueConstraint

class Task(Table):
    name = Varchar()
    description = Varchar()
    task_constraints = UniqueConstraint(["name", "description"])

After that, we just make a new migration and run that new migration.
Everything works great and we have to wait for @dantownsend to do the final review.

[chilico]

How is the review going on this? We could really do with it.