Multi-column foreign key constrait using postgresql, autogenerated migration fails

[Gastón Avila]

Hi all,

I used alembic to generate (auto) a migration which added a table to my postgresql DB which had a foreign key constraint matching two columns in the new table to two columns in an existing one. The two columns in the existing table where a joint primary key (hence unique). The generated migration had TWO lines for the foreign key constraint and that caused it to fail with this error

 

there is no unique constraint matching given keys for referenced table "agency_version"

 [SQL: '\nCREATE TABLE agency_tag (\n\tagency_id INTEGER NOT NULL, \n\ttransaction_id INTEGER NOT NULL, \n\tname VARCHAR(12), \n\tPRIMARY KEY (agency_id, transaction_id), \n\tFOREIGN KEY(agency_id) REFERENCES agency_version (agency_id), \n\tFOREIGN KEY(transaction_id) REFERENCES agency_version (transaction_id)\n)\n\n']

The migration had these lines

sa.ForeignKeyConstraint(['agency_id', 'transaction_id'], ['agency_version.agency_id'], ),
sa.ForeignKeyConstraint(['transaction_id'], ['agency_version.transaction_id'], ),

which when changed to 

sa.ForeignKeyConstraint(['agency_id', 'transaction_id'], ['agency_version.agency_id', 'agency_version.transaction_id'], ),

worked allright.

Is postgresql the only DBMS which will complain about this? It took me a while to figure this out.

Thanks

 

[Mike Bayer]

those mean two totally different things, and you likely want the latter.
You should make sure your original model / table metadata uses the
composite ForeignKeyConstraint as well, and not ForeignKey() which isn't
typically compatible with a composite primary key as a target.

>
>
> worked allright.
>
> Is postgresql the only DBMS which will complain about this? It took me a
> while to figure this out.
>
> Thanks
>
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Gastón J. Avila]

That clears it all up. I used __table_args__ and ForeignKeyConstrait. Alembic handled it perfectly.

Thanks Mike for your help and for creating these libraries.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy-alembic/UHv8WScQEP4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy-alem...@googlegroups.com.