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.

Answer 1

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

Answer 2

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