I have a PostgreSQL 9.3 database with two tables, table B with 100 million records linked via a FK constraint to another table A with around 10 million records.
Occasionally I need to delete a record in table A, and due to the FK constraint, Postgres requires that I first delete all referencing records in table B. However, since this link represents a semantic "source" relationship, my application doesn't need this strict referential integrity, so I'm trying to drop the constraint.
However, I'm finding this very difficult. When I try and run:
alter table tableB drop constraint fk_constraint_to_tableA;
it seems to time out. I let it run for an hour and it didn't complete, so I killed my psql terminal. Unfortunately, that apparently didn't stop it running on the server, which became unresponsive and wouldn't let me connect back in, forcing me to restart it.
I understand that PostgreSQL doesn't scale well, and isn't a good database for large-data applications, but is there any way to delete this constraint, or am I stuck with it forever?