Not Null on Primary Key???

[dpeacock]

I'm designing a new physical data model using a third party tool. When I generate the database creation script automatically it puts 'not null' constraints on the primary keys. I don't have much experience with this particular tool and can't find the correct setting to stop this.

The extra 'not null' constraints are redundant but what would be the problem if I just leave them? For example, would the 'not null' condition be checked twice for every insert or update?

Any ideas? I could always just edit the script...

[abhaysk]

PKzzz defualtly will have Not Null constraints....

I dont understand wat are you talking about.

[slimdave]

if a not null constraint is being created in addition to the primary key, then you can drop it. Yes, it will be checking not null twice.

[irehman]

Just like Erwin, when you create a PK on a column it creates a NOT NULL.

PK has to be NOT NULL.

[slimdave]

yes, but not null is inherent in the PK constraint -- it shold not be created seperately

[adewri]

You cannot alter table column to not null or null if that column is a PRIMARY key. It is by default a not null if it has a primary key constraint on it.

[slimdave]

yes you can ...

SQL> create table my_table (my_column number primary key);

Table created.

SQL> desc my_table
Name Null? Type
----------------------------------------- -------- ----------------------

MY_COLUMN NOT NULL NUMBER

SQL> alter table my_table modify (my_column not null);

Table altered.
SQL> alter table my_table modify (my_column null);

Table altered.

SQL> alter table my_table modify (my_column null);
alter table my_table modify (my_column null)
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL> desc my_table
Name Null? Type
----------------------------------------- -------- -------------------

MY_COLUMN NOT NULL NUMBER


Note that it is the second modify to not null that fails, but not because o the PK definition ...

SQL> drop table my_table;

Table dropped.

SQL> create table my_table (my_column number null);

Table created.

SQL> alter table my_table modify (my_column null);
alter table my_table modify (my_column null)
*
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

[dpeacock]

Example:

create table TBL_PERSONWORK (
PERSONID NUMBER(9) not null,
LIMIT NUMBER(3),
PEREVERY NUMBER(3),
DAP NUMBER(5),
HAZARD NUMBER(1),
constraint PK_PERSONWORK primary key (PERSONID)
using index
tablespace INDX
);

So the 'not null' on PERSONID adds an extra not null constraint when this is already covered by the PK constraint?

Slimdave - Thanks for your help.

Cheers

[slimdave]

So the 'not null' on PERSONID adds an extra not null constraint when this is already covered by the PK constraint?

Correct, it does, and this will be an overhead on insert.

SQL> create table my_table (my_column number not null primary key);

Table created.

SQL> select constraint_type from user_constraints
2 where table_name = 'MY_TABLE';

C
-
C
P

SQL> alter table my_table modify (my_column null);

Table altered.

SQL> select constraint_type from user_constraints
2 where table_name = 'MY_TABLE';

C
-
P

SQL> desc my_table;
Name Null? Type
----------------------------------------- -------- --------------------

MY_COLUMN NOT NULL NUMBER


Your tool may have a configuration setting to prevent such an unfortunate double-constraint.

[slimdave]

I just thought of a reason why a designer might want to keep the not null on there actually.

Suppose you were defining a table for which the PK had to be deferrable -- you might want the "unique" part of the PK to be deferrable, but keep the "not null" part enforced. In such a case you would have to keep a seperate not null constraint on the table as well as the deferrable PK.

Probably not a very common case, I would think. The only situation i can think of for having a deferrable pk would be on a fast-refresh materialized view -- i believe that deferrable pk's are a benefit there because Oracle does not guarantee uniqueness on a PK or Unique column list while the refresh is taking place.