getting there [message #374330] |
Wed, 06 June 2001 09:02 |
LEVIATHAN
Messages: 8 Registered: June 2001
|
Junior Member |
|
|
I want to restict data entry so that of the 6 characters allowed, the first three characters can only be numbers (1 to 9) and the last three can
only be letter (A TO Z). Below is a sample of what I have done. But I dont understand why it wont accept my inserted values.
create table subject
(subject_id varchar2(6) not null constaint subject_pk primary key etct...
ALTER TABLE SUBJECT
ADD CONSTRAINT CHK_SUBJECT_ID CHECK
(SUBSTR(SUBJECT_ID, 1,1)<='9' AND
SUBSTR(SUBJECT_ID, 1,1)>='0' AND
SUBSTR(SUBJECT_ID, 2,1)<='9' AND
SUBSTR(SUBJECT_ID, 2,1)>='0' AND
SUBSTR(SUBJECT_ID, 3,1)<='9' AND
SUBSTR(SUBJECT_ID, 3,1)>='0' AND
SUBSTR(SUBJECT_ID, LENGTH(SUBJECT_ID) -1,1)<='Z' and
SUBSTR(SUBJECT_ID, LENGTH(SUBJECT_ID) -1,1)>='A' and
SUBSTR(SUBJECT_ID, LENGTH(SUBJECT_ID) -2,1)<='Z' and
SUBSTR(SUBJECT_ID, LENGTH(SUBJECT_ID) -2,1)>='A' and
SUBSTR(SUBJECT_ID, LENGTH(SUBJECT_ID) -3,1)<='Z' and
SUBSTR(SUBJECT_ID, LENGTH(SUBJECT_ID) -3,1)>='A');
insert into subject values ('999ZED');
BUT I get message check contraint violated why?
|
|
|
|