How to declare NOT VALID PostgreSQL constraints with SQLAlchemy and alembic?

Question

I'd like to create a PostgreSQL CHECK constraint that is marked as NOT VALID, yet I don't see a way to create such constraint in alembic or declare it using SQLAlchemy.

It looks like SQLAlchemy added support for introspection of NOT VALID constraints, but not the other way around: https://github.com/sqlalchemy/sqlalchemy/commit/3980a9a455d08c5073cabc3c2b77de46fa36d7b4 .

Is there a way to do that? Or is the feature just missing and I need to manually write the SQL statements with alembic?

At present in Alembic "Some free-standing constraint additions and removals may not be supported, including PRIMARY KEY, EXCLUDE, CHECK", so I think you need to write the Alembic script yourself. — snakecharmerb, Jan 22 '22 at 18:34
I was able to get SQLAlchemy to emit the DDL at table creation time, but even then Alembic does not include the 'NOT VALID' option (as does Postgres, in a `CREATE TABLE`). When I added the constraint code after the table had been created Alembic did not create a migration for it. — snakecharmerb, Jan 22 '22 at 18:38
@snakecharmerb Thanks, I was under the same impression. I opened a PR to have `NOT VALID` natively supported in SQLAlchemy: https://github.com/sqlalchemy/sqlalchemy/pull/7601 . This should also work seamlessly with alembic. TBH, I'm very surprised nobody did that before: it's a quite old and useful feature of postgres, so I really thought I was missing something. I'm even more surprised nobody raised this use case in public before. — user8808265, Jan 23 '22 at 17:12

score 1 · Accepted Answer · answered Mar 11 '22 at 13:46

Support for declaring NOT VALID constraints in PostgreSQL dialect was added in SQLAlchemy 1.4.32. Such constraint can be declared by setting postgresql_not_valid dialect option to True:

import sqlalchemy as sa


sa.CheckConstraint(
    "lastname IS NOT NULL",
    postgresql_not_valid=True,  # ⬅
)
sa.ForeignKeyConstraint(
    ["head_teacher_id"],
    ["teachers.id"],
    postgresql_not_valid=True,  # ⬅
)

Alembic's create_check_constraint and create_foreign_key functions will forward any dialect option to SQLAlchemy, therefore creating a migration with such constraint is pretty straightforward:

from alembic import op


def upgrade():
    op.create_check_constraint(
        "ck_lastname_not_null",
        "students",
        "lastname IS NOT NULL",
        postgresql_not_valid=True,  # ⬅
    )
    op.create_foreign_key(
        "fk_head_teacher",
        "students",
        "teachers",
        ["head_teacher_id"],
        ["id"],
        postgresql_not_valid=True,  # ⬅
    )


def downgrade():
    op.drop_constraint("ck_lastname_not_null", "students")
    op.drop_constraint("fk_head_teacher", "students")

See also PostgreSQL constraint options documentation.

How to declare NOT VALID PostgreSQL constraints with SQLAlchemy and alembic?

1 Answers1