Re: 2 Oracle doubts

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 - 13:44:56 CDT