Re: SQL*Plus - PK Question

"Martin Haltmayer" <Martin.Haltmayer_at_d2mail.de> wrote in message news:3CD0E07D.2A9D58F2_at_d2mail.de...
> 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.
>

Yeah, I know what you were trying to show. I'm afraid I don't get the distinction between a "table constraint" and a "column constraint" though.

Try doing an 'alter table emp add (constraint blah primary key); and it will fail miserably: your "table constraint" needs to know precisely which columns are the primary key.

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

But the optimizer can deduce, for example, that an index is usable to enforce primary keyness, even if it is a non-unique index (deferrable constraints and all that jazz). So where is the evidence for your assertion that it can't work out not nullness (I'm not saying it can or it can't... I just would like to know how you came to draw this conclusion).

Regards
HJR
>
> 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 - 02:54:09 CDT