Dropping constraint cascade

[Real-Gecko]

I'm working on legacy database with poor structure and want to introduce some quality of life improvements like replacing UNIQUE constraints with PRIMARY KEYs. However pgschema fails to achieve this with an error

Error: failed to execute concatenated statements in group 1: ERROR: cannot drop constraint cases_id_key on table cases because other objects depend on it (SQLSTATE 2BP01)

as it does not drop constraint in CASCADE mode. I've searched docs and code but did not find any clue if I can achieve this with pgschema. So, is there a way to drop constraint in cascade mode without executing extra sql scripts?

[tianzhou]

Thanks for the report — this is a real limitation, but a CASCADE flag isn't the right fix for it.

What's happening: you're replacing the UNIQUE constraint cases_id_key with a PRIMARY KEY. The error means another object depends on that unique constraint — almost certainly a foreign key in another table that references cases(id). In Postgres an FK binds to one specific unique/PK constraint, so dropping cases_id_key requires removing the dependent FK as well.

pgschema currently emits a bare ALTER TABLE ... DROP CONSTRAINT with no CASCADE and no reordering. It already does dependency-aware handling for dropped tables (#382) and dropped columns (#384), but not for an FK that depends on a unique/PK constraint being replaced — so the drop fails.

Why we won't just add a CASCADE mode: DROP CONSTRAINT ... CASCADE would silently drop the dependent FK. Since that FK is unchanged in your desired schema, the plan would never recreate it — you'd end up with a schema that no longer matches your files and a silently lost referential-integrity constraint. That breaks the declarative/state-based contract pgschema is built on.

The right fix is for pgschema to detect the dependency and reorder into a self-contained sequence:

1. drop the dependent FK(s)
2. drop the unique constraint
3. add the primary key
4. re-add the FK(s)

This keeps the final state equal to your schema files. I'm treating this as the actionable bug here (recreate FK dependents when a referenced unique/PK constraint is replaced).

For now, the workaround is a manual SQL step outside pgschema (drop FK → swap constraint → re-add FK). There's no CASCADE flag or directive today, and we don't plan to add one.

[Real-Gecko]

Well, manual SQL is the a BIG problem in my case, because of existing "migration system™" left by previous developers, relying on it's script running in right order is usually a suicide, DB structure in production can easily go out of state and fixing it manually is nearly impossible and always leads to a lot of headaches. That's why I brought pgschema to the project. It helped a lot ensuring database structure is consistent between releases/environments, the only problem is this constraint, that cannot be dropped. Maybe there's another way around? Like some kind of pre-apply step that can be executed by pgschema automatically and only once?

1. detect if constraint exists
2. drop it cascade
3. run apply, restoring desired state with new PK and all of it's dependencies
   The key here is automation, no extra scripts, no manual intervention.

[Real-Gecko]

Great! Thanks for your effort, much appreciated.