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等函數來判斷，當然對於高版本也可以使用Regex來進行判斷。所有上面這些方法的共同點是在函數中明確指出了所有的可能性，比如利用LTRIM的方法：LTRIM(UPPER(C), ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’) IS NULL。

1. 在SQL Server中建立全域暫存資料表技巧
2. 並行查詢讓SQL Server加速運行
3. SQL Server 2005常見問題淺析