Re: SQL*Plus - PK Question

I just wanted to show that the general rule "primary key" = "unique + not null" is not true because the primary key constraint is a table constraint whereas the not null is a column constraint.

This has another consequence: the optimizer can evaluate the fact that a join column is not null. So if a column is logically not null declare it as such even if it were deductible from the primary key (because the optimizer cannot deduce it from the *table* constraints).

Martin

"Howard J. Rogers" wrote:
>
> "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 Thu May 02 2002 - 01:45:17 CDT