Re: SQL*Plus - PK Question

> Sted, You bring up an interesting point. I have never considered
> this point having thought that since Oracle documentation has
> described the PK as being equivilent to a 'not null' constraint + a
> unique key constraint that Oracle explicitly created a 'not null'
> constraint on columns that make up a PK when the PK constarint was
> declared if one was not present. But the following SQL (ran on
> 8.1.7.2) shows Oracle does not do this as no such constraint appears
> in dba_constraints for the 'not null' when implicitly declared. So
> the question for someone with indept knowledge becomes is that any
> performance impact one way or the other.
>
> Personally I like the explict constraint as it ensures that the column
> will always have a value in the event the PK is disabled for
> maintenance of some type and not re-enabled, but if there is a
> performance impact to explicitly declaring the constraint I would
> reconsider this position. Also I like to determine the requirement
> that columns hold data independent of and prior to determining which
> columns will be the PK and will be indexed since this helps determine
> if the developers got the design right to being with.

Sorry, my English is not very good.

Did you try inserting a null value into the primary key without explicitly declaring a not null constraint (i.e. by just using a PK) ? UT1> @ora/obj/cons_tbl
Enter value for table_name: marktest

CONSTRAINT_NAME C SEARCH_CONDITION STATUS

------------------------------ - ------------------------------

MARKTEST_PK P
ENABLED Although the not null contraint is not listed when you do the above, the primary key may act as a not null + unique constraint as stated by oracle.

Firstly, im not sure why there is a need to disable a PK for maintenance. Secondly, I assume there may be a performance issue as explicitly declaring a not null contraint gives an overall "double-check" for the presence of null values. Received on Tue Apr 23 2002 - 09:54:40 CDT