Ensure Data Integrity with Check Constraints

Jim_Knicely · August 2018

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!