How do I specify a column to be unique in the scope of a relationship in SQL Server 2008?

Question

It's easier with an example. I have two tables: Books and Chapters. Both have Title and Id columns. Chapters also has a Book column which is a foreign key to Books.Id. Books.Title is unique (I don't want two books with the same title).

Now my problem is defining Chapter.Title uniqueness. I want it to be unique as long as Books.Id is the same. So that one book may not have two chapters with the same title but two different books may have the same title in a chapter.

Is this possible on SQL Server 2008? How?

Answer 1

Create a UNIQUE INDEX on Chapters using columns BookID and Title (indexes may include several columns, not just one)

Answer 2

So within Chapter, it is the combination of Book and Title that must be unique. Simply create a UNIQUE index spanning those columns, and you're done.

By the way, I can't help but think that

(I don't want two books with the same title)

is going to come back and bite you, if you're actually talking about real-world books. If this is just an example, then no worries.

Answer 3

UNIQUE CONSTRAINT or UNIQUE index. Either will do.

ALTER TABLE dbo.MyTable WITH CHECK ADD
    CONSTRAINT UQ_MyTable_BookTitle UNIQUE (BookID, Title)

or

CREATE UNIQUE INDEX IXU_MyTable_BookTitle ON dbo.MyTable (BookID, Title)