Re: check constraint**
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 03/08/04
- Next message: Tibor Karaszi: "Re: OUTPUT var in SP"
- Previous message: Uri Dimant: "Re: uniqueidentifier***"
- In reply to: RM: "check constraint**"
- Next in thread: RM: "Re: check constraint**"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 8 Mar 2004 15:17:51 +0530
hi RM,
The check constraint that you have defined needs to have mininum of 9
characters.In addition to this, you are doing positional comparison of the
string that you are going to insert into emp_id column. For example
following check constraint
emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
will be evaluate as:
1 character of the emp_id must be in the range of A-Z, 2nd and 3rd character
follows the same rule. 4th character should have value between 1-9, 5th 6th
7th 8th should have value between 0-9 and 9th character should have value of
either F or M.
Similarly it will follow the same constraint rule that you've defined in 2nd
clause (after OR) except that 2nd character must be '-'
Ex:
create table t
(emp_id varchar(15)
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'))
insert into t values('visk090')--this will fail because 4th character must
be numeric.
insert into t values('vis90987f')--success
insert into t values('vis90987x')--fail (last character is ot F or M)
insert into t values('v-i90987F')--success (2nd check constraint clause
evaluated)
In short the above check constraint can also be implemented as follows.
create table t1
(emp_id varchar(15)
CONSTRAINT CK_emp_id1 CHECK (emp_id LIKE
'[A-Z][-A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'))
insert into t1 values('visk090')--this will fail because 4th character must
be numeric.
insert into t1 values('vis90987f')--success
insert into t1 values('vis90987x')--fail (last character is ot F or M)
insert into t1 values('v-i90987F')--success
insert into t1 values('v9i90987F')--fail (2nd character is not A-Z or a '-'
Therefore by specifying values in [] square brackets you are looking for
range of values only, but in addition you are doing positional check of
characters.
Look for more information in books online under topic
"like"
"wildcard characters"
-- Vishal Parkar vgparkar@yahoo.co.in
- Next message: Tibor Karaszi: "Re: OUTPUT var in SP"
- Previous message: Uri Dimant: "Re: uniqueidentifier***"
- In reply to: RM: "check constraint**"
- Next in thread: RM: "Re: check constraint**"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
