Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sqlalchemy - Many-to-many with composite keys: No unique constraint?

In SQLAlchemy I've declared 2 classes with a composite primary key and would like a many-to-many relation between them. This:

class Topic(Base):
    __tablename__ = 'topic'
    id = Column(BigInteger, primary_key=True)
    repo = Column(String, primary_key=True)

class Label(Base):
    __tablename__ = 'label'
    repo = Column(String, primary_key=True)
    title = Column(String(25), primary_key=True)

class TopicLabel(Base):
    __tablename__ = 'topic_label'
    topic_id = Column(BigInteger, primary_key=True)
    topic_repo = Column(String, primary_key=True)
    label_repo = Column(String, primary_key=True)
    label_title = Column(String, primary_key=True)

    __table_args__ = (
        ForeignKeyConstraint(
            ['topic_id', 'topic_repo'],
            ['topic.id', 'topic.repo'],
        ),
        ForeignKeyConstraint(
            ['label_repo', 'label_title'],
            ['label.repo', 'label.title'],
        ),
    )


This results in the following error:

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

[SQL:
CREATE TABLE topic_label (
    topic_id BIGINT NOT NULL,
    topic_repo VARCHAR NOT NULL,
    label_repo VARCHAR NOT NULL,
    label_title VARCHAR NOT NULL,
    PRIMARY KEY (topic_id, topic_repo, label_repo, label_title),
    FOREIGN KEY(label_repo, label_title) REFERENCES label (repo, title),
    FOREIGN KEY(topic_id, topic_repo) REFERENCES topic (id, repo)
)

]

What am I doing wrong here?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
等待大神答复

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...