Not Null on Primary Key???

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... :)

PKzzz defualtly will have Not Null constraints....

I dont understand wat are you talking about.

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.

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

PK has to be NOT NULL.

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

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.

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

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

Quote:

---

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.

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.

Quote:

---

Originally posted by slimdave
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.

---

A deferrable PK also enforce that the set of columns are not null.

Quote:

---

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.

---

Deferrable pk benefits in cascading changes to pk and data loading.

Quote:

---

A deferrable PK also enforce that the set of columns are not null.

---

Not when it's deferred is doesn't

Quote:

---

Deferrable pk benefits in cascading changes to pk and data loading.

---

Changes to the PK? Heaven forbid.

Quote:

---

Originally posted by slimdave
Not when it's deferred is doesn't

---

try it.

Quote:

---

Changes to the PK? Heaven forbid.

---

Cascading changes are Rare but possible.

Quote:

---

try it.

---

SQL> create table my_table (my_column number
2 constraint xpkmy_table primary key deferrable);

Table created.

SQL> insert into my_table values(1);

1 row created.

SQL> set constraint xpkmy_table deferred;

Constraint set.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-01400: cannot insert NULL into ()

Ta Da!

Quote:

---

Cascading changes are Rare but possible.

---

... and symptomatic of bad design practices. I try to address my comments only to good design practices -- but you were right about data loading, that's a good example of where you might want to defer a PK.

Slimdave i have to agree your findings are quite interesting :)

Quote:

---

Originally posted by slimdave

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.

---

Hold on. If the MV is a fast refresh one, then the master table has a PK, right? Thne, how can you get duplicate values on the snapshot side?

An excellent question.

Maybe that MV stuff is a bad example -- lets stick with the data loading scenario instead.

Quote:

---

Originally posted by julian
Hold on. If the MV is a fast refresh one, then the master table has a PK, right? Thne, how can you get duplicate values on the snapshot side?

---

Good point but I guess this would hold true from 8i onwards. What if the MV is ROWID based and not PK based ?

Quote:

---

Originally posted by vinit
Good point but I guess this would hold true from 8i onwards. What if the MV is ROWID based and not PK based ?

---

What I would say if your MV is based on rowid? I would say that you are either using Oracle 7 (Prior to Oracle 8, snapshots were created using the ROWID from the master site but by default, Oracle8 will create snapshots USING PRIMARY KEY) or that if you run Oracle8i or 9i that you have improperly configured the replication :-)

Oracle strongly recommends not to create the snapshot logs based on the ROWID. There are several reasons for that.

I love these wandering threads. Lets talk about replication now.

Quote:

---

Originally posted by slimdave
SQL> create table my_table (my_column number
2 constraint xpkmy_table primary key deferrable);

Table created.

SQL> insert into my_table values(1);

1 row created.

SQL> set constraint xpkmy_table deferred;

Constraint set.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-01400: cannot insert NULL into ()

Ta Da!

---

SOHK: 2 OCP: 0

Perhaps we could award a new certification.

OCDP = Oracle Certified Documentation Peruser

Will you lot stop flirting..........

funny :D

Quote:

---

Originally posted by Horace
Will you lot stop flirting..........

---

Well, if that's what passes for flirting in Nottingham nowadays, it's a pretty sorry business. It were different in my day ...

Ah Dave,

But that was during the war, when all the boys were away........

... and on a Saturday night the world could be yours for the cost of a pair of nylons.

Mayday! Mayday!

We've drifted WAY off course here and the fuel is spent.

We're going down! Repeat, we are going down!

:D

- Chris

Quote:

---

Originally posted by slimdave
SQL> create table my_table (my_column number
2 constraint xpkmy_table primary key deferrable);

Table created.

SQL> insert into my_table values(1);

1 row created.

SQL> set constraint xpkmy_table deferred;

Constraint set.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (null);

1 row created.

SQL> insert into my_table values (1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-01400: cannot insert NULL into ()

Ta Da!

---

Quote:

---

Calvin: A deferrable PK also enforce that the set of columns are not null.

SlimDave: Not when it's deferred is doesn't

---

It ofcourse enforces the primary key aint it!!! atleast at the end of the transaction - which is because of the "Deferred" until the end of a Tx..
am I right here.. :)

Hmmmm..

The point that we were discussing was whether it was desirable to have both a primary key and a not null constraint on the same column(s), and the conclusion was that it wasn't.

The whole thing with the deferrable pk constraint was that there might be a circumstance in which you wanted the uniqueness of the pk to be deferred, but wanted to maintain the not null-ness, in which case it might be proper to place a separate not null constraint on the columns.

GEEZ
sometimes it has to be hammered into my brain to understand..
oh well its just a cuppa coffee thats needed now..
Thanks