simple table organisation question

#11

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

Quote:

"Paul G. Brown" <paul_geoffrey_brown (AT) yahoo (DOT) com> wrote in message
news:57da7b56.0308061732.4e0c437 (AT) posting (DOT) google.com...
joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message n
The part I don't
understand is why we never required a PRIMARY KEY constraint on all
tables.

Because, on occasion, a DBMS needs to be able to swallow a file
that looks like this:

--BEGIN--
1
1
1
2
1
1
2
1

And I have a table that looks like this:

a b
a c
a d
a e
b a
b d
c a
c e
c b

purely as a join condition assistor, (it helped massively and reduced
query
times from 5 to 0.5 seconds)

Your table has a candidate key--both columns.

#12

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

Quote:

And I have a table that looks like this:

a b
a c
a d
a e
b a
b d
c a
c e
c b

purely as a join condition assistor, (it helped massively and reduced
query
times from 5 to 0.5 seconds)

Your table has a candidate key--both columns.

Doh! Yes of course.

--
Mike W

#13

joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message news:<a264e7ea.0308061415.6c58e590 (AT) posting (DOT) google.com>...

Quote:

But I was referring to a multi-field key, in that case one (or
more) fields [sic] can be null.

Nope. If you use the PRIMARY KEY constraint, **all** columns must be
NOT NULL. Trust me; I did spend ten years on the Standards Committee.
Now, a UNIQUE () constraint can have NULLs. The part I don't
understand is why we never required a PRIMARY KEY constraint on all
tables.

Time for me to learn more.

The standard you talk about here I assume is the SQL standard? (of
course, that's why your name is on the title page! Mine is an older
copy, SQL92.) yes, there it is, under CREATE TABLE and the "column and
table constraints" subheading.

I guess that's why so many systems I see have used pseudoKeys such as
ID numbers as their primary keys since it avoids this issue. But it
doesn't solve the problem. So I guess I'd have to refine my suggestion
to have a reviews relation that ties the entities together with a
primary key of ID and then put the multiple attributes that relate the
various parts (authors, books, and reviews).

I'll have to go back and review my notes on normalization. (I don't do
DB design often enough to have all the details memorized.) But this is
a SQL constraint, so implimentation time must deal with it.

Thanks CELKO. You are always informative.

Ed

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

simple table organisation question

comp.databases comp.databases

Re: simple table organisation question - 08-07-2003 , 11:26 AM

Re: simple table organisation question - 08-07-2003 , 11:38 AM

Re: simple table organisation question - 08-07-2003 , 12:49 PM