Re: SQL*Plus - PK Question

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.

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

1 row created.

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

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 - 12:02:43 CDT