This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
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 OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 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: 2238

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: 20132

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
I agree. Here's the link:
subject: Column Constraint
It's not a secret anymore!