Re: Adding Foreign Key Constraint To Existing Table

From: Thom Brown <thom(at)linux(dot)com>
To: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Adding Foreign Key Constraint To Existing Table
Date: 2011-07-01 21:11:13
Message-ID: BANLkTi=f64Y314Tntn3sA86JLR15rPJ2sA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 1 July 2011 22:00, Rich Shepard <rshepard(at)appl-ecosys(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Rich Shepard 2011-07-01 21:39:23 Re: Adding Foreign Key Constraint To Existing Table
Previous Message Joshua D. Drake 2011-07-01 21:10:57 Re: Adding Foreign Key Constraint To Existing Table