Re: [GENERAL] adding column constraint
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/
Re: [GENERAL] adding column constraint
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/
Re: [GENERAL] adding column constraint
almost, ALTER TABLE cust# the table to be alteredyes 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/
