Migration issues with SQLite

### Apache Airflow version

main (development)

### If "Other Airflow 2 version" selected, which one?

_No response_

### What happened?

Notice migrations are breaking my SQLite.

**Downgrade from 3.1 to 2.11.0**
```

2025-09-19T10:12:22.607315Z [info     ] Running downgrade eaf332f43c7c -> a3c7f2b18d4e, add last_parse_duration to dag model. [alembic.runtime.migration] loc=migration.py:622
Traceback (most recent call last):
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/python/bin/airflow", line 10, in <module>
    sys.exit(main())
  File "/opt/airflow/airflow-core/src/airflow/__main__.py", line 55, in main
    args.func(args)
  File "/opt/airflow/airflow-core/src/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/utils/cli.py", line 114, in wrapper
    return f(*args, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/utils/providers_configuration_loader.py", line 54, in wrapped_function
    return func(*args, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/cli/commands/db_command.py", line 214, in downgrade
    run_db_downgrade_command(args, db.downgrade, _REVISION_HEADS_MAP)
  File "/opt/airflow/airflow-core/src/airflow/cli/commands/db_command.py", line 189, in run_db_downgrade_command
    command(to_revision=to_revision, from_revision=from_revision, show_sql_only=args.show_sql_only)
  File "/opt/airflow/airflow-core/src/airflow/utils/session.py", line 100, in wrapper
    return func(*args, session=session, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/utils/db.py", line 1217, in downgrade
    command.downgrade(config, revision=to_revision, sql=show_sql_only)
  File "/usr/python/lib/python3.10/site-packages/alembic/command.py", line 530, in downgrade
    script.run_env()
  File "/usr/python/lib/python3.10/site-packages/alembic/script/base.py", line 549, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/python/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/python/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/opt/airflow/airflow-core/src/airflow/migrations/env.py", line 138, in <module>
    run_migrations_online()
  File "/opt/airflow/airflow-core/src/airflow/migrations/env.py", line 132, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/python/lib/python3.10/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/python/lib/python3.10/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "/opt/airflow/airflow-core/src/airflow/migrations/versions/0084_3_1_0_add_last_parse_duration_to_dag_model.py", line 49, in downgrade
    with op.batch_alter_table("dag", schema=None) as batch_op:
  File "/usr/python/lib/python3.10/contextlib.py", line 142, in __exit__
    next(self.gen)
  File "/usr/python/lib/python3.10/site-packages/alembic/operations/base.py", line 397, in batch_alter_table
    impl.flush()
  File "/usr/python/lib/python3.10/site-packages/alembic/operations/batch.py", line 163, in flush
    batch_impl._create(self.impl)
  File "/usr/python/lib/python3.10/site-packages/alembic/operations/batch.py", line 468, in _create
    op_impl.drop_table(self.table)
  File "/usr/python/lib/python3.10/site-packages/alembic/ddl/impl.py", line 446, in drop_table
    self._exec(schema.DropTable(table, **kw))
  File "/usr/python/lib/python3.10/site-packages/alembic/ddl/impl.py", line 246, in _exec
    return conn.execute(construct, params)
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 286, in execute
    return self._execute_20(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1477, in _execute_ddl
    ret = self._execute_context(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL:
DROP TABLE dag]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
root@532da64895d2:/opt/airflow#
m
```

**Upgrade from 2.11 to 3.1.0**

```

Traceback (most recent call last):
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: FOREIGN KEY constraint failed

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/python/bin/airflow", line 10, in <module>
    sys.exit(main())
  File "/opt/airflow/airflow-core/src/airflow/__main__.py", line 55, in main
    args.func(args)
  File "/opt/airflow/airflow-core/src/airflow/cli/cli_config.py", line 49, in command
    return func(*args, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/utils/cli.py", line 114, in wrapper
    return f(*args, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/utils/providers_configuration_loader.py", line 54, in wrapped_function
    return func(*args, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/cli/commands/db_command.py", line 207, in migratedb
    run_db_migrate_command(args, db.upgradedb, _REVISION_HEADS_MAP)
  File "/opt/airflow/airflow-core/src/airflow/cli/commands/db_command.py", line 135, in run_db_migrate_command
    command(
  File "/opt/airflow/airflow-core/src/airflow/utils/session.py", line 100, in wrapper
    return func(*args, session=session, **kwargs)
  File "/opt/airflow/airflow-core/src/airflow/utils/db.py", line 1136, in upgradedb
    command.upgrade(config, revision=to_revision or "heads")
  File "/usr/python/lib/python3.10/site-packages/alembic/command.py", line 483, in upgrade
    script.run_env()
  File "/usr/python/lib/python3.10/site-packages/alembic/script/base.py", line 549, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/python/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/python/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/opt/airflow/airflow-core/src/airflow/migrations/env.py", line 138, in <module>
    run_migrations_online()
  File "/opt/airflow/airflow-core/src/airflow/migrations/env.py", line 132, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/python/lib/python3.10/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/python/lib/python3.10/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "/opt/airflow/airflow-core/src/airflow/migrations/versions/0041_3_0_0_rename_dataset_as_asset.py", line 158, in upgrade
    _drop_fkey_if_exists("dagrun_dataset_event", "dagrun_dataset_event_event_id_fkey")
  File "/opt/airflow/airflow-core/src/airflow/migrations/versions/0041_3_0_0_rename_dataset_as_asset.py", line 107, in _drop_fkey_if_exists
    with op.batch_alter_table(table, schema=None) as batch_op:
  File "/usr/python/lib/python3.10/contextlib.py", line 142, in __exit__
    next(self.gen)
  File "/usr/python/lib/python3.10/site-packages/alembic/operations/base.py", line 397, in batch_alter_table
    impl.flush()
  File "/usr/python/lib/python3.10/site-packages/alembic/operations/batch.py", line 163, in flush
    batch_impl._create(self.impl)
  File "/usr/python/lib/python3.10/site-packages/alembic/operations/batch.py", line 450, in _create
    op_impl._exec(
  File "/usr/python/lib/python3.10/site-packages/alembic/ddl/impl.py", line 246, in _exec
    return conn.execute(construct, params)
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 286, in execute
    return self._execute_20(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/usr/python/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed
[SQL: INSERT INTO _alembic_tmp_dagrun_dataset_event (dag_run_id, event_id) SELECT dagrun_dataset_event.dag_run_id, dagrun_dataset_event.event_id
FROM dagrun_dataset_event]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
root@6bb0895ba3ef:/opt/airflow#
```



### What you think should happen instead?

_No response_

### How to reproduce

1. Create a instance using SQLite 
2. Try running `airflow db downgrade -n 2.11.0`
3. Noticed issues in downgrade
4. Try upgrading from 2.11.0 to 3.1.0 `airflow db migrate`

### Operating System

OS

### Versions of Apache Airflow Providers

_No response_

### Deployment

Official Apache Airflow Helm Chart

### Deployment details

_No response_

### Anything else?

_No response_

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)


Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Migration issues with SQLite #55882

Apache Airflow version

If "Other Airflow 2 version" selected, which one?

What happened?

What you think should happen instead?

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else?

Are you willing to submit PR?

Code of Conduct

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Uh oh!

Migration issues with SQLite #55882

Description

Apache Airflow version

If "Other Airflow 2 version" selected, which one?

What happened?

What you think should happen instead?

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else?

Are you willing to submit PR?

Code of Conduct

Metadata

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Issue actions