Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 - Unique Constraint

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
abiyn

New User


Joined: 25 Mar 2008
Posts: 12
Location: Chennai

PostPosted: Wed Mar 26, 2008 3:30 pm    Post subject: DB2 - Unique Constraint
Reply with quote

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
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10326
Location: italy

PostPosted: Wed Mar 26, 2008 3:33 pm    Post subject: Reply to: DB2 - Unique Constraint
Reply with quote

the explanation is right in front of You

Quote:
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
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Wed Mar 26, 2008 3:34 pm    Post subject:
Reply with quote

Could you please show CREATE statments of PRO table?
Back to top
View user's profile Send private message
abiyn

New User


Joined: 25 Mar 2008
Posts: 12
Location: Chennai

PostPosted: Wed Mar 26, 2008 3:42 pm    Post subject:
Reply with quote

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);
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 788
Location: Chennai, India

PostPosted: Wed Mar 26, 2008 3:57 pm    Post subject:
Reply with quote

Please give this,
Code:
CREATE UNIQUE INDEX PROINX ON PRO(PID);

Instead of ALTER...
Back to top
View user's profile Send private message
abiyn

New User


Joined: 25 Mar 2008
Posts: 12
Location: Chennai

PostPosted: Wed Mar 26, 2008 4:05 pm    Post subject:
Reply with quote

Gnanas,

I got the result.. Thanks a lot
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Unique sort situation.... dbecker SYNCSORT 6 Wed Apr 27, 2016 8:53 pm
No new posts Unique Record Count anandhan87 DFSORT/ICETOOL 1 Tue Feb 09, 2016 4:06 pm
No new posts unique 8 character token Pedro CLIST & REXX 9 Fri May 08, 2015 10:59 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us