Horton

Thread

  1. adding column constraint

    Timothy H. Keitt <keitt@nceas.ucsb.edu> — 2000-07-26T19:57:33Z

    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/

  2. Re: adding column constraint

    mikeo <mikeo@spectrumtelecorp.com> — 2000-07-26T21:05:34Z

    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/
>

  3. Re: adding column constraint

    Timothy H. Keitt <keitt@nceas.ucsb.edu> — 2000-07-26T21:26:06Z

    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/

  4. Re: adding column constraint

    mikeo <mikeo@spectrumtelecorp.com> — 2000-07-26T21:46:52Z

    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/
>