Thread: Constraints/On Delete...

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?

thnx,
Chris

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.

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

Could you give me an example of this?  This is what I tried:

ALTER TABLE "second" ADD CONSTRAINT "secondfk"
FOREIGN KEY (record_num) REFERENCES "first"("record_num")
ON DELETE CASCADE

which gave me this error:

ERROR: secondfk referential integrity violation - key referenced
from second not found in first

What am I doing wrong?

Also, from the looks of it, it seems like what will happen is
when records from "second" are deleted, the referenced records
in "first" will be deleted, too.  Is this the case?  If so,
what I want to happen is that when records are deleted in the
"first table" (the root or parent table, if you will), records
will also be deleted in the "second" table.  Again, if so, how
do I go about setting this functionality up?

Chris

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

> > You don't alter first.  You need to drop the constraint on
> > second and add a new constraint on second with on delete
> > cascade.
>
> Could you give me an example of this?  This is what I tried:
>
> ALTER TABLE "second" ADD CONSTRAINT "secondfk"
> FOREIGN KEY (record_num) REFERENCES "first"("record_num")
> ON DELETE CASCADE
>
> which gave me this error:
>
> ERROR: secondfk referential integrity violation - key referenced
> from second not found in first

I think you want
ALTER TABLE "second" DROP CONSTRAINT "$1";
ALTER TABLE "second" ADD CONSTRAINT "secondfk"
 FOREIGN KEY (name) REFERENCES "first"("record_num")
 ON DELETE CASCADE;


> Also, from the looks of it, it seems like what will happen is
> when records from "second" are deleted, the referenced records
> in "first" will be deleted, too.  Is this the case?  If so,

No. Referential actions are applied on changes to the primary key side of
the constraint (in this case "first"), so deletes from first cause
actions on second.

Further this, the reason I'm having (serious) difficulties
trying to figure out what's going in is because when I created
the table "second" initially, I used the following create:

CREATE TABLE "second" (
"text" varchar (10) NOT NULL,
"name" int4 REFERENCES "first"("record_num") NOT NULL ON DELETE CASCADE,
"record_num" SERIAL ,
PRIMARY KEY ("record_num"));

And I got the following message:

"...will create implicit trigger(s) for FOREIGN KEY check(s)"

So I was like, ok, dropped the table and recreated it w/o the
ON DELETE CASCADE just to test it.  My new statement looks like
this:

CREATE TABLE "second" (
"text" varchar (10) NOT NULL,
"name" int4 REFERENCES "first"("record_num") NOT NULL,
"record_num" SERIAL ,
PRIMARY KEY ("record_num"));

As mentioned previously, now when I try to delete a record from
"first", I get this error:

QUERY:
DELETE FROM "first" WHERE "record_num" = 2

ERROR: $1 referential integrity violation - key in first still referenced from second

Why is this happening?  I thought the triggers were implicite?
Evidently not.

So now I try to add a foreign key:

QUERY:
ALTER TABLE second
ADD FOREIGN KEY (record_num)
REFERENCES first(record_num) ON DELETE CASCADE

ERROR:
$2 referential integrity violation - key referenced from second not found in first

Fine.  I have no idea why it's doing that.  Why should it matter
what data is in the respective tables?  I'm just trying to create
a rule/functionality/whatever that will happen when records are
deleted.  So now I try this:

QUERY:
ALTER TABLE "second" ADD CONSTRAINT "secondfk" FOREIGN KEY ("record_num") REFERENCES "first"("record_num") ON DELETE CASCADE;

ERROR:
secondfk referential integrity violation - key referenced from
second not found in first

Same as above.  I'm having a very, very difficult time trying to
figure out what's going on and how to make what I'm trying to do
work.  The documentation is very poor wrt this and it's just been
an incredibly frustrating ordeal.

Any help would be greatly appreciated!

Chris

> ALTER TABLE "second" DROP CONSTRAINT "$1";

So the reason mine wasn't working (which looks exactly
like yours) is because there was the above constraint on
the table?  What is the $1?  How can I view the existing
constraints for any particular table?

> ALTER TABLE "second" ADD CONSTRAINT "secondfk"
> FOREIGN KEY (name) REFERENCES "first"("record_num")
> ON DELETE CASCADE;

This worked like a charm!  Thank you very much for your help!

> No. Referential actions are applied on changes to the primary
> key side of the constraint (in this case "first"), so deletes
> from first cause actions on second.

Ok, I just wanted to make sure.  It just looked like it was the
other way around.  Because it appeared that the pointer (the
"references") was going from "second" to "first".  I guess that
when records are deleted from the "first", it asks what tables are
referencing one of it's columns and it's not that it implicitely
already knows..?

Again, thank you for all your help!

Chris

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

> > ALTER TABLE "second" DROP CONSTRAINT "$1";
>
> So the reason mine wasn't working (which looks exactly
> like yours) is because there was the above constraint on
> the table?  What is the $1?  How can I view the existing
> constraints for any particular table?

Not quite.  In your example, you had foreign key (record_num)
references "first"("record_num") which wasn't the correct column to be
referencing from so the data didn't line up correctly.

"$1" is the default name for the first unnamed check/fk constraint on the
table (as of 7.3).

and I think \d <table> will show the constraints now.

> > No. Referential actions are applied on changes to the primary
> > key side of the constraint (in this case "first"), so deletes
> > from first cause actions on second.
>
> Ok, I just wanted to make sure.  It just looked like it was the
> other way around.  Because it appeared that the pointer (the
> "references") was going from "second" to "first".  I guess that
> when records are deleted from the "first", it asks what tables are
> referencing one of it's columns and it's not that it implicitely
> already knows..?

When the constraint is added, triggers are added to first that do the
action or check against second.

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

> CREATE TABLE "second" (
> "text" varchar (10) NOT NULL,
> "name" int4 REFERENCES "first"("record_num") NOT NULL,
> "record_num" SERIAL ,
> PRIMARY KEY ("record_num"));
>
>
> As mentioned previously, now when I try to delete a record from
> "first", I get this error:
>
> QUERY:
> DELETE FROM "first" WHERE "record_num" = 2
>
> ERROR: $1 referential integrity violation - key in first still referenced
> from second
>
> Why is this happening?  I thought the triggers were implicite?
> Evidently not.
They're implicit in the creation of the constraint.  I think part of
the problem is that you're forgetting part of what a foreign key is.

Foreign keys basically say that at any check time (end of statement for
non-deferred constraints) that the foreign key in the foreign key table
(second(name)) must have a matching candidate key in the primary key table
(first(record_num)).  So deleting from first a record_num that has
matching rows violates the constraint since you would have rows in
second that had name=2 while no row in first with record_num=2.

There are some additional things you can add to the foreign key to do
actions at the time of a delete or update from the primary key table.
In this case, some time before the constraint is effectively checkeed the
action occurs (technically our timing is non-complient I believe, but it
doesn't affect this example).  In the case of ON DELETE CASCADE it does
the deletion of the foreign key rows that reference the key that was just
deleted.  In this case the delete from first is okay, because by the time
the check would be done, there are no longer any rows in second with
name=2.

> So now I try to add a foreign key:
>
> QUERY:
> ALTER TABLE second
> ADD FOREIGN KEY (record_num)
> REFERENCES first(record_num) ON DELETE CASCADE
>
> ERROR:
> $2 referential integrity violation - key referenced from second not found in
> first
>
> Fine.  I have no idea why it's doing that.  Why should it matter
> what data is in the respective tables?  I'm just trying to create
> a rule/functionality/whatever that will happen when records are
> deleted.  So now I try this:

As per the above that's not what this does precisely.  If there's a row in
second with a record_num that doesn't show up in first's record_nums the
constraint is immediately violated.