We support "alter table add foreign key".  How about supporting 
"alter table drop foreign key"?

- he said as he went to drop a foreign key
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 11:02:00 -0500
"Dan Langille" <dan@langille.org>
7.4 - TODO : alter table drop foreign key

On Thu, 5 Dec 2002, Dan Langille wrote:

> We support "alter table add foreign key".  How about supporting
> "alter table drop foreign key"?
>
> - he said as he went to drop a foreign key

It seems to work for me on my 7.3b2 system with
alter table <table> drop constraint <constraint name>;


Thu, 5 Dec 2002 08:20:37 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 8:20, Stephan Szabo wrote:

> 
> On Thu, 5 Dec 2002, Dan Langille wrote:
> 
> > We support "alter table add foreign key".  How about supporting
> > "alter table drop foreign key"?
> >
> > - he said as he went to drop a foreign key
> 
> It seems to work for me on my 7.3b2 system with
> alter table <table> drop constraint <constraint name>;

How was that FK added?  How did you determine the constraint name?
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 11:24:36 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 8:20, Stephan Szabo wrote:

> 
> On Thu, 5 Dec 2002, Dan Langille wrote:
> 
> > We support "alter table add foreign key".  How about supporting
> > "alter table drop foreign key"?
> >
> > - he said as he went to drop a foreign key
> 
> It seems to work for me on my 7.3b2 system with
> alter table <table> drop constraint <constraint name>;

Premature send.. sorry

How was that FK added?  How did you determine the constraint name?

How would you do that if the FK was added with the following syntax?

alter table <table>
    add foreign key (<column>)
       references <othertable> (<othercolumn>) 
on update cascade on delete cascade;


-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 11:28:33 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote:

> On 5 Dec 2002 at 8:20, Stephan Szabo wrote:
>
> >
> > On Thu, 5 Dec 2002, Dan Langille wrote:
> >
> > > We support "alter table add foreign key".  How about supporting
> > > "alter table drop foreign key"?
> > >
> > > - he said as he went to drop a foreign key
> >
> > It seems to work for me on my 7.3b2 system with
> > alter table <table> drop constraint <constraint name>;
>
> Premature send.. sorry
>
> How was that FK added?  How did you determine the constraint name?

alter table <table> add constraint <name> foreign key ...

> How would you do that if the FK was added with the following syntax?
>
> alter table <table>
>     add foreign key (<column>)
>        references <othertable> (<othercolumn>)
> on update cascade on delete cascade;

IIRC, the constraint will get an automatic name of the form
$<n> in such cases.  I believe if you do a \d on the table,
it gives the name in the constraint definitions (on one of mine
i get:

Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE
CASCADE ON DELETE NO ACTION

Where $1 is the name of the constraint.



Thu, 5 Dec 2002 08:44:28 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 8:44, Stephan Szabo wrote:

> On Thu, 5 Dec 2002, Dan Langille wrote:
> 
> > On 5 Dec 2002 at 8:20, Stephan Szabo wrote:
> >
> > >
> > > On Thu, 5 Dec 2002, Dan Langille wrote:
> > >
> > > > We support "alter table add foreign key".  How about supporting
> > > > "alter table drop foreign key"?
> > > >
> > > > - he said as he went to drop a foreign key
> > >
> > > It seems to work for me on my 7.3b2 system with
> > > alter table <table> drop constraint <constraint name>;
> >
> > Premature send.. sorry
> >
> > How was that FK added?  How did you determine the constraint name?
> 
> alter table <table> add constraint <name> foreign key ...
> 
> > How would you do that if the FK was added with the following syntax?
> >
> > alter table <table>
> >     add foreign key (<column>)
> >        references <othertable> (<othercolumn>)
> > on update cascade on delete cascade;
> 
> IIRC, the constraint will get an automatic name of the form
> $<n> in such cases.  I believe if you do a \d on the table,
> it gives the name in the constraint definitions (on one of mine
> i get:
> 
> Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE
> CASCADE ON DELETE NO ACTION
> 
> Where $1 is the name of the constraint.

Thanks.  In my 7.2.3 database, the table in question has:

Primary key: watch_list_staging_pkey
Check constraints: "watch_list_stag_from_watch_list" 
((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool))
                   "watch_list_stagin_from_pkg_info" ((from_pkg_info 
= 't'::bool) OR (from_pkg_info = 'f'::bool))
Triggers: RI_ConstraintTrigger_4278482,
          RI_ConstraintTrigger_4278488

No mention of FK constraints.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 11:47:09 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 11:47, Dan Langille wrote:

> On 5 Dec 2002 at 8:44, Stephan Szabo wrote:
> 
> > On Thu, 5 Dec 2002, Dan Langille wrote:
> > 
> > > On 5 Dec 2002 at 8:20, Stephan Szabo wrote:
> > >
> > > >
> > > > On Thu, 5 Dec 2002, Dan Langille wrote:
> > > >
> > > > > We support "alter table add foreign key".  How about supporting
> > > > > "alter table drop foreign key"?
> > > > >
> > > > > - he said as he went to drop a foreign key
> > > >
> > > > It seems to work for me on my 7.3b2 system with
> > > > alter table <table> drop constraint <constraint name>;
> > >
> > > Premature send.. sorry
> > >
> > > How was that FK added?  How did you determine the constraint name?
> > 
> > alter table <table> add constraint <name> foreign key ...
> > 
> > > How would you do that if the FK was added with the following syntax?
> > >
> > > alter table <table>
> > >     add foreign key (<column>)
> > >        references <othertable> (<othercolumn>)
> > > on update cascade on delete cascade;
> > 
> > IIRC, the constraint will get an automatic name of the form
> > $<n> in such cases.  I believe if you do a \d on the table,
> > it gives the name in the constraint definitions (on one of mine
> > i get:
> > 
> > Foreign Key constraints: $1 FOREIGN KEY (a) REFERENCES qqq(a) ON UPDATE
> > CASCADE ON DELETE NO ACTION
> > 
> > Where $1 is the name of the constraint.
> 
> Thanks.  In my 7.2.3 database, the table in question has:
> 
> Primary key: watch_list_staging_pkey
> Check constraints: "watch_list_stag_from_watch_list" 
> ((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool))
>                    "watch_list_stagin_from_pkg_info" ((from_pkg_info 
> = 't'::bool) OR (from_pkg_info = 'f'::bool))
> Triggers: RI_ConstraintTrigger_4278482,
>           RI_ConstraintTrigger_4278488
> 
> No mention of FK constraints.

Found the solution:

drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;

Given that the FK in question did not have a name to start with, I 
concede that it would be difficult to code DROP FOREIGN KEY.

What about supporting ALTER TABLE <table> ADD FOREIGN KEY <keyname> 
... which at present we don't?  That would then make dropping the FK 
a simple coding issue?
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 11:51:56 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote:

> Found the solution:
>
> drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;

Actually there are three triggers for the constraint.  You may have
dangling triggers on the other table of the constraint.  It's one on the
table the constraint's defined on and two on the referenced table.

> Given that the FK in question did not have a name to start with, I
> concede that it would be difficult to code DROP FOREIGN KEY.
>
> What about supporting ALTER TABLE <table> ADD FOREIGN KEY <keyname>
> ... which at present we don't?  That would then make dropping the FK
> a simple coding issue?

ISTM, that's
 ALTER TABLE <table> ADD CONSTRAINT <name> FOREIGN KEY ...
which should be there in any 7.x.

And the drop constraint for foreign keys (and the \d display stuff) is new
in 7.3.


Thu, 5 Dec 2002 09:02:18 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 9:02, Stephan Szabo wrote:

> On Thu, 5 Dec 2002, Dan Langille wrote:
> 
> > Found the solution:
> >
> > drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;
> 
> Actually there are three triggers for the constraint.  You may have
> dangling triggers on the other table of the constraint.  It's one on the
> table the constraint's defined on and two on the referenced table.
> 
> > Given that the FK in question did not have a name to start with, I
> > concede that it would be difficult to code DROP FOREIGN KEY.
> >
> > What about supporting ALTER TABLE <table> ADD FOREIGN KEY <keyname>
> > ... which at present we don't?  That would then make dropping the FK
> > a simple coding issue?
> 
> ISTM, that's
>  ALTER TABLE <table> ADD CONSTRAINT <name> FOREIGN KEY ...
> which should be there in any 7.x.

Agreed.  But the syntax is different. If we are supporting "ALTER 
TABLE <table> ADD FOREIGN KEY " without a name, why not support it 
with a name?

> And the drop constraint for foreign keys (and the \d display stuff) is new
> in 7.3.

That's going to be much more useful.  I installed 7.3 for testing 
this morning.  Looking at it now, I no longer see a need for a DROP 
FOREIGN KEY. 

Thank you.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 12:15:53 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote:

> On 5 Dec 2002 at 9:02, Stephan Szabo wrote:
>
> > On Thu, 5 Dec 2002, Dan Langille wrote:
> >
> > > Found the solution:
> > >
> > > drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;
> >
> > Actually there are three triggers for the constraint.  You may have
> > dangling triggers on the other table of the constraint.  It's one on the
> > table the constraint's defined on and two on the referenced table.
> >
> > > Given that the FK in question did not have a name to start with, I
> > > concede that it would be difficult to code DROP FOREIGN KEY.
> > >
> > > What about supporting ALTER TABLE <table> ADD FOREIGN KEY <keyname>
> > > ... which at present we don't?  That would then make dropping the FK
> > > a simple coding issue?
> >
> > ISTM, that's
> >  ALTER TABLE <table> ADD CONSTRAINT <name> FOREIGN KEY ...
> > which should be there in any 7.x.
>
> Agreed.  But the syntax is different. If we are supporting "ALTER
> TABLE <table> ADD FOREIGN KEY " without a name, why not support it
> with a name?

When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so
I think that might be why we're talking past each other here.

Technically the syntax in question is:
 ALTER TABLE <table> ADD <table constraint definition>
where CONSTRAINT <name> is an optional leading clause in a table
constraint definition.  ADD FOREIGN KEY is a shorthand for a foreign key
constraint (technically unnamed).

Thus you can also say things like:
ALTER TABLE <table> ADD CONSTRAINT blah CHECK (foo!=0);
to make a named check constraint.


Thu, 5 Dec 2002 09:31:15 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 9:31, Stephan Szabo wrote:

> When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so
> I think that might be why we're talking past each other here.
> 
> Technically the syntax in question is:
>  ALTER TABLE <table> ADD <table constraint definition>
> where CONSTRAINT <name> is an optional leading clause in a table
> constraint definition.  ADD FOREIGN KEY is a shorthand for a foreign key
> constraint (technically unnamed).

Understood.

What about allowing a named foreign key?  I haven't checked the RFCs

> Thus you can also say things like:
> ALTER TABLE <table> ADD CONSTRAINT blah CHECK (foo!=0);
> to make a named check constraint.

Understood.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 12:41:29 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On Thu, 5 Dec 2002, Dan Langille wrote:

> On 5 Dec 2002 at 9:31, Stephan Szabo wrote:
>
> > When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so
> > I think that might be why we're talking past each other here.
> >
> > Technically the syntax in question is:
> >  ALTER TABLE <table> ADD <table constraint definition>
> > where CONSTRAINT <name> is an optional leading clause in a table
> > constraint definition.  ADD FOREIGN KEY is a shorthand for a foreign key
> > constraint (technically unnamed).
>
> Understood.
>
> What about allowing a named foreign key?  I haven't checked the RFCs

Here's a part of what SQL92 (draft) has to say about table constraint
definitions:

         <table constraint definition> ::=
              [ <constraint name definition> ]
              <table constraint> [ <constraint attributes> ]

         <table constraint> ::=
                <unique constraint definition>
              | <referential constraint definition>
              | <check constraint definition>


         <constraint name definition> ::= CONSTRAINT <constraint name>

         <referential constraint definition> ::=
              FOREIGN KEY <left paren> <referencing columns> <right paren>
                <references specification>

11.6     Syntax Rules

         2) If <constraint name definition> is not specified, then a <con-
            straint name definition> that contains an implementation-
            dependent <constraint name> is implicit. The assigned <con-
            straint name> shall obey the Syntax Rules of an explicit <con-
            straint name>.

In our case, the implementation dependent naming scheme is I believe
"$<n>" where <n> is the maximum one already there for that table +1 I
would guess.


Thu, 5 Dec 2002 09:51:26 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 9:51, Stephan Szabo wrote:

> On Thu, 5 Dec 2002, Dan Langille wrote:
> 
> > On 5 Dec 2002 at 9:31, Stephan Szabo wrote:
> >
> > > When we talk about ALTER TABLE ADD FOREIGN KEY we're being imprecise, so
> > > I think that might be why we're talking past each other here.
> > >
> > > Technically the syntax in question is:
> > >  ALTER TABLE <table> ADD <table constraint definition>
> > > where CONSTRAINT <name> is an optional leading clause in a table
> > > constraint definition.  ADD FOREIGN KEY is a shorthand for a foreign key
> > > constraint (technically unnamed).
> >
> > Understood.
> >
> > What about allowing a named foreign key?  I haven't checked the RFCs
> 
> Here's a part of what SQL92 (draft) has to say about table constraint
> definitions:
> 
>          <table constraint definition> ::=
>               [ <constraint name definition> ]
>               <table constraint> [ <constraint attributes> ]
> 
>          <table constraint> ::=
>                 <unique constraint definition>
>               | <referential constraint definition>
>               | <check constraint definition>
> 
> 
>          <constraint name definition> ::= CONSTRAINT <constraint name>
> 
>          <referential constraint definition> ::=
>               FOREIGN KEY <left paren> <referencing columns> <right paren>
>                 <references specification>
> 
> 11.6     Syntax Rules
> 
>          2) If <constraint name definition> is not specified, then a <con-
>             straint name definition> that contains an implementation-
>             dependent <constraint name> is implicit. The assigned <con-
>             straint name> shall obey the Syntax Rules of an explicit <con-
>             straint name>.
> 
> In our case, the implementation dependent naming scheme is I believe
> "$<n>" where <n> is the maximum one already there for that table +1 I
> would guess.

Thanks.  I guess I should rename my thread to 7.4 - TODO : allow 
constraint names when using the "ALTER TABLE <table> ADD FOREIGN KEY" 
syntax.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 13:45:19 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
> Thanks.  I guess I should rename my thread to 7.4 - TODO : allow 
> constraint names when using the "ALTER TABLE <table> ADD FOREIGN KEY" 
> syntax.

You can do that now.

ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
05 Dec 2002 14:04:46 -0500
Rod Taylor <rbt@rbt.ca>
Re: 7.4 - TODO : alter table drop foreign key
Dan Langille wrote:> On 5 Dec 2002 at 11:47, Dan Langille wrote:
> 
>>Primary key: watch_list_staging_pkey
>>Check constraints: "watch_list_stag_from_watch_list" 
>>((from_watch_list = 't'::bool) OR (from_watch_list = 'f'::bool))
>>                   "watch_list_stagin_from_pkg_info" ((from_pkg_info 
>>= 't'::bool) OR (from_pkg_info = 'f'::bool))
>>Triggers: RI_ConstraintTrigger_4278482,
>>          RI_ConstraintTrigger_4278488
>>
>>No mention of FK constraints.
> 
> 
> Found the solution:
> 
> drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;
> 

You should now go to the table this RI constraint was referring to and delete 
the two triggers in there as well.  They will still be checking for deletions 
and updates.  Look for something like
RI_ConstraintTrigger_4278490
RI_ConstraintTrigger_4278492
and with the associated procedure RI_FKey_noaction_del and RI_FKey_noaction_upd


BTW, the rhdb-admin program can drop the constraints for you, even the unnamed 
ones on backends 7.2 up.  You can download it from:

http://sources.redhat.com/rhdb

Of course, now that you broke the set of triggers for this FK constraint you'll 
still need to drop the other ones by hand.  But the tool at least will show you 
the column and table involved so it will be easier to identify the two you have 
to get rid of.


Regards,
Fernando




-- 
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


Thu, 05 Dec 2002 14:17:02 -0500
Fernando Nasser <fnasser@redhat.com>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 14:04, Rod Taylor wrote:

> > Thanks.  I guess I should rename my thread to 7.4 - TODO : allow 
> > constraint names when using the "ALTER TABLE <table> ADD FOREIGN KEY" 
> > syntax.
> 
> You can do that now.
> 
> ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....

That I know.  That syntax is radically different from that proposed.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 14:21:34 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 14:17, Fernando Nasser wrote:

> Dan Langille wrote:> On 5 Dec 2002 at 11:47, Dan Langille wrote:
> > 
> > drop trigger "RI_ConstraintTrigger_4278488" on watch_list_staging;
> > 
> 
> You should now go to the table this RI constraint was referring to and delete 
> the two triggers in there as well.  They will still be checking for deletions 
> and updates.  Look for something like
> RI_ConstraintTrigger_4278490
> RI_ConstraintTrigger_4278492
> and with the associated procedure RI_FKey_noaction_del and RI_FKey_noaction_upd

Oh thank you!  I didn't know about those.  FWIW, I've just documented 
this exercise at http://www.freebsddiary.org/postgresql-dropping-
constraints.php so corrections are most welcome.

> BTW, the rhdb-admin program can drop the constraints for you, even the unnamed 
> ones on backends 7.2 up.  You can download it from:
> 
> http://sources.redhat.com/rhdb

Thanks.  I hope to check that out one day.

> Of course, now that you broke the set of triggers for this FK constraint you'll 
> still need to drop the other ones by hand.  But the tool at least will show you 
> the column and table involved so it will be easier to identify the two you have 
> to get rid of.

I did the identification by hand and fixed it up that way. Hopefully 
there's nothing else in there I've done wrong.

-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 14:51:34 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
> > > Thanks.  I guess I should rename my thread to 7.4 - TODO : allow
> > > constraint names when using the "ALTER TABLE <table> ADD FOREIGN KEY"
> > > syntax.
> >
> > You can do that now.
> >
> > ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....
>
> That I know.  That syntax is radically different from that proposed.

Isn't it identical?  The CONSTRAINT <const> is SQL standard optional clause
for all commands that add constraints.

Chris


Thu, 5 Dec 2002 11:52:08 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 11:52, Christopher Kings-Lynne wrote:

> > > > Thanks.  I guess I should rename my thread to 7.4 - TODO : allow
> > > > constraint names when using the "ALTER TABLE <table> ADD FOREIGN KEY"
> > > > syntax.
> > >
> > > You can do that now.
> > >
> > > ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....
> >
> > That I know.  That syntax is radically different from that proposed.

I take back the adjective "radical" 

> Isn't it identical?  The CONSTRAINT <const> is SQL standard optional clause
> for all commands that add constraints.

Except that one is ADD CONSTRAINT, the other is an ADD FOREIGN KEY. 
They are similar in nature but different overall.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 14:58:38 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
> > Isn't it identical?  The CONSTRAINT <const> is SQL standard optional
clause
> > for all commands that add constraints.
>
> Except that one is ADD CONSTRAINT, the other is an ADD FOREIGN KEY.
> They are similar in nature but different overall.

I think you're getting a little confused here, Dan.

http://www3.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-altertable.
html

There is only one command for adding constraints to a table.  It has this
syntax:

ALTER TABLE [ ONLY ] table [ * ]
    ADD table_constraint

The table_constraint clause is defined like this (basically):

[CONSTRAINT blah] (PRIMARY KEY or UNIQUE or  FOREIGN KEY) ...

So, the CONSTRAINT blah clause allows you to specify a name for any of the 3
types of constraint: primary key, unique or foreign key.  There's nothing
special about foreign keys in this case.

If you don't put in the CONSTRAINT blah clause, you get an automatically
assigned constraint name.

Chris




Thu, 5 Dec 2002 12:09:06 -0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: 7.4 - TODO : alter table drop foreign key
On Thu, 2002-12-05 at 14:52, Christopher Kings-Lynne wrote:
> > > > Thanks.  I guess I should rename my thread to 7.4 - TODO : allow
> > > > constraint names when using the "ALTER TABLE <table> ADD FOREIGN KEY"
> > > > syntax.
> > >
> > > You can do that now.
> > >
> > > ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....
> >
> > That I know.  That syntax is radically different from that proposed.
> 
> Isn't it identical?  The CONSTRAINT <const> is SQL standard optional clause
> for all commands that add constraints.

Not to mention the same as the CREATE TABLE syntax for constraints that
we already have.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
05 Dec 2002 15:10:22 -0500
Rod Taylor <rbt@rbt.ca>
Re: 7.4 - TODO : alter table drop foreign key
On 5 Dec 2002 at 12:09, Christopher Kings-Lynne wrote:

> > > Isn't it identical?  The CONSTRAINT <const> is SQL standard optional
> clause
> > > for all commands that add constraints.
> >
> > Except that one is ADD CONSTRAINT, the other is an ADD FOREIGN KEY.
> > They are similar in nature but different overall.
> 
> I think you're getting a little confused here, Dan.
> 
> http://www3.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-altertable.
> html
> 
> There is only one command for adding constraints to a table.  It has this
> syntax:
> 
> ALTER TABLE [ ONLY ] table [ * ]
>     ADD table_constraint
> 
> The table_constraint clause is defined like this (basically):
> 
> [CONSTRAINT blah] (PRIMARY KEY or UNIQUE or  FOREIGN KEY) ...
> 
> So, the CONSTRAINT blah clause allows you to specify a name for any of the 3
> types of constraint: primary key, unique or foreign key.  There's nothing
> special about foreign keys in this case.
> 
> If you don't put in the CONSTRAINT blah clause, you get an automatically
> assigned constraint name.

Regardless of what is documented, the following is valid and works:

ALTER TABLE slave
   ADD FOREIGN KEY (master_id)
   REFERENCES master (id) ON DELETE CASCADE;
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 15:24:59 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key
"Dan Langille" <dan@langille.org> writes:
>> You can do that now.
>> ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....

> That I know.  That syntax is radically different from that proposed.

So you're proposing we replace a SQL-spec-compliant syntax with one
that is not?  Why?

			regards, tom lane

Thu, 05 Dec 2002 15:36:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.4 - TODO : alter table drop foreign key 
On 5 Dec 2002 at 15:36, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> >> You can do that now.
> >> ALTER TABLE <table> ADD CONSTRAINT <const> FOREIGN KEY ....
> 
> > That I know.  That syntax is radically different from that proposed.
> 
> So you're proposing we replace a SQL-spec-compliant syntax with one
> that is not?  Why?

If it's not compliant, I withdraw.
-- 
Dan Langille : http://www.langille.org/


Thu, 05 Dec 2002 15:40:15 -0500
"Dan Langille" <dan@langille.org>
Re: 7.4 - TODO : alter table drop foreign key 