Re: Row based keys or composite key

From: Nandakumar <N.Kumar_at_rocketmail.com>
Date: Mon, 08 Nov 1999 16:50:55 GMT
Message-ID: <806v1e$75q$1@nnrp1.deja.com>

Thanks for all your responses!

so here's the conclusion!

1. A primary key ( no matter simple or composite) CAN NOT accept NULL value. NOT NULL constraint is automatically attached to all primary key fields.

( DEFALUT clause places the default value when no value is specified. so the default does not make much sense with PK fields except for one insert when no value is specified for the PK field and the default value does not already exist in the table for that PK.)

An interesting fact is that, default clause does not make any sense with PK field in a single column table. Because no insert or update is possible on a single column table without specifying a value. If any objections, pls. let me know!!

2) If distinctness has to be enforced on a row by one more more fields, define a primary key with those fields. Or a UNIQUE and NOT NULL on those fields. ((( This raises a question, what would be the difference between PK field and a UNIQUE & NOT NULL field?? Any thing to do with PK&FK relationship???))

regards
Nanda

In article <7vdd0b$nn0$1_at_news4.svr.pol.co.uk>,   "-=< a q u a b u b b l e >=-"
<aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com> wrote:
> Jurij Modic <jmodic_at_src.si> wrote in message
> news:381b0357.13804941_at_news.siol.net...
> > On Fri, 29 Oct 1999 20:23:24 +0100, "-=< a q u a b u b b l e >=-"
> > <aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com> wrote:
> >
> > >Nandakumar <N.Kumar_at_rocketmail.com> wrote in message
> > >news:7vcnl3$3sp$1_at_nnrp1.deja.com...
> > >> hi
> > >>
> > >> few question regarding row based keys ( user-generated or system
> > >> generated).
> > >>
> > >> 1) if we define a composite primary key with several fields, can
these
> > >> fields allow null values in them?
> > >
> > >Yes, but it is better to define it as a not null column and have
some
> > >default value in it e.g. NOTAVAIL, otherwise the index will not be
used.
> >
> > No, primary keys do not allow null values in any of the columns
> > conforming PK. In fact, even if the column doesn't have NOT NULL
> > constraint, when you include it in a PK constraints the columns will
> > implicitly become NOT NULL.

>
> Yes you're correct... I'll remember this now! Just shows how your
brain
> deteriorates on a Friday night.

>
> What I said still holds true for composite *indexes*, especially when
you're
> likely to have nulls in the columns. In fact, if Nandakumar is asking
the
> question because he has nulls in some columns that he wants to be part
of
> his PK, then it would be best to have a default value.

>

> Cheers... I going back to tuck into more of my fine British beef!
>
>

--
Nandakumar
(N.Kumar_at_rocketmail.com)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 08 1999 - 10:50:55 CST