Question:
I have a primary key (client id) of five characters which contains:
character # 1: A-Z character # 2: 0-9 character # 3: 1-9 character # 4: blank or a-z character # 5: blank or a-z
There’s no problem with the first three characters. Char 4 can be blank or a-z. Char 5 can be blank or a-z but cannot contain a-z if character 4 is blank. Do you have any suggestions?
Answer:
A check constraint can be easily coded to your specifications using the LIKE keyword. Note that for comparison purposes, trailing blanks in a character field are not used. So “ABC” is equal to “ABC “
Here’s the code and the test data I used. The third insert causes an error.
use scratchgoif exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'checktest')-- INFORMATION_SCHEMA is a ver. 7 feature-- use sysobjects in 6drop table dbo.checktestgocreate table dbo.checktest( sec_code char(5), constraint chk_valid_code check (sec_code like '[A-Z][0-9][1-9]' or sec_code like '[A-Z][0-9][1-9][a-z][a-z]') )go insert into checktest values('A01')insert into checktest values('A01az')insert into checktest values('A01 z')
