Constraints to the Max! I ran across this question today on the Oracle-l list:

Hi List,

I have read the following but I am looking for a way to create a
case-insensitive database. Is it even possible?

http://askanantha.blogspot.com/2007/07/making-oracle-case-insensitive.html

Thanks,

Roger Xu

Later in the thread, Niall Litchfield, replied with the following:

Assuming that you mean you want to make all string data case insensitive and that the requirement has come from developers who don't want to check their inputs for case errors (though I bet they want to say business logic is an application function) then a check constraint on each column that the inserted/updated value is equal to its uppercase representation is a start. Then they'll have to either check for the constraint failing or start discussions with you about database input validation aka constraints.

That gave me an idea.

CREATE TABLE t
(
  x VARCHAR2(30)
    CONSTRAINT pk_x PRIMARY KEY
    CONSTRAINT ck_upper_x_t CHECK ( x = UPPER( x ) )
);

What does that do? It insures that the value put in X is always uppercase.

PARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'home' );
INSERT INTO t ( x ) VALUES ( 'home' )
*
ERROR at line 1:
ORA-02290: check constraint (PARTY.CK_UPPER_X_T) violated

PARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'HOME' );

1 row created.

Cool. Now the Application Developers have to deal with it. No wiggle room there.

How far can you go?
Let's see.

DROP TABLE t PURGE;

CREATE TABLE t
(
  x VARCHAR2(30)
    CONSTRAINT pk_x PRIMARY KEY
    CONSTRAINT ck_upper_x_t CHECK ( x = UPPER( x ) )
    CONSTRAINT ck_nospaces_x_t CHECK ( INSTR( x, ' ' ) = 0 )
    CONSTRAINT ck_charonly_x_t CHECK ( REGEXP_INSTR( x, '[[:digit:]]' ) = 0 )
);

I'm sure I could go on and on...but it's kind of fun.

ARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'HOME SCHOOL' );
INSERT INTO t ( x ) VALUES ( 'HOME SCHOOL' )
*
ERROR at line 1:
ORA-02290: check constraint (PARTY.CK_NOSPACES_X_T) violated

No spaces allowed.

PARTY@TESTING>INSERT INTO t ( x ) VALUES ( '12345' );
INSERT INTO t ( x ) VALUES ( '12345' )
*
ERROR at line 1:
ORA-02290: check constraint (PARTY.CK_CHARONLY_X_T) violated

No digits allowed!

Why?
Constraints are awesome. The very definition of constraints is awesome. Some of the best ideas come to you when you are constrained by something, usually time. OK, maybe not time in software development. Time in writing I've heard is pretty cool. Limit the colors an artist has available and see what they come up with. Less choices sometimes means better.

Typically I'll constrain the crap out of a data model. With development and testing, some of those will be relaxed. Some will be added.

The point is, don't be afraid of them. Constraints are a very good thing indeed.

Labels: constraints, database, development, oracle

  ¶ 7/21/2009 12:53:00 AM