SQLAlchemy Invalid Foreign Key with PostgreSQL but not SQLite-CodePudding

I can create the following tables in an SQLite database:

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class Paragraph(db.Model):
    article_id = db.Column(db.ForeignKey('article.id'), primary_key=True)
    index = db.Column(db.Integer, primary_key=True)

class Summary(db.Model):
    article_id = db.Column(db.ForeignKey('article.id'), primary_key=True)
    paragraph_index = db.Column(db.ForeignKey('paragraph.index'), primary_key=True)
    level = db.Column(db.Integer, primary_key=True)

However, when I create the same tables in PostgreSQL, I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "paragraph"

Removing Summary.paragraph_index allows me to create the tables, so it appears that the issue lies here. However, the column does reference a primary key, which I feel should satisfy the requirement.

Adding unique=True to Paragraph.index also allows me to create the tables. I can then remove the unique constraint using pgAdmin and have everything work fine. However, this seems improper.

I have also tried:

declaring the relationship via the ORM
declaring ForeignKeyConstraint in Summary table args
declaring UniqueConstraint in Paragraph table args for the primary key columns

CodePudding user response：

In response to the comments by Adrian Klaver, of which I am grateful, I have used __table_args__ to point Summary at both halves of the composite primary key on Paragraph:

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class Paragraph(db.Model):
    article_id = db.Column(db.ForeignKey('article.id'), primary_key=True)
    index = db.Column(db.Integer, primary_key=True)

class Summary(db.Model):
    article_id = db.Column(db.ForeignKey('article.id'), primary_key=True)
    paragraph_index = db.Column(db.ForeignKey('paragraph.index'), primary_key=True)
    level = db.Column(db.Integer, primary_key=True)
    __table_args__ = (db.ForeignKeyConstraint(
        ['article_id', 'paragraph_index'],
        ['paragraph.article_id', 'paragraph.index']),)