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

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'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.

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> [ <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.

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/