Re: SQL*Plus - PK Question

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 1 May 2002 09:44:34 +1000
Message-ID: <a2Gz8.35$su6.212@news.oracle.com>

"Martin Haltmayer" <Martin.Haltmayer_at_d2mail.de> wrote in message news:3CCECE33.A2959526_at_d2mail.de...
> Primary key is a bit different than unique + not null. Please see the
following
> example in 8.1.7.2.1 (W2000):
>
> SQL> create table nulltest (n number, a varchar2 (5));
>
> Table created.
>
> Elapsed: 00:00:00.60
> SQL> alter table nulltest
> 2 add constraint nulltest_pk primary key (n) deferrable initially
deferred;
>
> Table altered.

You have created the primary key as a deferrable constraint...

>
> Elapsed: 00:00:00.70
> SQL>
> SQL> insert into nulltest (n, a) values (null, 'abc');
>
> 1 row created.
>

...Therefore, the insert of a null value into the primary key column is permitted.

> Elapsed: 00:00:00.41
> SQL> rollback;
>
> Rollback complete.
>
> Elapsed: 00:00:00.40
> SQL>
> SQL> insert into nulltest (n, a) values (null, 'abc');
>
> 1 row created.
>
> Elapsed: 00:00:00.40
> SQL> commit;
> commit
> *
> ERROR at line 1:
> ORA-02091: transaction rolled back
> ORA-01400: cannot insert NULL into ("SCOTT"."NULLTEST"."N")
>

Perfectly legitimate behaviour. Although a deferrable constraint permits the insertion of violating records, you are not permitted to house them permanently in the table, so the commit fails.

Quite what this demonstration of deferrable constraint behaviour has to do with proving a difference between primary keyness and (not null+unique)ness, I have no idea. But try this anyway:

SQL> create table unn1 (
  2 col1 char(5) constraint unn1_col1_uq unique deferrable initially immediate);
Table created.

SQL> create table pk1 (
  2 col1 char(5) constraint pk1_col1 primary key deferrable initially immediate);
Table created.

---

So both tables have their constraints declared as immediate ones (which happens to be the default, anyway)

---

SQL> alter table unn1 modify col1 not null;

Table altered.

---

So now the first table has a unique constraint AND a not null constraint

---

SQL> insert into pk1 values (null);
insert into pk1 values (null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."PK1"."COL1")

SQL> insert into unn1 values (null);
insert into unn1 values (null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."UNN1"."COL1")

---

So now it transpires that nulls are not permitted in either table. I fail to see the difference in behaviour between the two sorts of constraint that you suggest should be evident.

Regards
HJR
> The first insert worked although I could not commit (as the second insert
> shows). If you have a not null constraint explicitly this is a column
constraint
> whereas primary key is a table constraint. As a consequence, the optimizer
can
> take advantage of the not null in joins and where conditions.
>
> Martin
>
>
>
> Sted Alana wrote:
> >
> > 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.
Received on Tue Apr 30 2002 - 18:44:34 CDT