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