Ensure Data Integrity with Check Constraints
Jim_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!