Re: 2 Oracle doubts

Hi Paul Brewer, thanks for writing this:
> 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]
>

I understand your point. However, most of our lookup tables are not maintained. They are loaded by a sql script once and just sit there. If there's an error (such as a duplicate) that sneaks in, the table is simply recreated. Unless someone is really interested in messing it up by using sqlplus, there's little chance of duplicates sneaking in.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]