When using
symfony propel-build-sql
to build the DDL SQL for a PostgreSQL database having foreign keys defined, the resulting lib.model.schema.sql file will be technically valid but broken in terms of what the content represents (and what tables/foreign keys will be build therefrom).
This is mainly because DROP .. CASCADE statements are spread all over the file. So creating a table with some foreign keys to another table initially works. Then this other table get dropped and recreated. But the mutually dropped foreign keys from the first table are missing in the end.
Also the ALTER TABLE statements to create the foreign keys are as well spread all over the file. But trying to setup a foreign key relation to a table not yet existing will not work either.
The solution will be:
- all the DROP statements at first
- then all CREATE TABLE statements
- then all ALTER TABLE statements to setup foreign keys
