Re: does a table always need a PK?

In the last exciting episode, etp <etp_at_spamoutgun.com> wrote:
> If I want to model say a book where I have a root element of book represented as
> n pages, can I do something like this:
>
> table BOOK
> -----------------------------
> pageid FK
>
> and
>
> table PAGES
> -----------------------------
> pageid PK
> content text
>
> i.e do I need to have a (f.e.) primary key in the BOOK table? The only reason I
> can see that I would want to is if I want to have many different books but then
> I would have to have BOOK, and then a BOOK_PAGES joining table no?

You almost certainly should have a UNIQUE primary key on every relation.

Relations are used to express facts, and it doesn't normally make sense to have duplicate facts. Duplicates tend to lead to trouble...

You might have multiple copies of a book in a library; in that case, it would make sense to have those books keyed on (ISBN, COPY_NUMBER), as the books are physically distinct even if they appear identical.

In the case of your BOOK relation, it might make sense for the relation to look something like:

create table book (
  isbn isbn,
  page integer
  primary key (isbn, page)
);

(Where the ISBN domain is one that characterizes ISBN ids. You might alternatively use an internal identifier, and have the ISBN in a "book catalogue" table...)

-- 
(reverse (concatenate 'string "moc.enworbbc" "_at_" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/postgresql.html
Lisp stoppped itself
FEP Command: