Ensure Data Integrity with Check Constraints: Quick Tip

Jim Knicely authored this tip. To help safeguard against “bad” data creeping into your database, Vertica supports Table “Check Constraints”. They specify a Boolean expression that evaluates a column’s value on each row. If the expression resolves to FALSE for a given row, the column value is regarded as violating the constraint and Vertica will not allow the “bad” data to be inserted. Examples: Say I don’t want values that are less than 1 to be inserted into the C1 column of the CC_TEST table. I could create a check constraint to enforce that business rule! dbadmin=> CREATE TABLE cc_test (c1 INT CONSTRAINT cc_test_c1_positive CHECK (C1 > 0)); CREATE TABLE dbadmin=> INSERT INTO cc_test(c1) VALUES (1); — Good value! OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO cc_test(c1) VALUES (0); — Bad value! ERROR 7230: Check constraint ‘public.cc_test.cc_test_c1_positive’ (cc_test.c1 > 0) violation: ‘c1=0’ dbadmin=> INSERT INTO cc_test(c1) VALUES (-1); — Bad value! ERROR 7230: Check constraint ‘public.cc_test.cc_test_c1_positive’ (cc_test.c1 > 0) violation: ‘c1=-1’ We can really get creative too! In the next example, I only want data inserted into the TELEPHONE column of the TELEPHONE_NUMBERS table that has the format area_code_3_digits-exchange_3_digits-number_4_digits (i.e., 123-456-7890). dbadmin=> CREATE TABLE telephone_numbers (telephone_number VARCHAR(14) CONSTRAINT telephone_number_format_ck CHECK (REGEXP_LIKE(telephone_number, '^([01][-])?(\(\d{3}\)|\d{3})[-]?\d{3}[-]\d{4}$'))); CREATE TABLE dbadmin=> INSERT INTO telephone_numbers SELECT ‘123-456-7890’; — Good value! OUTPUT ——– 1 (1 row) dbadmin=> INSERT INTO telephone_numbers SELECT ‘123-456-789’; — Bad value! ERROR 7230: Check constraint ‘public.telephone_numbers.telephone_number_format_ck’ regexp_like(telephone_numbers.telephone_number, E’^([01][-])?(\\(\\d{3}\\)|\\d{3})[-]?\\d{3}[-]\\d{4}$’) violation: ‘telephone_number=123-456-789’ dbadmin=> UPDATE telephone_numbers SET telephone_number = ‘XXX-XXX-XXXX’; — Bad value! ERROR 7230: Check constraint ‘public.telephone_numbers.telephone_number_format_ck’ regexp_like(telephone_numbers.telephone_number, E’^([01][-])?(\\(\\d{3}\\)|\\d{3})[-]?\\d{3}[-]\\d{4}$’) violation: ‘telephone_number=XXX-XXX-XXXX’ dbadmin=> SELECT * FROM telephone_numbers; telephone_number —————— 123-456-7890 (1 row) Have fun!