Set two columns as foreign keys references two primary keys in SQLAlchemy

Question

I am using SQLALchemy in flask. And I am confused about how to set two columns as foreign keys reference two primary keys.

class Feature(db.Model):
    __tablename__ = 'feature'
    id = db.Column(Integer, primary_key=True)
    sample_id = db.Column(Integer, ForeignKey('sample.id'), primary_key=True)


class Move(db.Model):
    __tablename__ = 'move'
    id = db.Column(Integer, primary_key=True, autoincrement=True)
    feature_id = db.Column(Integer, ForeignKey(Feature.id), nullable=False)
    sample_id = db.Column(Integer, ForeignKey(Feature.sample_id), nullable=False)
    __table_args__ = (
        db.UniqueConstraint('feature_id', 'sample_id'),
    )

I've tried several configurations but when I try to insert something I get:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) foreign key mismatch - "move" referencing "feature"

In fact the create statement is wrong:

CREATE TABLE move (
    id INTEGER NOT NULL, 
    feature_id INTEGER NOT NULL, 
    sample_id INTEGER NOT NULL,
    PRIMARY KEY (id), 
    UNIQUE (feature_id, sample_id), 
    FOREIGN KEY(feature_id) REFERENCES feature (id), 
    FOREIGN KEY(sample_id) REFERENCES feature (sample_id)
)

The last line should be:

FOREIGN KEY(feature_id, sample_id) REFERENCES feature (id, sample_id)

score 0 · Answer 1 · answered Feb 19 '20 at 23:43

Finally, I found a solution here

The working code is:

class Move(db.Model):
    __tablename__ = 'move'
    id = db.Column(Integer, primary_key=True, autoincrement=True)
    feature_id = db.Column(Integer)
    sample_id = db.Column(Integer)
    __table_args__ = (ForeignKeyConstraint([feature_id, sample_id],
                                           [Feature.id, Feature.sample_id]), {})

Set two columns as foreign keys references two primary keys in SQLAlchemy

1 Answers1