Thread: Adding Foreign Key Constraint To Existing Table
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. Pointer please. Rich
On Friday, July 01, 2011 2:00:38 pm Rich Shepard 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.
>
> Pointer please.
http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html
Examples at bottom of page:
"To add a foreign key constraint to a table:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL; ">
> Rich
--
Adrian Klaver
adrian.klaver@gmail.com
On 07/01/2011 02:00 PM, Rich Shepard 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. > > Pointer please. alter table bar add foreign key (id) references foo(id); JD > > Rich > > -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579
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
On Fri, 1 Jul 2011, Joshua D. Drake wrote: > alter table bar add foreign key (id) references foo(id); Thanks, Josh. I was close, but not exact. Rich
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. By the way, rather than dropping the foreign key then recreating it, you could always do this: ALTER TABLE tablename DISABLE TRIGGER ALL; Then it would ignore the foreign key trigger and you could put in mischievous values... but remember to enable it again (replace DISABLE with ENABLE). You'll have to be a superuser to do it though. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, 1 Jul 2011, Thom Brown wrote: > By the way, rather than dropping the foreign key then recreating it, you > could always do this: > > ALTER TABLE tablename DISABLE TRIGGER ALL; > > Then it would ignore the foreign key trigger and you could put in > mischievous values... but remember to enable it again (replace DISABLE > with ENABLE). You'll have to be a superuser to do it though. Thom, Valuable information, thanks. I try to get the table structure correct before loading it into a database. In this case I was copying structure (with minor mods) from an existing environmental database and it took me a while to notice the original authors used synthetic keys when they are not needed. So, I got rid of those keys. Rich
A foreign key is a kind of constraint. Section 5.5.3 has example syntax to add constraints, including a foreign key constraint. In alter table the part "ADD table_constraint" is what you want to follow (it leads you to the create table page for theformal syntax to use). David J. On Jul 1, 2011, at 17: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. > > Pointer please. > > Rich > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 1 Jul 2011, at 23:10, Adrian Klaver wrote: > http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html > Examples at bottom of page: > "To add a foreign key constraint to a table: > ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL; > " Ah, so that's where my habit to add MATCH FULL to every FK constraint originated from! I'm pretty sure it doesn't serve anypurpose in the above single-column constraint, or does it? The docs say about MATCH: "There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will notallow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allowssome foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented." Perhaps this warrants a documentation patch? It goes at least as far back as 8.2, the oldest online documentation on thesite, maybe even as far back as 7.4 (the first version I used) or earlier. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4e0ef3b012091559666998!