It is a good idea to have a check digit in ID columns, especially if values are entered manually. Here is a check function to use:
CREATE OR REPLACE FUNCTION add_check_digit( in_val IN NUMBER ) RETURN NUMBER AS c_in_val VARCHAR2(100); c_sum NUMBER := 0; i NUMBER; BEGIN c_in_val := TO_CHAR( in_val ); i := LENGTH( c_in_val ); WHILE i > 0 LOOP c_sum := c_sum + TO_NUMBER( SUBSTR( c_in_val, i, 1 ) ); i := i - 1; END LOOP; RETURN TO_NUMBER( c_in_val || MOD( c_sum, 10 ) ); END add_check_digit; / SHOW ERRORS
Then create the table:
CREATE TABLE a_table ( id_col number(10) );
Here is a trigger to enforce the rule, since Oracle doesn't allow self-defined functions in check-constraints:
CREATE OR REPLACE TRIGGER ar_iu_a_table AFTER INSERT OR UPDATE OF id_col ON a_table FOR EACH ROW BEGIN IF :new.id_col != add_check_digit( TRUNC( :new.id_col / 10 ) ) THEN RAISE_APPLICATION_ERROR( -20000, 'id_col failed check-digit-test !' ); END IF; END; / SHOW ERRORS
Finally, here is how to use the function for generating new datasets:
INSERT INTO a_table ( id_col) VALUES ( add_check_digit( your_id_sequence.nextval() ) )
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.