Thread: adding column constraint
Can someone give an example of how to add a foreign key constraint to an existing table? (Pgsql and the man page differ and neither syntax seems to work.) I've tried: alter table mytable add constraint col foreign key references reftable with no luck. Tim -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
alter table cust add constraint fk_cust_bd_id foreign key (bd_id) references bill_dist (bd_id); At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: >Can someone give an example of how to add a foreign key constraint to an >existing table? (Pgsql and the man page differ and neither syntax seems >to work.) > >I've tried: > >alter table mytable add constraint col foreign key references reftable > >with no luck. > >Tim > >-- >Timothy H. Keitt >National Center for Ecological Analysis and Synthesis >735 State Street, Suite 300, Santa Barbara, CA 93101 >Phone: 805-892-2519, FAX: 805-892-2510 >http://www.nceas.ucsb.edu/~keitt/ >
Hmmm... I got it to work, but using a slightly different syntax. Let me see if I understand your example: ALTER TABLE cust # the table to be altered ADD CONSTRAINT fk_cust_bd_id # the column in cust to add the constraint? FOREIGN KEY (bd_id) # foreign key in bill_dist? REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? Oddly, I put the foreign table name in place of fk_cust_bd_id and the local column name where you have (bd_id). It seemed to work. Is the identifier after ADD CONSTRAINT a noop? BTW, the problem with the example in the man page is that its impossible to tell which identifiers are table names and which are column names and which column names go with which table, etc. The format above (with comments) would help a lot. (Or use identifiers like the_table_to_be_altered and so on.) Tim mikeo wrote: > > alter table cust add constraint fk_cust_bd_id foreign key (bd_id) > references bill_dist (bd_id); > > At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: > >Can someone give an example of how to add a foreign key constraint to an > >existing table? (Pgsql and the man page differ and neither syntax seems > >to work.) > > > >I've tried: > > > >alter table mytable add constraint col foreign key references reftable > > > >with no luck. > > > >Tim > > > >-- > >Timothy H. Keitt > >National Center for Ecological Analysis and Synthesis > >735 State Street, Suite 300, Santa Barbara, CA 93101 > >Phone: 805-892-2519, FAX: 805-892-2510 > >http://www.nceas.ucsb.edu/~keitt/ > > -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
almost, ALTER TABLE cust # the table to be altered yes ADD CONSTRAINT fk_cust_bd_id # name of the constraint (see tgconstrname column in pg_trigger) FOREIGN KEY (bd_id) # column in cust to be FK'd to bill_dist REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? yes mikeo At 02:26 PM 7/26/00 -0700, Timothy H. Keitt wrote: >Hmmm... I got it to work, but using a slightly different syntax. Let me >see if I understand your example: > >ALTER TABLE > cust # the table to be altered >ADD CONSTRAINT > fk_cust_bd_id # the column in cust to add the constraint? >FOREIGN KEY > (bd_id) # foreign key in bill_dist? >REFERENCES > bill_dist (bd_id); # specifies the column in bill_dist to use? > >Oddly, I put the foreign table name in place of fk_cust_bd_id and the >local column name where you have (bd_id). It seemed to work. Is the >identifier after ADD CONSTRAINT a noop? > >BTW, the problem with the example in the man page is that its impossible >to tell which identifiers are table names and which are column names and >which column names go with which table, etc. The format above (with >comments) would help a lot. (Or use identifiers like >the_table_to_be_altered and so on.) > >Tim > >mikeo wrote: >> >> alter table cust add constraint fk_cust_bd_id foreign key (bd_id) >> references bill_dist (bd_id); >> >> At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: >> >Can someone give an example of how to add a foreign key constraint to an >> >existing table? (Pgsql and the man page differ and neither syntax seems >> >to work.) >> > >> >I've tried: >> > >> >alter table mytable add constraint col foreign key references reftable >> > >> >with no luck. >> > >> >Tim >> > >> >-- >> >Timothy H. Keitt >> >National Center for Ecological Analysis and Synthesis >> >735 State Street, Suite 300, Santa Barbara, CA 93101 >> >Phone: 805-892-2519, FAX: 805-892-2510 >> >http://www.nceas.ucsb.edu/~keitt/ >> > > >-- >Timothy H. Keitt >National Center for Ecological Analysis and Synthesis >735 State Street, Suite 300, Santa Barbara, CA 93101 >Phone: 805-892-2519, FAX: 805-892-2510 >http://www.nceas.ucsb.edu/~keitt/ >