It is a good idea to have a check digit in ID columns, especially if values are entered manually. Here is a check...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.