Support multi-column foreign keys

[andrew-farries]

The current implementation (https://github.com/xataio/pg-roll/pull/73 and https://github.com/xataio/pg-roll/pull/79) allows for foreign key constraints to be added to an individual columns, referencing one column in the target table.

This should be extended to allow foreign key constraints to be defined at the table level so they can span multiple columns.

[saintazunya]

Hi,

Firstly, I would like to express my gratitude for your efforts in developing and maintaining this valuable tool. It's been instrumental in many of my projects, and I truly appreciate the work you've put into it.

I'm writing to inquire about the current support for composite keys and indexes in pgroll. Based on my experiances, not only foreign keys are not supported, but also

• Composite Primary Keys
• Composite Unique Keys
• Composite Indexes

are not supported for now.

These features are crucial for the implementation of complex data models where single-column keys are insufficient to ensure data integrity and performance optimization.

Do we have a plan on supporting these yet?

Thanks.

[exekias]

Thank you for your feedback! This is indeed something we want to tackle soon, as it's an important limitation. Now that we have all the foundations in place it feels like we are ready to start working on this.

[SferaDev]

Also faced this in one of my tests:

CREATE TABLE IF NOT EXISTS "users_to_groups" (
        "id" serial PRIMARY KEY NOT NULL,
        "user_id" integer NOT NULL,
        "group_id" integer NOT NULL,
        CONSTRAINT "users_to_groups_group_id_user_id_pk" PRIMARY KEY("group_id","user_id")
);

Error: unable to execute start operation: pq: multiple primary keys for table "users_to_groups" are not allowed

[chris-braidwell]

Error: unable to execute start operation: pq: multiple primary keys for table "users_to_groups" are not allowed

This isn't a pgroll limitation. Postgres doesn't support multiple PKs in a table. You can create unique constraints that function very similarly to a PK (e.g. can be the parent side of an FK relationship) but there can be only one "primary" key.

[kvch]

@saintazunya I created a separate issue for your request: https://github.com/xataio/pgroll/issues/409