Foreign Key help needed. Thanks!

Discuss the database features

Foreign Key help needed. Thanks!

Postby jerhill » Sat Nov 24, 2018 5:26 am

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.
OpenOffice 4.1.6 on MAC 10.14.1
jerhill
 
Posts: 1
Joined: Sat Nov 24, 2018 4:30 am

Re: Foreign Key help needed. Thanks!

Postby FJCC » Sat Nov 24, 2018 6:10 am

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   Expand viewCollapse view
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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7090
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests