From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Matthew Kennedy <mkennedy(at)hssinc(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, bmccoy(at)chapelperilous(dot)net |
Subject: | Re: (redefinition) alter table, add foreign key constraint |
Date: | 2000-10-17 16:05:10 |
Message-ID: | Pine.BSF.4.10.10010170859470.76325-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 17 Oct 2000, Matthew Kennedy wrote:
> bmccoy(at)chapelperilous(dot)net wrote:
> > On Tue, 17 Oct 2000, Matthew Kennedy wrote:
> > > Is it possible to perform an alter table with add foreign key constraint
> > > in PostgreSQL? We are trying to support multiple DBMS in our project by
> > > keeping to a common, compatible denominator. Oracle, supports such a
> > > construct. Does (or might) PostgreSQL support this feature?
> > Yes...
> > ALTER TABLE <table> ADD <constraint definition>
> > Brett W. McCoy
>
> Firstly...
>
> A little redefinition of my question is in order. If I try doing this:
>
> create table things (name varchar(20), state(2));
> create table states (abbreviation varchar(2));
> alter table things
> add foreign key (state)
> references states (abbreviation);
>
> Postgres responds with:
>
> NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
> for
> FOREIGN KEY check(s)
> CREATE
>
> I am wondering what the implications of postgres using implicit triggers
> for foreign key constraints are? I guess if there were no implications
> (ie. implicit triggers were a perfect implimentation of foreign keyt
> constraints), then there would be no warning message?
Actually, it's just to let you know. Similar to the creating unique index
messages that are(were) in for unique/primary key.
> Secondly...
>
> Postgres doesn't seem to take the name of a foreign key (it's a parse
> error to include it). This is slightly different to what SQL92 and
> Oracle allow. I beleive I should have been able to write:
>
> alter table things
> add foreign key things2states (state)
> references states (abbreviation);
I'm pretty sure the correct SQL92 syntax is:
alter table things
add constraint things2states foreign key (state)
references states (abbreviation);
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-10-17 16:49:47 | Re: OpenBSD startup - logfile? |
Previous Message | Frank Bax | 2000-10-17 16:04:56 | Re: OpenBSD startup - logfile? |