ALTER TABLE ADD FOREIGN KEY POSTGRES UPDATE

I often run the whole thing - drop, bulk work, recreat - inside a single transaction. Take advantage of the transactional DDL in PostgreSQL - as always. Obviously, you need to script both the DROP and CREATE statements before you actually drop something.Īnd the second obvious note - always run these things in a transaction. You can then just load those files as include files in psql. The add foreign key function lists all of the columns of the table and allows the user to choose one or more columns to add to the foreign key for the table. The easiest way to turn these commands into proper scripts is to just use the psql output feature: The RazorSQL alter table tool includes an Add Foreign Key option for adding foreign keys to PostgreSQL database tables. ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC Īgain, note the reversed order that is necessary so we create PRIMARY KEYs and UNIQUE constraints before we create the FOREIGN KEYs. To generate a script to reload the constraints: Note that the order is important - we must drop FOREIGN KEYs before we drop PRIMARY KEYs and UNIQUE constraints, since they depend on each other. ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace INNER JOIN pg_class ON conrelid=pg_class.oid

To generate a script to drop constraints: Adding filters for just a single table/namespace/constrainttype is trivial, but left as an exercise for the reader. The following simple queries will generate SQL scripts that drop all your constraints, and then re-create them. All of them, or maybe just the FOREIGN KEYs, depending on exactly what you are doing.

ALTER TABLE ADD FOREIGN KEY POSTGRES UPDATE

Of the kind where you load or update lots of data - which means that it will run a lot faster with the constraints in the database turned off. The idea is: you have some large operations you are doing on your database. I do this fairly often, but after talking to some other people I realized it might be a good idea to share a couple of quick SQL scripts.

Alter table add foreign key postgres