CODEWITHSUNDEEP
mysqlpostgresqlsqlalchemyalembic
Gill Bates
Gill Bates

Reputation: 15217

How to drop constraint defined without name?

I have Alembic migration that declares Foreign Key constraint like that:

op.create_table(
    'that',
    ...
    Column('this_id', String),
    ForeignKeyConstraint(['this_id'], ['this.id'])
    ...
)

I have a requirement in my project to support two databases - PostgreSQL and MySQL. And since name of the constraint is not defined, each of the database generates it automatically. In MySQL it looks like this_ibfk_1 and in Postgres like that_this_id_key.

Now I need to write a migration that will drop the constraint. But how can I reference it considering that I don't know its name?

Upvotes: 9

Views: 10343

Answers (4)

opensourceelephantwhale
opensourceelephantwhale

Reputation: 1

As said in snakecharmerb's answer, define naming convention in the metadata to avoid this problem entirely: https://alembic.sqlalchemy.org/en/latest/naming.html

Upvotes: 0

Kant Chen
Kant Chen

Reputation: 51

To get the foreign key's name in postgres, you can use psql

psql -U <username> -h <DB hostname> <database name>

After login into the SQL prompt, use \d to inspect a certain table:

\d <table name>

And you will see the foreign key constraints.

When alembic generates migration files, the foreign key's name defaults to None

op.create_foreign_key(None, 'table_name', 'other_table', ['other_table_id'], ['id'])
op.drop_constraint(None, 'table_name', type_='foreignkey')

Simply replace None with the foreign key's name you looked up from psql.

Best practice of ForeignKey creation with alembic

When you use alembic to automatically generate a revision file, it's better to assign a foreign key's name to the upgrade() and downgrade() function before applying the revision. Whatever the name you want can be used.

It makes sure that it always use the specified name to create foreign key regardless of database types/ versions. So your migrations will never fail

Upvotes: 5

deqyra
deqyra

Reputation: 764

This answer may be about 4 years late, but I just had that problem myself: Alembic would throw upon dropping a constraint whose name is not known.
Here follows a bunch of solutions to find it:

  • get an SQLAlchemy Table object to manipulate:
from alembic import op
from sqlalchemy import Table, MetaData

meta = MetaData(bind=op.get_bind())
my_table = Table('my_table_name', meta)

my_table_constraints = list(my_table.constraints)

All of the constraints on your table are now listed in my_table_constraints. To get their names: 

my_constraint_names = list(map(lambda x: x.name, my_table_constraints))

  • "guess" the name of your constraint. For a Postgres database it will most likely be something along the lines of '<table>_<column>_fkey, or 'fk_<table>_<column>'.

  • For a Postgres database, check the contents of catalog tables. The table you're interested in is pg_constraints. If you use pgAdmin, this table is located under Servers > [Your server] > Databases > [Your db] > Catalogs > PostgreSQL Catalog (pg_catalog) > Tables > pg_constraints.
    If you cannot use a GUI tool or want to query it more precisely, this answer explains how to do it.

Upvotes: 6

JRajan
JRajan

Reputation: 702

You can use the inspect method in sqlalchemy for getting the table details, more details in here

In the inspect method, you can get existing foreign keys of specified table name using get_foreign_keys method. From that list, you can find the name of your foreign key by checking the value of referred_table.

Hope this helps.

Upvotes: 4

Related Questions