Re: trigger/for key help

On Sun, 2004-04-11 at 21:27, Tom Lane wrote:
> Bret Hughes <bhughes(at)elevating(dot)com> writes:
> > FWIW I tried to use alter table but could never get the parser to accept
> > $1 as a constraint name. I used single and double quotes as well as a
> > lame attempt \$1.
>
> Hm, "$1" works for me ...
>
> regression=# create table foo (f1 int primary key);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> CREATE TABLE
> regression=# create table bar (f1 int references foo);
> CREATE TABLE
> regression=# \d bar
> Table "public.bar"
> Column | Type | Modifiers
> --------+---------+-----------
> f1 | integer |
> Foreign-key constraints:
> "$1" FOREIGN KEY (f1) REFERENCES foo(f1)
>
> regression=# alter table bar drop constraint "$1";
> ALTER TABLE
> regression=#
>
> regards, tom lane

Here is a recreation of what I did:

elevating=# create table foo (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
elevating=# create table bar (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
elevating=# ALTER TABLE ONLY foo ADD CONSTRAINT "$1" FOREIGN KEY (f1)
REFERENCES bar;
ALTER TABLE

elevating=# \d foo
Table "foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
Primary key: foo_pkey
Triggers: RI_ConstraintTrigger_2042118

elevating=# \d bar
Table "bar"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
Primary key: bar_pkey
Triggers: RI_ConstraintTrelevating=# create table foo (f1 int primary
key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
elevating=# create table bar (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"bar_pkey" for table "bar"
CREATE TABLE
elevating=# ALTER TABLE ONLY foo ADD CONSTRAINT "$1" FOREIGN KEY (f1)
REFERENCES bar;
ALTER TABLE
igger_2042119,
RI_ConstraintTrigger_2042120

elevating=#

Not much help to me. It was at this point I started reading old emails
and trying to get rid of the foreign key that I guessed was buried in
the trigger names somewhere.

As you say, I must not have had the syntax right since:

elevating=# alter table foo drop constraint "$1";
ALTER TABLE
elevating=# drop table foo;
DROP TABLE
elevating=# drop table bar;
DROP TABLE

Well I guess I am brain dead. this is from the psql history:
alter table only diag_logs drop contraint "$2";
alter table only diag_logs drop contraint '$2';
alter table only diag_logs drop contraint ;
alter table only diag_logs drop contraint '\$2';
alter table only diag_logs drop contraint $2;
alter table only diag_logs drop contraint $2;
alter table only diag_logs drop contraint \$2;
alter table only diag_logs drop contraint "\$2";
alter table only diag_logs drop contraint '*';
alter table only diag_logs drop contraint "$2";
alter table diag_logs drop contraint "$2";

I told you I tried all combos I could think of. All, that is except for
spelling constraint correctly :(

Anyway, I appreciate the help.

Bret