Re: null as part of key?

It's not "OK" for a unique index: the index won't contain pointers to table records that have a null value in the key field. So it's not really 'ok' -we just ignore them. It is OK for a unique *constraint*, because uniqueness simply means not having a value another record has. Since null means 'unknown', we can't compare null with null, and hence records with null values in the constrained column can't possibly be duplicating each other -we simply don't know what their value is.

But a primary key is used to identify, uniquely, *every* row in a table, with no skipping of awkward rows, and I don't rate your chances of ever identifying anything if you don't know what it is that you're looking for!

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"ed zappulla" <zappullae_at_rcn.com> wrote in message
news:9r42ov$k4m$1_at_bob.news.rcn.net...


> i agree that a non business id would work.

>

> i don't understand why null is not a valid value for a key part but is ok

> for a unquie index.

>

>

> "Henning Follmann" <h.follmann_at_gmx.de> wrote in message

> news:slrn9tamm8.hl.h.follmann_at_mobile.follmann-net...

> > On Mon, 22 Oct 2001 11:40:57 -0400, ed zappulla <zappullae_at_rcn.com>

wrote:


> > > I see.  What about this case

> > >

> > > country

> > > state

> > > city

> > > some other cols

> > >

> > > pk = country, state, city

> > >

> > > Some contries don't have states; therefore it would be null for those

> > > countries.

> > >

> >

> > The problems you are  just encountering show some flaws in your

> > datamodeling.

> >

> > 1) do not use production data as a primary key

> >

> > This is a good rule!

> >

> > Suggestion:

> >

> > Create a metadata table:

> > it includes for every country the meta information about address

formats.


> > Use for the countries the iso standard! Sooner or later you will be

> > thankfull you did so!

> >

> > now the rough design

> >

> > Party : Party_ID, PartyType_ID

> >

> > Address: Party_ID, AddressMeta_ID, ...

> >

> > AddressMeta: AddressMeta_ID, Country_ID, Field1, Field1Flag (Mand, Opt,

> > notuse) ...

> >

> > Country: Country_ID (Num(3)), Name, A2, A3

> >

> >

> >

> >

> > I use also Party, because you might have Companies, private Persons ...

> > A Party might have also more than one Address, so you might tweak it for

> > your purposes.

> > It might also a good idea to place some rules for checking the data

> > depending on the country. If you use MetaData this gives you the

> > flexibility to stor the validation rules (names of procedures) in your

> > table.

> >

> >

> > Good luck,

> > dealing with international addresses, currencies, etc. is not easy.

> > I hope this helps.

> >

> >

> >

> >

> > > does this make sense?

> > >

> > >

> > >

> > > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in

> message

> > > news:3bd42f49$0$237$ed9e5944_at_reading.news.pipex.net...

> > >> No. Nor in any version. If you think carefully you will see that

> allowing

> > >> this would be a nonsense.

> > >>

> > >>

> > Cheers,

> > henning

> >

> > --

> > Henning Follmann      |     8 Jane Road

> > Tel.: +1 908 656 7061 |     New Providence, NJ 07974

> > H.Follmann_at_gmx.de     |     USA

> >

> >

> > -----=  Posted via Newsfeeds.Com, Uncensored Usenet News  =-----

> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!

> >  Check out our new Unlimited Server. No Download or Time Limits!

> > -----==  Over 80,000 Newsgroups - 19 Different Servers!  ==-----

>

>