Initialize revision for tables with foreign keys

[DGDarkKing]

Describe the bug
When tables with foreign keys are defined and the first command revision --autogenerate is executed, the generate script includes only create_table() methods with ForeignKey arguments passed within them.

Expected behavior
Generated script should includes create_table() methods at the beginning and create_foreign_key() at the end to ensure proper separation of table creation and foreign key constraints

Versions.

• OS: Windwos 10
• Python: 3.10
• Alembic: 1.14.0
• SQLAlchemy: 2.0.36
• Database: PostgreSQL
• DBAPI: psycopg2

Have a nice day!

[CaselIT]

Hi,

This is at most a matter of preference.
If I'm not mistaken setting a name to the foreign key may make alembic use two create statements

[DGDarkKing]

Maybe this is matter of preference, but if alembic upgrade is run using the first generated migration script, it executes the following SQL queries

CREATE TABLE first_table (
id integer PRIMARY KEY,
second_table_id integer NOT NULL,
... ,
FOREIGN KEY (second_table_id) REFERENCES second_table(id),
....
);

CREATE TABLE second_table(
id integer PRIMARY KEY,
.... ,
);

As a result, PGSQL ERROR is raised, which looks something like this:

TABLE second_table does not exist - can not create relationship for first_table.second_table_id and second_table.id

thanks for the hint! I will try setting a name for foreign keys. However, it seem to me that this is a correct rule if first migration already exists, but not always applicable. Therefore, I think to avoid this problem, migration scripts should always generate separate create table and create foreign key statements.

[CaselIT]

strange that the statement are not ordered.
@zzzeek should they be or is this a missing feature / capability of autogenerate?

[zzzeek]

this is a known issue see #326