| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Plus - PK Question
"Sted Alana" <Sted_Alana_at_hotmail.com> wrote in message news:<3cc506ff_1_at_news.iprimus.com.au>...
> I have come across many examples in sql*plus with regards to using 'not
> nulls' in conjunction with primarys keys. This bothers me because from my
> reading when a primary key is defined for an attributes(s) of a table, it
> assumes that those values cannot contain null values, but yet i see this:
>
> create table Example
> (
> sid char(5) not null,
> ....
> constraint pk_sid primary key (sid)
> );
>
> why need to use not null when primary key implicitly imposes that contraint?
>
> Any help appreciated.
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.
UT1> desc marktest
Name Null? Type ----------------------------------------- -------- ---------------------------- FLD1 VARCHAR2(10) FLD2 NUMBER FLD3 DATE
UT1> alter table marktest add constraint marktest_pk primary key (fld1);
Table altered.
UT1> desc marktest
Name Null? Type ----------------------------------------- -------- ---------------------------- FLD1 NOT NULL VARCHAR2(10) FLD2 NUMBER FLD3 DATE
UT1> @ora/obj/cons_tbl
Enter value for table_name: marktest
CONSTRAINT_NAME C SEARCH_CONDITION STATUS ------------------------------ - ------------------------------ -------- MARKTEST_PK PENABLED Declaring the constraint explicitly places another constraint into dba_constraints. Drop PK and start over:
UT1> desc marktest
Name Null? Type ----------------------------------------- -------- ---------------------------- FLD1 VARCHAR2(10) FLD2 NUMBER FLD3 DATE
UT1> alter table marktest modify (fld1 varchar2(10) not null);
Table altered.
UT1> alter table marktest add constraint marktest_pk primary key (fld1);
Table altered.
UT1> @ora/obj/cons_tbl
Enter value for table_name: marktest
CONSTRAINT_NAME C SEARCH_CONDITION STATUS ------------------------------ - ------------------------------ -------- SYS_C007445 C "FLD1" IS NOT NULL ENABLED MARKTEST_PK PENABLED 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.
 |
 |