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
 
adding constraint after creating the table(altering a table)

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

New User


Joined: 21 Nov 2005
Posts: 8

PostPosted: Thu Dec 29, 2005 12:41 pm    Post subject: adding constraint after creating the table(altering a table)
Reply with quote

ALTER TABLE EMPLOYEE ADD CONSTRAINT CON_DEPT
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);

when i am using this i am facing some problem and not able to alter the table .con u figure what wrong with the above command
Back to top
View user's profile Send private message

program developer

New User


Joined: 15 Nov 2005
Posts: 3

PostPosted: Thu Dec 29, 2005 12:54 pm    Post subject: Re: adding constraint after creating the table(altering a ta
Reply with quote

whats the error you are getting?
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Thu Dec 29, 2005 2:35 pm    Post subject: Constraint help
Reply with quote

Hi ramesh,

Quote:
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT404I SQLCODE = 162, WARNING: TABLE SPACE DBTCHN03.TS001 HAS BEEN PLACED IN CHECK PENDING DSNT418I SQLSTATE = 01514 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXICRC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 20 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000014' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION


U have came across the above error only, it is because

The indicated table space is in the CHECK PENDING state because ALTER TABLE was used to specify a referential constraint on a populated table. The table space is not generally available until the CHECK PENDING state is removed from the table space.

Check for the valid data in that and do again
Back to top
View user's profile Send private message
jajularamesh
Currently Banned

New User


Joined: 21 Nov 2005
Posts: 8

PostPosted: Thu Dec 29, 2005 3:00 pm    Post subject:
Reply with quote

ALTER TABLE EMPLOYEE2 ADD CONSTRAINT CON_DEP FOREIGN KEY (DEPTNO) REFERENCES DEPT2(DEPTNO);
------+---------+---------+---------+---------+---------+---------+---------+
T404I SQLCODE = 541, WARNING: THE REFERENTIAL OR UNIQUE CONSTRAINT CON_DEP
HAS BEEN IGNORED BECAUSE IT IS A DUPLICATE
T418I SQLSTATE = 01543 SQLSTATE RETURN CODE
T415I SQLERRP = DSNXICKK SQL PROCEDURE DETECTING ERROR
T416I SQLERRD = 290 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
T416I SQLERRD = X'00000122' X'00000000' X'00000000' X'FFFFFFFF'
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Thu Dec 29, 2005 3:03 pm    Post subject: SQL Error 541
Reply with quote

Hi ramesh,
It is due to A FOREIGN KEY clause uses the same key and parent table as another FOREIGN KEY clause, or a UNIQUE clause uses the same column list as another UNIQUE clause. In either case, the duplicate clause is ignored. 'name' is either the foreign key name or the name of the first column in the constraint that is ignored.

So if the duplication is an error, correct the statement and execute it again.
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

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