Foreign Key help needed. Thanks!

[jerhill]

I'm trying to relate three tables and can't seem to get the third , "Authors," into the game.
The WriterSong.writer_id and Authors.ID should be the matching data. I'm getting this error when I try to add to onto the relationship:

SQL Status: 23000
Error code: -177

Integrity constraint violation - no parent 6, table: WriterSong in statement [ALTER TABLE "WriterSong" ADD FOREIGN KEY ("Writer_ID") REFERENCES "Authors" ("ID")]

When I run the statement from Tools>SQL, the results are:

1: Integrity constraint violation - no parent 6, table: WriterSong in statement [ALTER TABLE "WriterSong" ADD FOREIGN KEY ("Writer_ID") REFERENCES "Authors" ("ID")]

I am new at this and obviously missing something. Thanks for any help.

[FJCC]

I suspect one of the rows of "WriterSong" has a "Writer_ID" with no counterpart in "Authors"."ID". If the tables are too large to check this by hand, you could do it with a query, something like

Code: Select all

SELECT "Writer_ID", "ID" FROM "WriterSong" LEFT OUTER JOIN "Authors" ON "WriterSong"."Writer_ID" = "Authors"."ID" WHERE "Authors"."ID" IS NULL

I am not entirely sure that query is correct. I have not written a query in a while.