Re: Adding Foreign Key Constraint To Existing Table

On 1 July 2011 22:00, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>  I've examined the 9.0 manual page on alter table without seeing how to add
> a foreign key constraint to a column.
>
>  I needed to make changes on a couple of existing tables which could be
> accomplished only by dropping the foreign key constraint. That, and changing
> the table structure, column names, and column types were successful. But, I
> am not seeing the proper syntax to add a foreign key constraint back to the
> two affected tables.

ALTER TABLE table_a ADD CONSTRAINT fk_name
    FOREIGN KEY (column_of_table_a) REFERENCES table_b (column_of_table_b);

If in future you want foreign key checks to be deferred until the
transaction ends, you can add the DEFERRED keyword to the end.  This
will allow you to violate the foreign key temporarily, as long as you
resolve it before the end of the transaction.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company