[PostgreSQL] The best approach to create composite primary key

[baltic-tea]

What is the best approach to create a composite primary key in piccolo-orm?

Sample PostgreSQL table (DDL):

CREATE TABLE table_with_composite_pk (
	"UserId" BIGINT NOT NULL
	"User" TEXT NOT NULL,  
    "Clicks" INTEGER NOT NULL,                    
    "Updated" TIMESTAMPTZ DEFAULT NOW(), 
    "Created" TIMESTAMPTZ DEFAULT NOW(),  
    PRIMARY KEY ("UserID", "User", "Created")  -- constraint of primary key
)

An exception raises during the creation of a table with the following object structure:

class TableWithCompositePK(Table):
    UserId = BigInt(primary_key=True)
    User = Text(primary_key=True, null=True)
    Clicks = Integer(null=False)
    Updated = Timestamptz(auto_update=datetime.now)
    Created = Timestamptz(primary_key=True)

asyncpg.exceptions.InvalidTableDefinitionError: multiple primary keys for table "direct_data" are not allowed

[sinisaos]

@baltic-tea The issue with adding a composite unique constraint to multiple columns at once will be resolved by this PR. For now, the workaround is to use raw sql (in migrations or script) to add a composite unique constraint, as described here.
EDIT:
I made a branch in my fork that combines the two (in my opinion important PRs, composite unique constraints and reverse lookup). The usage is like this

pip install https://github.com/sinisaos/piccolo/archive/dev.zip

I hope that helps you.

[KianAnbarestani]

Piccolo ORM does not currently support composite primary keys directly via model definitions. As shown in your example, trying to mark multiple fields as primary_key=True leads to the InvalidTableDefinitionError.
Instead of using composite primary keys, do one of the following:

1. Use a surrogate primary key (e.g., id = UUID(primary_key=True)) and place a unique constraint on the desired fields:

from piccolo.columns import *
from piccolo.table import Table

class TableWithCompositePK(Table):
    id = UUID(primary_key=True)
    user_id = BigInt()
    user = Text()
    created = Timestamptz(default=datetime.utcnow)

    class Meta:
        unique_together = ("user_id", "user", "created")

2. If a real composite PK is necessary, define the table manually in raw SQL and interact with it via RawTable or SQLAlchemy instead, as Piccolo's ORM does not support it natively yet.

Let me know if you want a workaround using SQLAlchemy for that part.

[sinisaos]

1. Use a surrogate primary key (e.g., id = UUID(primary_key=True)) and place a unique constraint on the desired fields:

from piccolo.columns import *
from piccolo.table import Table

class TableWithCompositePK(Table):
    id = UUID(primary_key=True)
    user_id = BigInt()
    user = Text()
    created = Timestamptz(default=datetime.utcnow)

    class Meta:
        unique_together = ("user_id", "user", "created")

@KianAnbarestani Unfortunately, this will not work in Piccolo. You can try to do migrations and the constraints will not be added to the database. Piccolo does not use the Meta class and you can read about it here. This PR will fix this problem. Piccolo really makes it easy to use raw sql (both migrations or some other sql feature that is not available in Piccolo), and a workaround is described in the previous comments.