Thread: 7.4 - TODO : alter table drop foreign key
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/
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>;
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/
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/
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.
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/
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/
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'sALTER 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.
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/
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.
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/
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> [
<constraintattributes> ]
<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
containsan 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.
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/
> 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
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
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/
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/
> > > 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
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/
> > 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
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
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/
"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
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/