Not Null on Primary Key???

[marist89]

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

[slimdave]

Perhaps we could award a new certification.

OCDP = Oracle Certified Documentation Peruser

[Horace]

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

[pando]

funny

[slimdave]

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

[Horace]

Ah Dave,

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

[slimdave]

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

[chrisrlong]

Mayday! Mayday!

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

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



- Chris

[dharma]

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!

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

[slimdave]

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.