1

I am making a table PATIENT which looks something like following:

CREATE TABLE PATIENT
(
PATIENT_ID          integer NOT NULL auto_increment,
PATIENT_CONTROL_NO  char(10) NOT NULL,
MEDICAL_RECORD_NO   char(10) NOT NULL,
PERSON_ID           integer NOT NULL,
GUARANTOR_ID        integer NOT NULL,
GUARANTOR_REL_CODE  char(2) NOT NULL,
TREAT_AUTH_CODE     char(18) NULL,
ADC_CODE            char(2) NULL,
CAPPLAN_AMTMONTH        decimal(11,2) NULL,
PRIMARY KEY (PATIENT_ID,PATIENT_CONTROL_NO,MEDICAL_RECORD_NO),
check(GUARANTOR_REL_CODE in('01','18','19','20','21','39','40','53','G8'))
);

Now the thing is I want to add the following Constraint(character-wise) in the column "TREAT_AUTH_CODE" which has 18 characters:

CHARACTER_POSITION               CHARACTER_TYPE
   1,2,5,6,9,10                     NUMBERS
   3,4,7,8,11-18                   ALPHABETS

Do I have to use the concept of arrays or can it be achieved in some easier way? If arrays, help with that too....

Thankx.....

VJ22
  • 171
  • 3
  • 12

2 Answers2

2

You can use a BEFORE trigger to enforce the pattern on TREAT_AUTH_CODE:

CREATE TRIGGER t_PatientAuthCheck BEFORE INSERT ON PATIENT
FOR EACH ROW 
  BEGIN 
    IF (NEW.TREAT_AUTH_CODE REGEXP '^[0-9]{2}[A-Z]{2}[0-9]{2}[A-Z]{2}[0-9]{2}[A-Z]{8}') = 0 THEN 
     SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Invalid Treatment Code';
    END IF; 
  END;

Sql Fiddle here

Note that check constraints are not currently enforced by MySql - you would also need to move the GUARANTOR_REL_CODE check into the trigger.

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
1

You can use regexp to archieve this:

CHECK(TREAT_AUTH_CODE RLIKE '([[:digit:]]{2}[[:alpha:]]{2}){3}[[:alpha:]]{6}')

Checkout documentation about regular expressions: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

rMX
  • 1,070
  • 16
  • 23