Thread: ALTER TABLE -- how to add ON DELETE CASCADE?
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
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
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
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
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!