SQL中给varchar2(5)数据类型合适的约束

数据库课，开始的部分讲sql，讲到create table和约束，在虎吧上给他们留了一道思考题，是从那本著名的SQL Puzzles and Answers里找的一道谜题，在原书上的解答其实是有错误的

请建立一新表，包含三个列，全部都是varchar2(5)数据类型要求给这个表上加上合适的约束，使第一个列只能输入“不含字母的字符串”（就是全部由数字或特殊符号等组成），第二个列只能输入“包含字母的字符串”，第三个列只能输入“全部是字母的字符串”

写出相关的create table语句

结果很快有一同学就解答出来了，并且把原书的错误也纠正了

随后给出了问题的答案：

这是他给的原始答案

create table x (a varchar2(5) check (upper(a)=a and lower(a)=a), 
b varchar2(5) check (not upper(b)=b or not lower(b)=b), 
c varchar2(5) check (length(translate(c,' 
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))=0));

这个答案还存在一些瑕疵

1每个列上还要给not null约束，他后来自己也很快发现这个问题并且指出纠正。

2第一个check其实可以简化为upper(a)=lower(a)，第二个check可以简化为upper(b)<>lower(b)

3第三个check使用的translate函数是神来之笔，原书上这个地方是错的。但translate的通用性不好，最好有通用性更好的办法

对于老虎的这个答案，显然还存在很多问题。根据第三个问题的答案分析，老虎这里的字母指的是英文字母，否则第三个问题的答案就是不完整的。

那么下面就按照英文字母来理解题中所提到的字母，当然字母代表英文字母也是绝大部分情况下正常的理解。

其实第一题和第二题的答案思路是一样的，通过UPPER和LOWER函数是否相等，来判断一个字符是否英文字符。这种方法是很不严谨的：

SQL> SELECT UPPER(CHR(42660)), LOWER(CHR(42660)) FROM DUAL;
UP LO
-- --
Δ δ

这是一个最简单的例子，要想知道不满足这个条件的结果有多少，可以执行下面的PL/SQL代码：

SQL> SET SERVEROUT ON SIZE 1000000
SQL> BEGINITPUB
  2   FOR I IN 1..65535 LOOP
  3    IF UPPER(CHR(I)) != LOWER(CHR(I)) AND LENGTH(CHR(I)) = 1 
  4     DBMS_OUTPUT.PUT_LINE(I || ':' || CHR(I));ITPUB个人空间t F[ Z+D;Y
  5    END IF;
  6   END LOOP;
  7  END;
  8  /

当然这里面大部分的结果是一个英文字母加上一个无法显示的字符，但是既然可能存在这种现象，作为标准答案就可以考虑。何况，还有上面例子给出的希腊字符在内的多种国家的字母。

凭经验或主观判断利用UPPER和LOWER来判断是否是英文字符的方法存在很多的不确定性，除非像上面一样验证过所有的字符UPPER和LOWER的输出结果。

其实对于这种问题，可以利用LTRIM、REPLACE、TRANSLATE等函数来判断，当然对于高版本也可以使用正则表达式来进行判断。所有上面这些方法的共同点是在函数中明确指出了所有的可能性，比如利用LTRIM的方法：LTRIM(UPPER(C), ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’) IS NULL。

【编辑推荐】