SQLAlchemy There are no primary or candidate keys that match the referencing column list in the foreign key

Question

I have a model that was updated

Before Users

class Users(db.Model):
    username = db.Column(db.String(64), index=True, unique=True)
    user_created_timestamp = db.Column(db.DateTime)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    user_uuid = db.Column(UNIQUEIDENTIFIER, primary_key=True)
    scores = db.relationship("Scores", backref="owner", lazy="dynamic")

After Users

class Users(db.Model):
    uuid = db.Column(UNIQUEIDENTIFIER, primary_key=True)
    email = db.Column(db.String(120), index=True, unique=True)
    user_created_timestamp = db.Column(db.DateTime)
    password_hash = db.Column(db.String(128))
    scores = db.relationship("Scores", backref="owner", lazy="dynamic")

So I wanted to remove username and rename user_uuid to uuid.

I generated a migration file and fixed a couple of errors for dropping the username, but I cannot get the user_uuid to rename to uuid.

Here is the migration script

    op.add_column('users', sa.Column('uuid', mssql.UNIQUEIDENTIFIER(), nullable=False))
    sa.PrimaryKeyConstraint("uuid")
    op.create_foreign_key(None, 'scores', 'users', ['user_uuid'], ['uuid'])
    op.drop_constraint('FK__scores__user_uui__17F790F9', 'scores', type_='foreignkey')
    op.drop_index(op.f("ix_users_username"), table_name="users")
    op.drop_column('users', 'username')
    op.drop_constraint('pk_users', 'users', type_='primary')
    op.drop_column('users', 'user_uuid')

And here is the error

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There are no primary or candidate keys in the referenced table 'users' that match the referencing column list in the foreign key 'FK__scores__user_uui__2BFE89A6'. (1776) (SQLExecDirectW)

I understand what the error means, but for the life of me, I can't get this to update even after making a number of changes to the original migration. Is there something obvious I'm doing wrong?

I have at least identified it is the create_foreign_key line that is the issue. — Sean Payne, May 03 '21 at 10:39
If this is in fact a question about Alembic then it should be tagged as such. — Gord Thompson, May 03 '21 at 11:10
@GordThompson Okay. I tagged it. Good to know for the future. — Sean Payne, May 03 '21 at 11:16

score 2 · Answer 1 · answered May 03 '21 at 10:50

Okay, I figured it out. The line

sa.PrimaryKeyConstraint("uuid")

Creates a constraint, but it does not actually create a primary key. I also needed to add

op.create_primary_key("pk_users", "users", ["uuid"])

And the final order of commands looks like

    op.add_column('users', sa.Column('uuid', mssql.UNIQUEIDENTIFIER(), nullable=False))
    op.drop_constraint('FK__scores__user_uui__17F790F9', 'scores', type_='foreignkey')
    op.drop_index(op.f("ix_users_username"), table_name="users")
    op.drop_column('users', 'username')
    op.drop_constraint('pk_users', 'users', type_='primary')
    op.drop_column('users', 'user_uuid')
    sa.PrimaryKeyConstraint("uuid")
    op.create_primary_key("pk_users", "users", ["uuid"])
    op.create_foreign_key(None, 'scores', 'users', ['user_uuid'], ['uuid'])

SQLAlchemy There are no primary or candidate keys that match the referencing column list in the foreign key

1 Answers1