Why am I getting a 'not unique' error when referencing a primary key?

Question

I have two models -

Station:

class Station(db.Model):
    """Measurement station"""
    __tablename__ = "station"

    reference = db.Column(db.Text, nullable=False, primary_key=True)
    name = db.Column(db.Text, nullable=False, primary_key=True)
    status = db.Column(db.Text)


    river_analyses = db.relationship("RiverAnalysis")

and

RiverAnalysis:

class RiverAnalysis(db.Model):
    __tablename__ = "river_analysis"
    id = db.Column(db.Integer, nullable=False, primary_key=True)
    reference = db.Column(db.Text, db.ForeignKey("station.reference"), nullable=False)

    station_ref = db.relationship("Station", foreign_keys=reference)

When I try to run flask db upgrade using this migration code

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('river_analysis', sa.Column('reference', sa.Text(), nullable=False))
    op.create_foreign_key(None, 'river_analysis', 'station', ['reference'], ['reference'])

I get this error: (psycopg2.ProgrammingError) there is no unique constraint matching given keys for referenced table "station".

Why am I getting this even though station.reference is a primary key of Station?

Thanks for any help you can provide.

Do you have a "composite" primary key consisting of `reference, name` by design, or did you mean to make `name` unique on its own? — Ilja Everilä, Apr 07 '19 at 18:37

score 1 · Answer 1 · answered Apr 07 '19 at 18:20

reference alone is not your primary key, but the pair (reference, name). In order to reference that key you need a composite foreign key constraint:

class RiverAnalysis(db.Model):
    __tablename__ = "river_analysis"
    id = db.Column(db.Integer, nullable=False, primary_key=True)
    reference = db.Column(db.Text, nullable=False)
    name = db.Column(db.Text, nullable=False)

    __table_args__ = (
        db.ForeignKeyConstraint(
            [reference, name],
            ["station.reference", "station.name"]),
    )

Why am I getting a 'not unique' error when referencing a primary key?

1 Answers1