Not Null on Primary Key???

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.