Re: Concatenated Primary Key

Mark,

     You may want to build your table differently:

CREATE TABLE junk
 (x VARCHAR2(10) CONSTRAINT junk_x_nn NOT NULL,   y VARCHAR2(10),
  CONSTRAINT junk_xy_uk UNIQUE (x, y));

SQL> INSERT INTO junk VALUES('1','A');
1 row created.

SQL> INSERT INTO junk VALUES('1', 'B'); 1 row created.

SQL> INSERT INTO junk VALUES('1', null); 1 row created.

SQL> INSERT INTO junk VALUES(null, 'A'); INSERT INTO junk VALUES(null, 'A')

            *
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

     But if you want to disable a NULL constraint on an existing table, I believe you have to drop the primary key and then modify your table with your new constraints:

SQL> ALTER TABLE junk
  2 DROP PRIMARY KEY;
Table altered.

SQL> ALTER TABLE junk
  2 ADD CONSTRAINT junk_xy_uk UNIQUE (x, y); Table altered.

SQL> ALTER TABLE junk
  2 MODIFY x NOT NULL;
Table altered.

Jay!!!

Mark S. Reichman wrote:
>
> In the book "Building Intelligent Databases with Oracle PL/SQL
> Triggers and Procedures" the author on page 59 says the
> individual columns of a concatenated primary key can be null as long
> as at least one of the columns is not null.
> I have found that when a concatenated primary key is created Oracle
> automatically assigns "not null" to each column.
> When I do the following I get..
>
> SQL> CREATE TABLE JUNK(X VARCHAR2(10),Y VARCHAR2(10));
>
> Table created.
>
> SQL> ALTER TABLE JUNK ADD CONSTRAINT PK_JUNK PRIMARY KEY (X,Y);
>
> Table altered.
>
> SQL> DESC JUNK
> Name Null? Type
> ------------------------------- -------- ----
> X NOT NULL VARCHAR2(10)
> Y NOT NULL VARCHAR2(10)
>
> SQL> INSERT INTO JUNK VALUES('1','A');
>
> 1 row created.
>
> SQL> INSERT INTO JUNK VALUES('1',NULL);
> INSERT INTO JUNK VALUES('1',NULL)
> *
> ERROR at line 1:
> ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
>
> I would like to have some of these columns null. How do I remove
> the "NOT NULL" constraint so this works like the book says...
>
> -Mark
Received on Mon Jul 27 1998 - 12:13:44 CDT