Thread: ALTER TABLE -- how to add ON DELETE CASCADE?

ALTER TABLE -- how to add ON DELETE CASCADE?

From
felix@crowfix.com
Date:
I have table A with a column AA which references table B's primary
key BB, and I want to alter column AA to delete on cascade.

    ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE

is what I tried with a zillion variations, all reporting syntax
errors.

\h alter table seems to be missing any way to add a constraint to a
column.

Or maybe this isn't a constraint ... does seem like maybe the wrong
name, but I can't think what else it would be called, and the other
choices shown with \h seem even less likely.

Your assiatnce is much appreciated.  I would gladly send you some
Oreos or Ginger Nuts :-)

--
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

Re: ALTER TABLE -- how to add ON DELETE CASCADE?

From
Tom Lane
Date:
felix@crowfix.com writes:
>     ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE

You're missing the specification of the foreign key, not to mention
spelling the CASCADE clause backwards.  Try

ALTER TABLE A ADD FOREIGN KEY(AA) REFERENCES B(BB) ON DELETE CASCADE

> \h alter table seems to be missing any way to add a constraint to a
> column.

It's there, though you have to look to \h create table to see the
alternatives for "table_constraint".

            regards, tom lane

Re: ALTER TABLE -- how to add ON DELETE CASCADE?

From
felix@crowfix.com
Date:
On Tue, Mar 07, 2006 at 05:36:37PM -0500, Tom Lane wrote:
> felix@crowfix.com writes:
> >     ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE
>
> You're missing the specification of the foreign key, not to mention
> spelling the CASCADE clause backwards.  Try
>
> ALTER TABLE A ADD FOREIGN KEY(AA) REFERENCES B(BB) ON DELETE CASCADE

Got it right in the Subject: and my many attempts, just not in the
body :-)

The column already had the foreign key, I never thought to add it
again.  I was only thinking of modifying the minimum necessary.

Thanks.

--
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

Re: ALTER TABLE -- how to add ON DELETE CASCADE?

From
Jim Nasby
Date:
On Mar 7, 2006, at 4:42 PM, felix@crowfix.com wrote:

> On Tue, Mar 07, 2006 at 05:36:37PM -0500, Tom Lane wrote:
>> felix@crowfix.com writes:
>>>     ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE
>>
>> You're missing the specification of the foreign key, not to mention
>> spelling the CASCADE clause backwards.  Try
>>
>> ALTER TABLE A ADD FOREIGN KEY(AA) REFERENCES B(BB) ON DELETE CASCADE
>
> Got it right in the Subject: and my many attempts, just not in the
> body :-)
>
> The column already had the foreign key, I never thought to add it
> again.  I was only thinking of modifying the minimum necessary.

Yeah, unfortunately there's no support for modifying constraints.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



Re: ALTER TABLE -- how to add ON DELETE CASCADE?

From
David Fetter
Date:
On Wed, Mar 08, 2006 at 04:06:55PM -0600, Jim Nasby wrote:
> On Mar 7, 2006, at 4:42 PM, felix@crowfix.com wrote:
>
> >On Tue, Mar 07, 2006 at 05:36:37PM -0500, Tom Lane wrote:
> >>felix@crowfix.com writes:
> >>>    ALTER TABLE A COLUMN AA ADD CONSTRAINT DELETE ON CASCADE
> >>
> >>You're missing the specification of the foreign key, not to mention
> >>spelling the CASCADE clause backwards.  Try
> >>
> >>ALTER TABLE A ADD FOREIGN KEY(AA) REFERENCES B(BB) ON DELETE CASCADE
> >
> >Got it right in the Subject: and my many attempts, just not in the
> >body :-)
> >
> >The column already had the foreign key, I never thought to add it
> >again.  I was only thinking of modifying the minimum necessary.
>
> Yeah, unfortunately there's no support for modifying constraints.

Well, except in the sense of dropping and re-creating them inside a
transaction :)

Cheers,
D (transactional DDL is fantastic :)
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!