Cannot add SQL Foreign Key Constraint

Question

I have two tables. One is called BooksRead, one is called Authors. Authors has a Primary Key of BOTH author_last_name and author_first_name.

I'm using Netbeans IDE.

I want to alter table BooksRead, which also has columns author_last_name and author_first_name, to reference Authors as Foreign Keys.

ALTER TABLE BooksRead
ADD FOREIGN KEY(AUTHOR_LAST_NAME, AUTHOR_FIRST_NAME) 
REFERENCES AUTHORS(AUTHOR_LAST_NAME, AUTHOR_FIRST_NAME);

I've tried to do this many different ways--including adding/naming a CONSTRAINT--but always get the same error:

[Exception, Error code 30,000, SQLState X0Y45] Foreign key constraint 'SQL170903182055780' cannot be added to or enabled on table BOOKSREAD because one or more foreign keys do not have matching referenced keys.

Also, sounds like your `BooksRead` table has data that violates the foreign key constraint, ie there are no matching records in `Authors` — Phil, Sep 03 '17 at 23:43
That's the part that's puzzling--Authors' Primary Key is author_last_name and author_first_name. It should work. — beh1, Sep 03 '17 at 23:50
It's not puzzling at all. This is what can happen when you start adding data before constraints. — Phil, Sep 03 '17 at 23:52
Please read & act on [mcve]. You elicited two guesses (that should have been this comment.) Anyway this is a faq. There's a [derby answer](https://stackoverflow.com/a/8918957/3404097) with your error & causes but not what turned out to be your problem; but there are lists to troubleshoot in answers for sql generally & other sqls. — philipxy, Sep 04 '17 at 02:35

score 1 · Answer 1 · answered Sep 03 '17 at 23:47

1

Sounds like your BooksRead table has data that violates the foreign key constraint, ie there are no matching records in Authors.

Find and fix the records before adding the constraint

SELECT br.*
FROM BooksRead br
WHERE NOT EXISTS (
  SELECT 1 FROM AUTHORS a
  WHERE a.AUTHOR_LAST_NAME = br.AUTHOR_LAST_NAME
  AND a.AUTHOR_FIRST_NAME = br.AUTHOR_FIRST_NAME
)

That will give you a list of BooksRead rows not having a matching AUTHOR. You can either

Delete those rows
Add the relevant author details to AUTHORS, or
Perhaps the author details in BooksRead have typos or leading / trailing whitespace. Fix the author details in the BooksRead row to match records in AUTHORS

answered Sep 03 '17 at 23:47

Phil

157,677
23
242
245

2

It was one entry in the AUTHORS table that had a space between -- "R. R. Martin" --that threw it off. It was supposed to have "R.R. Martin". So, you were right: it was what happened when you add data before constraints. Thanks! – beh1 Sep 04 '17 at 00:13
@beh1 now would be a good time to normalise your data and use numeric IDs in the `AUTHORS` table and reference those instead of the composite key – Phil Sep 04 '17 at 00:15

score 0 · Answer 2 · answered Sep 03 '17 at 23:45

0

You must check if the existing data on the child table,exists on the parent as a primary key

answered Sep 03 '17 at 23:45

Jose Agustin Reinoso

65
1
8

Cannot add SQL Foreign Key Constraint

2 Answers2