|
View previous topic :: View next topic
|
| Author |
Message |
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
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 |
|
 |
saiprasadh
Active User
Joined: 20 Sep 2006
Posts: 154
Location: US
|
|
|
|
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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| 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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
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 |
|
|
|
|
