Re: Constraints/On Delete...

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.