does a table always need a PK?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

etp - 22 Aug 2003 21:08 GMT

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?

Danke!

-Tomas

,%%%,
,%%%` %==--
,%%`( '|
,%%@ /\_/
,%.-"""--%%% "@@__
%%/ |__`\
.%'\ | \ / //
,%' > .'----\ | [/
< <<` ||
`\\\ ||
)\\ )\
^^^^^^^^"""^^^^^^""^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
THE DUKE

Reply to this Message

Jerry Gitomer - 22 Aug 2003 23:18 GMT

> 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:
[quoted text clipped - 30 lines]
> `\\\ ||
> )\\ )\

^^^^^^^^"""^^^^^^""^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

> THE DUKE

As long as you only have one book you have no need for a primary
key ;-) You should always have a primary key. In fact many
DBMS require one.

You do not need a BOOK_PAGES joining table. You could add a
column for BOOK_ID to your PAGES table.

Signature

Once I learned how to spell DBA I became one -- Jerry Gitomer

Reply to this Message

Bob Badour - 22 Aug 2003 23:39 GMT

> > 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:
[quoted text clipped - 36 lines]
> As long as you only have one book you have no need for a primary
> key ;-)

Even then, there is a primary key; it just has zero columns. At least, the
table has one and only one candidate key so one might as well call it a
primary key.

> You should always have a primary key. In fact many
> DBMS require one.

One should always have at least one candidate key. When multiple candidates
exist, it is not always clear that one benefits from identifying one of the
candidates as the primary key. However, if one intends users to use one
candidate in particular for referential integrity, it makes sense to declare
this candidate as the primary key.

> You do not need a BOOK_PAGES joining table. You could add a
> column for BOOK_ID to your PAGES table.

Actually, I am not sure what function the BOOK table fills either.

Reply to this Message

Jan Hidders - 23 Aug 2003 00:06 GMT

>> > 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:
[quoted text clipped - 16 lines]
>
> Even then, there is a primary key; it just has zero columns.

In that case that table could have at most one record and I don't think that
is what Tomas had in mind. But I wonder if Tomas himself has a clear idea
of what it is that he wants to model. :-)

-- Jan Hidders

Reply to this Message

Tux - 22 Aug 2003 23:49 GMT

> 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:
[quoted text clipped - 11 lines]
>
> i.e do I need to have a (f.e.) primary key in the BOOK table?

That's the wrong question. You should first ask if you really need the BOOK
table. Since it is always going to be a projection of the PAGES table on
pageid it doesn't really add any information that is not already in the
PAGES 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?

No. All you would need is a FK in PAGES that points to the PK of BOOK and
indicates to which book the page in question belongs.

> Danke!

Bitte.

-- Jan Hidders

PS. Homework: Explain the relationship between Wittgensteins famous last
proposition (#7) in the Tractatus Logico Philosophicus and the
requirement that every table should have at least one candidate key.

Reply to this Message

Christopher Browne - 23 Aug 2003 03:34 GMT

In the last exciting episode, etp <etp@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:
[quoted text clipped - 13 lines]
> 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...)

Signature

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

Reply to this Message

Paul - 23 Aug 2003 18:28 GMT

cbbrowne@acm.org says...

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

Maybe this true in theory, but not essential in practice?

I'm not being facetious here.

Take an example that I recently worked on - we have a lookup table of 26
counties (in Ireland) which really has a very small about of data in it,
say max. 1.5K.

Now, as I understand it, RDBMS's will look at the size of a table before
scanning it to see if it's worthwhile using an index, and if the table
is too small, it'll just perform a straigh scan anyway and not bother
with the index, even if you've gone to the trouble of putting one in.

AFAIK, this is true for the RDBMS's that I use (Interbase, FireBird and
PostgreSQL).

Any thoughts, rants, references, URLs on this topic welcomed.

Paul...

Signature

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

p.s. just lacerated a tendon in left hand, so pls
excuse typos and tricky abbrevs - TIA.

Reply to this Message

Shane Petroff - 23 Aug 2003 18:59 GMT

> cbbrowne@acm.org says...
>
[quoted text clipped - 5 lines]
>
> AFAIK, this is true for the RDBMS's that I use

As far as anyone can tell, this is only true for some subset of dbms's
right now. Whether it holds true for any dbms in general or any of the
aforementioned at some other time is impossible to say. The
implementation that some specific db uses at some point in time is
entirely irrelevant to the topic at hand. The existence of a primary key
is a topic related to the structure of the data, not an implementation
strategy in some specific dbms.

Shane

Reply to this Message

Venkat - 23 Aug 2003 19:28 GMT

"Paul" <paul@not.a.chance.ie> wrote in

> Take an example that I recently worked on - we have a lookup table of 26
> counties (in Ireland) which really has a very small about of data in it,
[quoted text clipped - 3 lines]
> is too small, it'll just perform a straigh scan anyway and not bother
> with the index, even if you've gone to the trouble of putting one in.

Paul,

You are confusing an index with a key. Index is physical, while a key is
logical. Not all keys need to have a related index. Even if the key has an
associated index, it need not be used in the queries. For example, some
RDBMSs don't create indexes (indices) for the foreign keys, while some of
them create the associated index automatically. In some RDBMSs, even the
unique indexes for the primary/unique key have to be explicitly created.

> AFAIK, this is true for the RDBMS's that I use (Interbase, FireBird and
> PostgreSQL).
>
> Any thoughts, rants, references, URLs on this topic welcomed.

Here is one URL that might be of use:
http://www.aisintl.com/case/relational_keys.html

> p.s. just lacerated a tendon in left hand, so pls
> excuse typos and tricky abbrevs - TIA.

Hope you get your tendon fixed soon.

-- Venkat

Reply to this Message

Pablo Sanchez - 23 Aug 2003 19:57 GMT

> Now, as I understand it, RDBMS's will look at the size of a table
> before scanning it to see if it's worthwhile using an index, and if
> the table is too small, it'll just perform a straigh scan anyway and
> not bother with the index, even if you've gone to the trouble of
> putting one in.

Aside what Venkat points out, the more complex the query (more tables
in the join) it's possible that the above becomes grossly
inefficient. In-memory table scans can be very costly when paired
with (millions of) nested iterations.

Signature

Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com

Reply to this Message

Bob Badour - 24 Aug 2003 00:42 GMT

> cbbrowne@acm.org says...
>
[quoted text clipped - 15 lines]
> AFAIK, this is true for the RDBMS's that I use (Interbase, FireBird and
> PostgreSQL).

Uniqueness is a logical constraint independent of any physical structure
like an index. It is true that SQL generally confuses the two levels by
making uniqueness an attribute of an index.

Without any uniqueness, how do you plan to distinguish between rows?

Reply to this Message

Christopher Browne - 24 Aug 2003 04:09 GMT

Centuries ago, Nostradamus foresaw when Paul <paul@not.a.chance.ie> would write:

> cbbrowne@acm.org says...
>
[quoted text clipped - 3 lines]
>
> I'm not being facetious here.

Do you want counties to be unique?

If you do, then you need a UNIQUE key that will amount to providing a
PK.

Size is irrelevant to the matter.

The issue is of whether or not you want to use a relational database
to actually maintain some aspects of correctness.

If you don't care about correctness, then feel free to do whatever you
like.

I have seen database systems that accept invalid inputs, silently
transforming erroneous data into other erroneous data. (Indeed, that
is true for date handling for one of the most popular "open source"
database systems.) That doesn't give me any "warm fuzzies" about the
wisdom of using it to store data of any importance.

Having the UNIQUE index is a way of making sure that the data in the
database is CORRECT (or at least of preventing /some/ errors); I don't
give a rip whether the index is used for queries; the point of the
exercise is to keep from filling the database with garbage.

> AFAIK, this is true for the RDBMS's that I use (Interbase, FireBird
> and PostgreSQL).

You seem to be falling into the trap of becoming dependent on the
physical and/or logical representation of the data. Codd's principles
include that activities should remain unimpaired when changes are made
to physical and logical storage representations or access methods.

I could not care less whether or not the "region" index gets used to
access the data. The point of the index, in this case, is NOT to
improve access times, but rather to enforce uniqueness of the primary
key. What the query planner does or does not do is totally irrelevant
to that point.

Signature

let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/spreadsheets.html
"I'm sorry, the teleportation booth you have reached is not in service
at this time. Please hand-reassemble your molecules or call an
operator to help you...."

Reply to this Message

Bernard Peek - 26 Aug 2003 20:03 GMT

>In the case of your BOOK relation, it might make sense for the
>relation to look something like:
[quoted text clipped - 8 lines]
>alternatively use an internal identifier, and have the ISBN in a "book
>catalogue" table...)

To be pedantic (and I am replying to this in a theory newsgroup) you
should probably call this table Editions instead of Book. If you have
two books with the same ISBN they are instances of an edition. You would
expect two books with the same ISBN to have identical pagination.

If you want to distinguish between books which are copies of the same
edition you may have to introduce some other distinction.

Two that actually exist are the "state" and "printing."

If an edition of a book is printed and half the copies are bound in
yellow covers and half have green covers then whichever went on sale
first is the "first state." This can have make a factor of 10 difference
in their selling price.

Typically popular books are reprinted and these days the number of the
printing is recorded on the flyleaf. Successive printings may have the
same ISBN but collectors always want the first printing. The difference
in price between a first printing and a later one may be $30,000 so this
is something that a collectors' or sellers' database must record.

Signature

Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Reply to this Message

--CELKO-- - 23 Aug 2003 23:44 GMT

>> do I need to have a primary key in the BOOK table? <<

Yes, otherwise it is not a table by definition. Assuming that you
have more than one book, use the ISBN as the industry standard
identifier and use this table:

CREATE TABLE Books
(isbn CHAR(10) NOT NULL,
page_nbr INTEGER NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (isbn, page_nbr));

if you had only one book, use its title for the table name:

CREATE TABLE "Moby Dick"
(page_nbr INTEGER NOT NULL PRIMARY KEY,
content TEXT NOT NULL);

You are splitting attributes in the original schema. That is, you put
the attributes of a book in (n > 1) table, so the data model was not
complete.

Reply to this Message