Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us