File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Column Constraint Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Column Constraint" Watch "Column Constraint" New topic
Author

Column Constraint

John Culling
Greenhorn

Joined: Aug 19, 2012
Posts: 1
I am trying to create a column constraint in Apache Derby to check that the input data is in the form of three uppercase alpha characters plus a three digit number e.g. ABC100 or XYZ999.
I have the following, which works in MS Server

CONSTRAINT valid_consultant_id
CHECK ((ASCII(SUBSTRING(consultant_id,1,1)))BETWEEN 65 AND 90 AND
(ASCII(SUBSTRING(consultant_id,2,1)))BETWEEN 65 AND 90 AND
(ASCII(SUBSTRING(consultant_id,3,1)))BETWEEN 65 AND 90 AND
(CAST(SUBSTRING(consultant_id,4,3)as smallint)BETWEEN 100 AND 999))

however, Apache Derby does not seem to recognise the ASCII keyword.

I know I could do
CHECK (SUBSTR(consultant_id,1,1)) IN ('A','B','C'......................'Z') AND..............

but that seems very clumsy.

Can someone point me in the direction of a more elegant solution please
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1649
    
  14

Can't help with Apache Derby, but as you're having to look at platform-specific options anyway, could you use a regular expression instead?

No more Blub for me, thank you, Vicar.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19670
    
  18

Perhaps the following will work:
Unfortunately, that probably ignores the case so you'd have to figure out how to fix that issue.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Column Constraint