We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Ensure Data Integrity with Check Constraints — Vertica Forum

Ensure Data Integrity with Check Constraints

Jim_KnicelyJim_Knicely - Select Field - Administrator

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.