Re: diff. between Primary-Key and unique index

> Andreas Michler <Andreas.Michler_at_adicom.de> a écrit dans l'article
> <388819AE.547713F0_at_adicom.de>...
> Can anyone tell me the differences between a table with primary key
> and
> a table with no primary key, but a unique index on some fields
> ????????

we shouldn't mess things up, therefore I try to explain:

the 'primary key' in the relational theory is a set of attributes of a tuple, which are discovered to be 'discriminant' for a given tuple in the analysis phase - this means

given a tuple carrying the attributes (a, b, c, d, e, f, g) and the analysis yields that
a, b -> c, d, e, f, g we call a and b the 'discriminant columns' of the tuple, this property is equivalent to be a 'candidate primary key' - since these 'discriminant columns' describe the tuple they cannot be null - because null means - not currently known - ex falso sequitur quodlibet ... so what could you derive from 'i dunno' ?? and have to be unique - of course ...
but you don't really need a pk in theory - because any tuple is per se unique by definition -

in the technical implementation and in a consequence of the data modelling we start using the primary key to speed up access and enforce uniqueness for the discriminant columns -
by use of the primary key constraint -
this does three things for us:
1) in the repository, our discriminant columns become known for the table - so you can reference the tables pk with a foreign key constraint later on from other tables -
2) these columns are per definition set to be NOT NULL 3) in anticipation of heavy access using these columns, and to enforce the uniqueness of this set of columns, ORACLE just puts a unique index upon them - automatically -

so far for the primary key

unique indexing is
a) a tool to ease the load of enforcing uniqueness upon a (set of) column(s)
b) opening a fast access path into a table via a (set of) column(s)

it doesn't either imply 'discriminant properties' nor 'not null' thats it - no more

hope I could help Received on Tue Jan 25 2000 - 09:28:04 CST