agreed (well, for your first point, physically speaking, maybe the
table is created once and then no more DML is allowed?). but i've
often wondered this, what if you had some kind of "event logging"
table that just had, say, a user_id, date, and action (maybe action is
a lookup, so action_id, let's say)? would this would need a PK?
"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message news:<3f217acd$1_1_at_mk-nntp-1.news.uk.worldonline.com>...
> Karsten,
>
> Sorry. Disagree strongly.
> Apart from anything else, PK constraint prevents someone from accidentally
> duplicating an existing 2 char state code. And who on earth would use a
> sequence, when the US Postal State Code is *intended* to be unique?
> And what about when you need a FK constraint from your Address table to the
> state table?
>
> Regards,
> Paul
>
>
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:MPG.198b01e758561e9f989807_at_news.la.sbcglobal.net...
> > Hi delavega, thanks for writing this:
> > > Hi. I´m a MySql programmer, but i need make a application with Oracle
> > > 8.1 Server. I have 2 doubts (for the moment)
> > >
> > > 1- Can i have a Oracle table without primary Key?
> > > 2- In a sql, the date fields are with quotas ("")? Exists the
> > > direct comparation between dates (<, >)?
> > >
> > > Thanks!
> > >
> >
> > Several posts have asked why you want a table without a primary key. In a
> > pure "data design" sense, that's a good question. However, in implementing
> > a physical representation of your logical design, it's sometimes prudent
> > to have a table without a primary key.
> >
> > For example, we have a table that contains the two-character state
> > abbreviation and its spelled-out (long) name. Unless something very
> > unusual happens in the USA, there are only 52 rows in this table. Of
> > course, I could use the state abbreviation as the PK (since it will be
> > unique), or even use a sequence, but doing so would cause Oracle to
> > generate an index for it. Now I know that all 52 states will easily fit in
> > an Oracle block, so the optimizer will never use the index ... which means
> > I've wasted space (not a lot of space, mind you).
> >
> > We have other lookup tables that have things like status codes and
> > descriptions. They also don't have a PK. A full table scan (in memory) on
> > these small tables is not a serious performance hit. So, in conclusion,
> > the answer is "it depends."
> >
> > --
> > [:%s/Karsten Farrell/Oracle DBA/g]
Received on Fri Jul 25 2003 - 16:25:34 CDT
