?
Solved

using CURRENT_TIMESTAMP in a [CHECK] constraint

Posted on 2004-11-24
12
Medium Priority
?
1,763 Views
Last Modified: 2010-05-19
DB2 is issuing errors each time I try to create a table with a column defined as follows:

myDate    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP CHECK ( myDate < CURRENT_TIMESTAMP),

DB2 says: "SQL0548N  A check constraint that is defined with "CURRENT_TIMESTAMP" is
invalid.  SQLSTATE=42621"

I would like to make sure the user enters a time and date which is before the current time and date.
Please help
0
Comment
Question by:VbMonk
  • 4
  • 3
  • 2
9 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford earned 100 total points
ID: 12667108
The following works for me:

create table deleteme3 (
  myTS timestamp            
);

> Table DELETEME3 created

alter table deleteme3
add constraint cc1
check (myTS < current timestamp);

> ALTER completed for table DELETEME3

insert into deleteme3      
values(current timestamp - 1 day);

> 1 rows inserted in DELETME3

insert into deleteme3      
values(current timestamp + 1 day);

> INSERT or UPDATE not allowed by CHECK constraint
0
 

Author Comment

by:VbMonk
ID: 12667302
Thanks daveslash. While I'm checking your solution, can you answer this other bit. I cannot check the length of a char data type. It should be 6 chars long but, an insert statement accepts one with 3 chars or any along as it is below 6. How can i resolve that?
code   CHAR(6) NOT NULL
CHECK (LENGTH(code) > 5 AND LENGTH(code) < 7)
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 12667427
davelash's solution is to use Current Timestamp without the underscore?

the length of code will always be 6 since its a fixed length column

you'd need
code varchar(6) not null

to have varying lengths...

do you mean

check(length(strip(code,b,' '))=6)

ie using strip to remove any leading or trailing spaces...
 
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:VbMonk
ID: 12667507
LOWFATSPread, the system accepts a string with less than 4 characters. It should be 4 chars long only. How can I make it accept 4 chars only. I tested with 3 chars and it accepted (which is something I do not want)
0
 

Author Comment

by:VbMonk
ID: 12667527
I can't test your suggestions guys, the table cannot be dropped. When i tried to drop the table to implement daveslash's solution it gave this error:
"DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0901N  The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed.  (Reason "bad Defaul".)  
SQLSTATE=58004"
How can i drop the table?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12668125
you don't need to drop the table

you just need to
use ALTER TABLE
to first drop the existing check constraint on the code

Alter table Tablename drop check constraint constraintname;

then

alter table tablename add check constarint constraintname length(strip(code,b,' '))=6;




   
0
 

Author Comment

by:VbMonk
ID: 12668354
i think DB2 does not accept all the statements you are suggesting. I'm getting errors. I don't know the syntax of the statements. If you can help by writing the specific statements (which can recognised by DB2) instead of these generic ones.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12668719
ok which version of DB2/UDB are you using and which operating system?

sorry my example used strip wihich is a DB2 UDB function on OS/390 v7(?) and above

please specify the table name
and the names your using for any existing constraints...

ok ..
These should work for most DB2 UDBs

you havn't got the time constraint on the table already? so add one with

ALTER TABLE PutYourTableNameHere  ADD CONSTRAINT CK_TIMESTAMP CHECK ( myDate < CURRENT TIMESTAMP);

ALTER TABLE PutYourTableNameHere  ADD CONSTRAINT CK_CODE  CHECK (LENGTH(LTRIM(RTRIM(code)))=6);

i wasn't total clear on wether you wanted a 4 or 6 character code...
but if you don't want to allow spaces in the codes then you should probably have it defined as
a foreign key relationship to a reference table and validate it that way...
hth
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 12669398
> davelash's solution is to use Current Timestamp without the underscore?

In DB2 UDB for iSeries, you can specify Current_Timestamp with or without the underscore. They work equivalently.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question