From: Bret Hughes <bhughes@elevating.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgresql sql list <pgsql-sql@postgresql.org>
Subject: Re: trigger/for key help
Date: 12 Apr 2004 02:18:35 -0500
Message-ID: <1081754316.3024.74.camel@bretsony> (raw)
In-Reply-To: <28212.1081736841@sss.pgh.pa.us>
References: <1081662204.6609.72.camel@bretsony>
<20040411080932.Q83278@megazone.bigpanda.com>
<1081711155.6399.165.camel@bretsony>
<17189.1081720813@sss.pgh.pa.us>
<1081724318.2628.20.camel@bretsony>
<28212.1081736841@sss.pgh.pa.us>
On Sun, 2004-04-11 at 21:27, Tom Lane wrote:
> Bret Hughes <bhughes@elevating.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bhughes@elevating.com, tgl@sss.pgh.pa.us
Subject: Re: trigger/for key help
In-Reply-To: <1081754316.3024.74.camel@bretsony>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox