DB2 - Unique Constraint

abiyn · New User Joined: 25 Mar 2008 Posts: 12 Location: Chennai

I want to create below two tables

EMP (Parent table)
----

Emp ID - Primary Key
Ename
Project ID

PRO (Child table)
----
Project ID
PName
Manager ID - Foregin Key

I have created the first table. But when I tried to create 2nd table with unique constraint on Project ID, its throwing the below error message

---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -542, ERROR: PID CANNOT BE A COLUMN OF A PRIMARY KEY, A
UNIQUE CONSTRAINT, OR A PARENT KEY BECAUSE IT CAN CONTAIN NULL VALUES
DSNT418I SQLSTATE = 42831 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXIAB5 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 10 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'0000000A' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

Could you please provide the solution to define Project ID as unique.

Thanks In Advance

Regards
Abi

enrico-sorichetti · Posted: Wed Mar 26, 2008 3:33 pm

the explanation is right in front of You

Gnanas N · Posted: Wed Mar 26, 2008 3:34 pm

Could you please show CREATE statments of PRO table?

abiyn · New User Joined: 25 Mar 2008 Posts: 12 Location: Chennai

Gnanas,

Below this the code which i used to create 2 tables. I want PID in table PRO to be unique. How can i do that?

CREATE TABLE EMP(ID INT NOT NULL,NAME VARCHAR(10),PID INT,DES CHAR(5),
PRIMARY KEY(ID));

CREATE UNIQUE INDEX EMPINX ON EMP(ID);

CREATE TABLE PRO(PID INT NOT NULL,PNAME VARCHAR(10),
MID INT,
FOREIGN KEY(MID) REFERENCES EMP(ID))
;

ALTER TABLE PRO ADD CONSTRAINT PIDU UNIQUE(PID);

Gnanas N · Posted: Wed Mar 26, 2008 3:57 pm

Please give this,

abiyn · New User Joined: 25 Mar 2008 Posts: 12 Location: Chennai

Gnanas,

I got the result.. Thanks a lot