The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Ensure Data Integrity with Check Constraints

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.