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
 
Create Table - Validate row value

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

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Fri Sep 21, 2007 1:51 pm    Post subject: Create Table - Validate row value
Reply with quote

Hi All

I want to know if there is any option while creating Table such that, a Particular Column accepts only Valid value declared at time of Table creation .

Eg :
Assume That Table SAMPLE has 2 fields

Code:
Col-1(smallint)   Col-2(Char(1))
2                        Y
3                        Y
4                        N
5                        Y 



During Table Create I wish to put a condition that Col-2 should have either 'Y' or 'N' as valid row values ONLY.
If any value other than 'Y' or 'N' is Tried to inserted/UPdated it should give ERROR.

Code:
Col-1(smallint)   Col-2(Char(1))
2                        Y
3                        Y
4                        N
5                        T  THIS IS ERROR CONDITION 


T should not be inserted/Updated ..



If anyone has any idea to create Table which have row validation at run time, then please let me know.
Back to top
View user's profile Send private message

saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Fri Sep 21, 2007 2:40 pm    Post subject:
Reply with quote

Hi Snehal,

Create your table with this DDL.

Code:
CREATE TABLE SAMPLE
(COL1 SMALLINT NOT NULL,
 COL2 CHAR(1) CONSTRAINT COL2CHK CHECK
                      (COL2 = 'Y' OR COL2='N'),
PRIMARY KEY (COL1))
IN DATABASE.TABLESPACE
CCSID EBCDIC;

Thanks
Sai
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Fri Sep 21, 2007 4:16 pm    Post subject:
Reply with quote

saiprasadh wrote:
Hi Snehal,

Create your table with this DDL.

Code:
CREATE TABLE SAMPLE
(COL1 SMALLINT NOT NULL,
 COL2 CHAR(1) CONSTRAINT COL2  CHECK
                      (COL2 = 'Y' OR COL2='N'),
PRIMARY KEY (COL1))
IN DATABASE.TABLESPACE
CCSID EBCDIC;

Thanks
Sai


Thanks a Lot for solution .. This DDL worked fine !!!!!
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Fri Sep 21, 2007 5:39 pm    Post subject:
Reply with quote

Hi All

I would also like to mention that, for such Tables having row value data constraints I have found that we can use below query to find out the constraint

SELECT CHECKNAME, CHECKCONDITION
FROM SYSIBM.SYSCHECKS
WHERE TBNAME = 'Table name'
AND CREATOR = 'Qualifier ID ';

For my Above Query it gave below result
CHECKNAME CHECKCONDITION
---------+---------+---------+---------+---------+--------
COL2 COL2 = 'Y' OR COL2='N'


Hope this helps
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 Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm

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