Minimum length constraint on a column

Question

I'm trying to implement a minimum length constraint in Oracle.

As I read in this answer and multiple other similar questions I tried:

ALTER TABLE my_table 
ADD CONSTRAINT MY_TABLE_PASSWORD_CK CHECK (DATALENGTH(password) >=4)

And I am getting "DATALENGTH": invalid identifier". I also tried:

( DATALENGTH([password]) >=4 )
( LEN([password]) >=4 )
( LEN(password) >=4 )

What is the current format for this check constraint in Oracle?

BTW: don't store passwords in clear in a database. http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — Mat, Aug 22 '15 at 13:29
@Mat, Thank you for the suggestion, Its only a test-purpose database — Alexandru Severin, Aug 22 '15 at 13:30
For purposes of reference, the 11.1 SQL Reference Manual's section on SQL Functions [can be found here](http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#i88893). If you are using a different version of Oracle [you can find documentation for all versions here](https://docs.oracle.com/en/database/database.html). Best of luck. — Bob Jarvis - Слава Україні, Aug 22 '15 at 16:08

score 20 · Accepted Answer · answered Aug 22 '15 at 13:27

DATALENGTH() returns the length in bytes in SQL Server. The equivalent Oracle function is LENGTHB() (documented here):

ALTER TABLE my_table
    ADD CONSTRAINT MY_TABLE_PASSWORD_CK CHECK (LENGTHB(password) >= 4)

However, for your purposes, I think the string length would be appropriate in both databases, LENGTH() in Oracle (or LEN() in SQL Server).

1 Answers1