CODEWITHSUNDEEP
pythonpython-3.xpostgresqlsqlalchemy
Clayton J Roberts
Clayton J Roberts

Reputation: 366

Getting sqlalchemy.exc.ProgrammingError when trying to reference composite foreign key

I have two classes:

"Test1":

class Test1(Base):
    data = [
        [1, 2],
        [3, 4],
    ]

    a = Column(Integer, primary_key=True)
    b = Column(Integer, primary_key=True)

    def __init__(self, a, b):
        self.a = a
        self.b = b

that will be referenced by "Test2":

class Test2(Base):
    data = [
        [1, 1, 2],
        [2, 3, 4],
    ]

    id = Column(Integer, primary_key=True)
    c = Column(ForeignKey(Test1.a))
    d = Column(ForeignKey(Test1.b))

    __table_args__ = (ForeignKeyConstraint([c, d], [Test1.a, Test1.b]), {})

    def __init__(self, id, c, d):
        self.id = id
        self.c = c
        self.d = d

When trying to make these in SQLAlchemy (Python 3) using a local PostgreSQL database, I get this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is 
no unique constraint matching given keys for referenced table "Test1"
[SQL: '\nCREATE TABLE "Test2" (\n\tid SERIAL NOT NULL, \n\ta1 INTEGER, 
\n\tb1 INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(a1, b1) 
REFERENCES "Test1" (a, b), \n\tFOREIGN KEY(a1) REFERENCES "Test1" (a), 
\n\tFOREIGN KEY(b1) REFERENCES "Test1" (b)\n)\n\n'] (Background on this 
error at: http://sqlalche.me/e/f405)

Just to summarize, I am trying to make a table that references another table via the referenced table's composite foreign key, but with about 10 attempts for a workaround, I am unable to resolve this. Golden cookie to whomever does.

Upvotes: 0

Views: 269

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Neither Test1.a nor Test1.b is a (unique) key on its own, though you try to reference them as such with ForeignKey(Test1.a) and ForeignKey(Test1.b). Just remove those. The composite foreign key you've defined as well is correct.

Upvotes: 1

Related Questions