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?