File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Column Constraint Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Elasticsearch in Action this week in the Big Data forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Column Constraint" Watch "Column Constraint" New topic

Column Constraint

John Culling

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

Joined: Mar 01, 2009
Posts: 1996

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

Joined: Oct 27, 2005
Posts: 19911

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

How To Ask Questions How To Answer Questions
Don't get me started about those stupid light bulbs.
subject: Column Constraint