Re: compound primary key with null columns... is it possible?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 20 Dec 2002 07:36:58 +1100
Message-ID: <vYpM9.6578$jM5.18835@newsfeeds.bigpond.com>

Here's a moderately interesting one:

SQL> connect scott/tiger
Connected.
SQL> create table primtest (
  2 col1 number,
  3 col2 number,
  4 col3 number);
Table created.

SQL> alter table primtest add constraint primtest_pk primary key (col1, col2, col3);
Table altered.

SQL> alter table primtest modify col2 null; Table altered.

[Crikey!! This isn't the 'error message' Niall got, nor what I expected!!!]

SQL> desc primtest

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 COL1                                      NOT NULL NUMBER
 COL2                                      NOT NULL NUMBER
 COL3                                      NOT NULL NUMBER

[Ah. I see the command has been ignored anyway. And as proof...]

SQL> insert into primtest (col1, col3) values (1,3); insert into primtest (col1, col3) values (1,3) *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."PRIMTEST"."COL2")

[But just out of interest, we'll try one more time...]

SQL> alter table primtest modify col2 null; alter table primtest modify col2 null

                            *

ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL

[So now the exact same command as before produces the error Niall reported. Which is different from when I issued it earlier. Curiouser and curioser. Windows XP, 9.2. A bug, of sorts, I guess].

Regards
HJR "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3e01bb1e$0$246$ed9e5944_at_reading.news.pipex.net...
> "Giovanni Azua" <bravegag_at_hotmail.com> wrote in message
> news:atsbn8$25ar5$1_at_ID-114658.news.dfncis.de...
> > The problem is that the two new columns could likely
> > have null values and I was aware of the capability of having
> > nullable columns inside unique indexes, is it possible with
> > primary keys as a special case?

>

> No

>
[snip] Received on Thu Dec 19 2002 - 14:36:58 CST