0

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?

  • Welcome to this site. Can you add some sample data to clarify your model? I'm having a bit of difficulty following it. Be sure to include the non-unique data you talk about so we can see what your problem is. – Colin 't Hart Nov 2 '18 at 9:09
0

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

0

You need both the book ID and the user ID to identify a book, so both columns together are the primary key:

CREATE TABLE books (
    book_id  integer,
    user_id  integer REFERENCES users(user_id),
    ...,
    PRIMARY KEY (identifier_id, user_id)
);

So when you then declare that a chapter must reference a book, the constraint must use both IDs, and everything comes out right:

CREATE TABLE chapters (
    ...,
    book_id  integer,
    user_id  integer,
    FOREIGN KEY (book_id, user_id) REFERENCES books (book_id, user_id)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.