Re: UNIQUE and PK

Hi Howard, Zeb and all,

I think Zeb knows not to put all three constraints on a column (per se) but rather how can a column have all three constraints ?

Well it is possible, it just depends on how you look at it.

SQL> select a.table_name, column_name, constraint_type from dba_cons_columns a, dba_constraints b where a.constraint_name = b.constraint_name and a.table_name = 'BOWIECONS';

TABLE_NAME COLUMN_NAME C
--------------- --------------- -

BOWIECONS       X               U
BOWIECONS       X               P
BOWIECONS       Y               P
BOWIECONS       X               C

If the above formatting allows, you can see that column x has a PK, Unique and NN constraint on it.

Simple really. Just made column x be not null, made column x be unique and made columns (x, y) be PK. Aaahh hhaa, but that's cheating !!

Of course it is but it answers the question :)

Cheers

Richard

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:ahn3aa$8m6$1_at_lust.ihug.co.nz...
> "Primary Key" means "not null plus unique".
>
> Therefore, you don't put all three on the one column, you just make it the
> primary key, and that takes care of the other two.
>
> If you've already got a unique constraint preventing the creation of a
> primary key, drop it and create the primary key constraint on its own
> instead.
>
> Put it another way: a column declared to be unique is guaranteed unique
for
> every row, except that you are allowed any number of rows which leave the
> column null, because null can't be compared to null, and therefore there's
> no basis for testing that one null is duplicating another null. A Primary
> Key also guarantees uniqueness for every row, but since every row must
have
> an evaluate-able identifier, nulls are not permitted. Hence PK=UQ+NN
>
> Regards
> HJR
>
> "zeb" <thierry.constant1_at_free.fr> wrote in message
> news:3d3f0005$0$445$626a54ce_at_news.free.fr...
> > Hi,
> >
> > I have a DB ( 7.3.2. AIX )
> > for some tables, i have
> > the constraints:
> > NOT NUL, UNIQUE and PRIMARY KEY for the same column
> >
> > How is it possible ????
> >
> > If there a UNIQUE
> > when I try to put a PRIMARY KEY
> > I get:
> > ORA-02261 such unique or primary key already exists in the table
> >
> > and the same for the reverse
> >
> > How can you explain this situation ???
> >
> > Thanks in advance
> >
> >
> >
>
>
Received on Wed Jul 24 2002 - 22:09:54 CDT