This week's book giveaway is in the Design forum.
We're giving away four copies of Building Microservices and have Sam Newman 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 Building Microservices this week in the Design 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: 2206

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

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’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: Column Constraint
jQuery in Action, 3rd edition