[GH-ISSUE #24] Composite primary keys #11

New issue

Open

opened 2026-02-26 01:32:53 +03:00 by kerem · 1 comment

kerem commented

2026-02-26 01:32:53 +03:00

Owner

Originally created by @filmackay on GitHub (Jan 23, 2016).
Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/24

As far as I can tell there is no support for these (multiple columns in the primary key). @jeffknupp can you outline your view on this - are they supported, to what extent, do you think they should be etc.?

Originally created by @filmackay on GitHub (Jan 23, 2016). Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/24 As far as I can tell there is no support for these (multiple columns in the primary key). @jeffknupp can you outline your view on this - are they supported, to what extent, do you think they should be etc.?

kerem commented

2026-02-26 01:32:54 +03:00

Author

Owner

@Carelvd commented on GitHub (Nov 5, 2019):

I'm not sure this is a spot on answer but I've gotten compound keys to work. If you have two tables, TABLE and FOREIGN, both of which are auto-mapped. One can subclass the latter table as follows :

class FOREIGN(AutomapModel):

__tablename__ = "ForeignTable"
__endpoint__  = "android"

fk_A   = Column("A", Integer)
fk_B = Column("B", Integer)

__table_args__ = (ForeignKeyConstraint((fk_A, fk_B), ("TABLE.A","TABLE.B")), {})
# __table_args__ = (ForeignKeyConstraint((fk_A, fk_A), (TABLE.A, TABLE.B)), {}) # If TABLE is itself defined as an AutoModel subclass before this table and in the same file.

I got this working after reworking the Sandman 2 code base a little, I've submitted a PR accordingly but I'm awaiting acceptance/rejection thereof. If you want to pull my variant let me know and I'll post the URL. Then one might ask if you have to map the other fields in FOREIGN and the answer is not SQLAlchemy handles this for you. If you define your own TABLE use the second variant of __table__args__

Within my own notes I have the following links 1 is mostly thoeretical, 6 discusses foreign keys, 2, 3, 4 and 7 are more convenient for resolving the problem and 5 provides a nice script for identifying compound keys.

@Carelvd commented on GitHub (Nov 5, 2019): I'm not sure this is a spot on answer but I've gotten compound keys to work. If you have two tables, TABLE and FOREIGN, both of which are auto-mapped. One can subclass the latter table as follows : class FOREIGN(AutomapModel): __tablename__ = "ForeignTable" __endpoint__ = "android" fk_A = Column("A", Integer) fk_B = Column("B", Integer) __table_args__ = (ForeignKeyConstraint((fk_A, fk_B), ("TABLE.A","TABLE.B")), {}) # __table_args__ = (ForeignKeyConstraint((fk_A, fk_A), (TABLE.A, TABLE.B)), {}) # If TABLE is itself defined as an AutoModel subclass before this table and in the same file. I got this working after reworking the Sandman 2 code base a little, I've submitted a PR accordingly but I'm awaiting acceptance/rejection thereof. If you want to pull my variant let me know and I'll post the URL. Then one might ask if you have to map the other fields in FOREIGN and the answer is not SQLAlchemy handles this for you. If you define your own TABLE use the second variant of `__table__args__` Within my own notes I have the following links [1](https://stackoverflow.com/a/45643935) is mostly thoeretical, [6](https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017) discusses foreign keys, [2](https://stackoverflow.com/q/2292662), [3](https://stackoverflow.com/a/24060729), [4](https://stackoverflow.com/q/6651667) and [7](https://stackoverflow.com/questions/13663569/t-sql-clustered-foreign-key) are more convenient for resolving the problem and [5](https://sqlrus.com/2018/02/finding-composite-primary-key-columns/) provides a nice script for identifying compound keys.