Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jan 8, 1999

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 scratch
go
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME =
		'checktest')

-- INFORMATION_SCHEMA is a ver. 7 feature
-- use sysobjects in 6

drop table dbo.checktest
go
create 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 Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date