devxlogo

Check contraints

Check contraints

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')

devx-admin

Share the Post: