Postgres - Only allow insert if TWO columns match with a separate table I have three tables: a users table, a books table, and a chapters table.

Each book has an id called identifier_id (for good business reasons that I cannot work around). This id is guaranteed to be unique per-user, i.e. no user will ever have two books with the same id. It is not, however, guaranteed to be globally unique. Each book also has a foreign key user_id.

Each chapter has user_id and book_id foreign keys - the book_id foreign key points to identifier_id on the books table.

When I add chapters to a book, I want to add a database-level contraint that a chapter insert will be rejected unless the user_id of the chapter matches the user_id of the book AND the book_id of the chapter matches a book_id in the books table.

Because the book_id is not globally unique, I cannot put a unique index on it. Is there a way to do this?
You can create a unique compound index

 CREATE UNIQUE INDEX "book_id_user" ON books (identifier_id,user_id);


And then you can create a F.K. constraint

 ALTER TABLE chapters 
   ADD CONSTRAINT "FK_chapter_book" 
     FOREIGN KEY (book_id,user_id)
       REFERENCES books (identifier_id,user_id);


If you have any bad data that will need to be corrected before the constraint can be created