Elegant check sum constraint 1

[Thargy]

I am looking for an elegant solution to the following data validation constraint requirement.

The National Health Service (NHS)in the U.K. uses unique identifiers for every individual in the country, called NHS Numbers. These are validated according to the following rule:-

NHS NUMBER VALIDATION

The NHS number comprises 10 digits. The first nine are the
identifier and the tenth is a check digit used to confirm the
number's validity. The check digit is calculated using the Modulus
11 algorithm. There are four steps in the calculation:

• Step 1 - multiply each of the first nine digits by a weighting
factor as follows;
Digit Position
(Starting from the left)
Factor
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
• Step 2 - add the results of each multiplication together
• Step 3 - divide the total by 11 and establish the remainder
• Step 4 - subtract the remainder from 11 to give the check digit

[COLOR=blue]There are two occasions where the check digit calculation process
must be modified slightly:
• if the result of step 4 is 11 then a check digit of 0 is used
• if the result of step 4 is 10 then the number is invalid and not
used.[/color]

Example: Suppose the first nine digits of the number are 401 023 213
• Step 1 - apply weighting factors
Digit
Position
Value Factor Result
1 4 x 10 = 40
2 0 x 9 = 0
3 1 x 8 = 8
4 0 x 7 = 0
5 2 x 6 = 12
6 3 x 5 = 15
7 2 x 4 = 8
8 1 x 3 = 3
9 3 x 2 = 6
• Step 2 - add the results of each multiplication together
40 + 0 + 8 + 0 + 12 + 15 + 8 + 3 + 6 = 92
• Step 3 - divide the total by 11
92 ÷ 11 = 8, remainder 4
• Step 4 - subtract the remainder from 11 to give the check digit
11 - 4 = 7
The complete NHS Number in this example is therefore: 401 023 2137
[COLOR=blue]Please note there are some restrictions
Constant number strings such as 444 444 4444, 666 666 6666 etc are
not issued.[/color]
So, although they pass the checksum, they are invalid.
The reason for excluding numbers based on a single repeating digit is to safeguard the integrity of the numbering system and prevent potential
mistaken use or abuse.

Source
Newcastle Primary Care Trust 6
Data Quality Policy – NHS Number Procedure (PCTP(A)04b)

To summarise, the validation rules are that either the check digit matches the calculated check value, the check sum was 10 and the number is disallowed, or the calculated check value was 11 and the check digit is zero. Strings of repeated numbers are disallowed.

I have implemented this as follows:-

CREATE TABLE NHS_CONSTRAINT_TEST
    (NHS_NUMBER NUMBER(10,0));

/* Disallow ten repeated digits*/
ALTER TABLE NHS_CONSTRAINT_TEST
ADD CONSTRAINT CHK_DIGITS_MUST_DIFFER
CHECK (NHS_NUMBER != RPAD(SUBSTR(NHS_NUMBER,1,1),10,SUBSTR(NHS_NUMBER,1,1)));

/* Apply all the other validation rules */
ALTER TABLE NHS_CONSTRAINT_TEST
ADD CONSTRAINT CHK_VALID_TENTH_DIGIT
CHECK (
	   /* If checksum is 10, don't use the number */      
	  (MOD(SUBSTR(NHS_NUMBER,1,1)*10 +
       SUBSTR(NHS_NUMBER,2,1)*9 +
	   SUBSTR(NHS_NUMBER,3,1)*8 +
	   SUBSTR(NHS_NUMBER,4,1)*7 +
	   SUBSTR(NHS_NUMBER,5,1)*6 +
	   SUBSTR(NHS_NUMBER,6,1)*5 +
	   SUBSTR(NHS_NUMBER,7,1)*4 +
	   SUBSTR(NHS_NUMBER,8,1)*3 +
	   SUBSTR(NHS_NUMBER,9,1)*2,11) != 1)
	   OR
	   /* If number is exactly divisible by 11, check digit = 0 */
      (MOD(SUBSTR(NHS_NUMBER,1,1)*10 +
       SUBSTR(NHS_NUMBER,2,1)*9 +
	   SUBSTR(NHS_NUMBER,3,1)*8 +
	   SUBSTR(NHS_NUMBER,4,1)*7 +
	   SUBSTR(NHS_NUMBER,5,1)*6 +
	   SUBSTR(NHS_NUMBER,6,1)*5 +
	   SUBSTR(NHS_NUMBER,7,1)*4 +
	   SUBSTR(NHS_NUMBER,8,1)*3 +
	   SUBSTR(NHS_NUMBER,9,1)*2,11) = 11
	   AND SUBSTR(NHS_NUMBER,10,1) = 0)
       OR
	   /* Check digit matches calculated checksum  */	   
	   (11-MOD(SUBSTR(NHS_NUMBER,1,1)*10 +
       SUBSTR(NHS_NUMBER,2,1)*9 +
	   SUBSTR(NHS_NUMBER,3,1)*8 +
	   SUBSTR(NHS_NUMBER,4,1)*7 +
	   SUBSTR(NHS_NUMBER,5,1)*6 +
	   SUBSTR(NHS_NUMBER,6,1)*5 +
	   SUBSTR(NHS_NUMBER,7,1)*4 +
	   SUBSTR(NHS_NUMBER,8,1)*3 +
	   SUBSTR(NHS_NUMBER,9,1)*2,11) = SUBSTR(NHS_NUMBER,10,1)));  

/* Sample data, first row fails (repeated digits), second should succeed */	   
INSERT INTO NHS_CONSTRAINT_TEST (NHS_NUMBER) VALUES (1111111111);
INSERT INTO NHS_CONSTRAINT_TEST (NHS_NUMBER) VALUES (4010232137);

My question is this. Can anyone think of a more elegant solution than my last whopper of a constraint? The sheer amount of text in it is daunting. What is the limit on the size of the text in a constraint, and am I in danger of reaching this limit?

Second, I detest implicit casts, and view them as a bug in code. I always explicitly say "CAST" if I want to do so, but it would be ridiculously cumbersome to do so in the constraint. There is a lot of implicit casting from numbers to chars and back again. Can anyone think of a better way to achieve the constraint condition without such bad practice?

Regards

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

Yes, John, I believe that you can compress the sheer volume of code by using "CHECK check_digit(NHS_NUMBER) = <some value>". You use a "create or replace function check_digit(val_in) return..." that has a much-simplified version of your code, above (perhaps using a loop) that should cut down the number of lines of code by about 90%.

I'd do a sample for you presently, but I'm off to an assignment at church presently...If you have not posted a solution by when I return (late in your evening), I'll code up a version for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,

thanks for the advice Dave. I was under the impression that user-defined functions could not be used in constraints. I'll have a go at this, and see what happens.

I didn't get your message until now as my domestic internet connection failed.

Regards

Tharg

Grinding away at things Oracular

 

[karluk]

If I understand your algorithm correctly, you need to allow NHS numbers in exactly two situations.

1. The mod calculation and the checksum are both zero
2. The mod calculation and the checksum add up to 11.

In other words you have to allow NHS numbers where (mod + checksum) is either zero or 11, and disallow everything else.

You don't have to explicitly write code for the invalid situation when "the result of step 4 is 10", because then mod=1 and 0 <= checksum <= 9 and so (mod + checksum) is always greater than zero and less than 11.

That suggests you can simplify your constraint as follows. (I haven't tested this, but I hope it does the job.)

ALTER TABLE NHS_CONSTRAINT_TEST
ADD CONSTRAINT CHK_VALID_TENTH_DIGIT
CHECK (
decode (MOD(SUBSTR(NHS_NUMBER,1,1)*10 +
       SUBSTR(NHS_NUMBER,2,1)*9 +
       SUBSTR(NHS_NUMBER,3,1)*8 +
       SUBSTR(NHS_NUMBER,4,1)*7 +
       SUBSTR(NHS_NUMBER,5,1)*6 +
       SUBSTR(NHS_NUMBER,6,1)*5 +
       SUBSTR(NHS_NUMBER,7,1)*4 +
       SUBSTR(NHS_NUMBER,8,1)*3 +
       SUBSTR(NHS_NUMBER,9,1)*2,11)
         + SUBSTR(NHS_NUMBER,10,1),
           0,1,11,1,0) = 1
);

 

[Thargy]

Karluk,

It's late, I'm tired, and you are well worthy of a star. You've gone one better than provide an answer, you've bothered to check the underlying logic, and unless I'm mistaken, vastly simplified the problem.

A richly deserved star to you.

Regards

T

Grinding away at things Oracular

 

[Thargy]

Santa,

I've tried the following code, but without success, can you advise please.

CREATE TABLE CHECK_CONSTRAINT_TEST
    (TEST_FIELD INTEGER NOT NULL);

INSERT INTO CHECK_CONSTRAINT_TEST (TEST_FIELD) VALUES (1);
COMMIT;

CREATE OR REPLACE FUNCTION Check_Test (value_in INTEGER)
RETURN INTEGER
AS

BEGIN
  IF value_in = 6 THEN
      RETURN 1;
  ELSE
      RETURN 0;
  END IF;
END Check_Test;
/
  
ALTER TABLE CHECK_CONSTRAINT_TEST
ADD CONSTRAINT CHK_IS_NOT_SIX
CHECK (Check_Test(TEST_FIELD)= 0);

/*
Causes
ORA-00904: "CHECK_TEST": invalid IDENTIFIER
*/

Where have I got it wrong?

Regards

Tharg

Grinding away at things Oracular

 

[Beilstwh]

From the sql reference
Restrictions on Check Constraints


* You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.
* The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
* Conditions of check constraints cannot contain the following constructs:
o Subqueries and scalar subquery expressions
o Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
o Calls to user-defined functions
o Dereferencing of REF columns (for example, using the DEREF function)
o Nested table columns or attributes
o The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
o Date constants that are not fully specified

Bill
Oracle DBA/Developer
New York State, USA

 

[Thargy]

Beilstwh,

that's what I thought, but Santa's post above states that I could use such a function, hence my confusion. As you can see from my earlier posting, I was under the impression that user-defined functions were not permitted in constraints.

T

Grinding away at things Oracular

 

[SantaMufasa]

My apologies to Thargtheslayer, Beilstwh, and any others reading this thread: Somehow I had missed the restriction against user-defined functions in constratints.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Santa,

thanks for graciously clarifying this for me. I thought I'd lost the plot there.

I'll have a go at the declarative solution proffered by karluk (he of the analytic brain) and see how it goes.

Regards

Tharg

Grinding away at things Oracular

 

[Thargy]

All,

I've finally thought about it, and after due deliberation added the check constraints to the database, and I'm now awaiting protests from users, when hitherto acceptable data gets 'bounced' by the constraints. Karluk's slim-line version is now on its way into production.

My thanks to all involved.

Regards

Tharg

Grinding away at things Oracular