Re: Primary Keys optional?

On 8/17/06, Stephen Andert <andert_at_gmail.com> wrote:
>
> So my questions are:
>
> 1. Is it considered acceptable to have a unique index instead of a pk?

Hi Stephen,

unique index != primary key

primary keys require not null columns
unique indexes do not.

If there are no tables with PK, there has been no design effort.

It's more along the lines of "let's throws some columns at some tables and see what sticks"

Without Primary Keys, it is highly unlikely that the database is in 2nd normal form (columns depend on the entire key)

If it isn't in 2NF, then it also cannot be in 3NF, BCNF, 4FN, or any other higher form of NF.

Well, maybe DNF.

It's also very likely that the tables are not in 1NF (repeating groups) address_1, address_2, ...
concatenated strings (heaven forbid)

In short, the data in this database is likely to be a mess.

INSERT, DELETE and UPDATE statements will all be difficult to write. DML will be subject to anomolies.

Data will be inaccurate as well.

I could go on, but I've ranted long enough.

2. What are the circumstances when a table might be allowed to exist
> without any sort of primary key or unique index? (i.e. temp table,
> static small table, etc)
>
>

Temp tables: maybe. It depends on how and why they are used.

Small tables: these definitely require a PK. Size does not matter.

If possible, you may want to create PK and FK.

If you are able to do so, the developers will then come knocking at your door, as they will no longer be able to put bad data in the database.

Your challenge is to educate them. :)

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
and Database Design  Bigot

--
http://www.freelists.org/webpage/oracle-l