Re: Constraints/On Delete...

On Sat, 4 Jan 2003, Boget, Chris wrote:

> Newbie alert!! :p
>
> I have 2 tables already created
>
> CREATE TABLE "first" (
> "name" varchar (100) NOT NULL,
> "record_num" SERIAL,
> PRIMARY KEY ("record_num"));
>
> CREATE TABLE "second" (
> "text" varchar (10) NOT NULL,
> "name" int4 REFERENCES "first"("record_num") NOT NULL,
> "record_num" SERIAL ,
> PRIMARY KEY ("record_num"));
>
> and have records in each. The "name" field in the "second"
> table contains data that matches data in the "first". Now,
> when I go to delete a row from "first", I'm getting the error:
>
> ERROR: $1 referential integrity violation - key in first still referenced
> from second
>
> which I can understand. Now, how can I alter the "first" table
> so that when I delete rows from it, the corresponding rows from
> the "second" table are deleted as well? I was looking at the
> ALTER TABLE syntax but the only thing I saw there was adding a
> foreign key. But I already have a foreign key set up by the
> REFERENCES in the create definition for the "second" table, yes?
> So what do I need to do?

You don't alter first. You need to drop the constraint on second and add
a new constraint on second with on delete cascade.