Adding a Column Check Constraint

Less_than_zero_1987_posterVertica does not support check constraints for table columns. There are some pretty darn good reasons for that! Probably the best reason is the fact that we want to load data into Vertica super fast. To do that Vertica ignores all constraints assuming we’ve already scrubbed the data we’re loading. So why introduce another hurdle?

Oddly, some folks have expressed interest in being able to add a check constraint to their table anyway. So just for fun, I’ll show you a quirky little work around so that you can add a quasi check constraint to a table. But I wouldn’t recommend it in production because it’ll degrade performance.

Say we want to add a “greater than zero” check constraint on the C1 column of the JIM table. To do so, we can add a pseudo column named C2 to the table and derive a default value for C2 from the value in C1. We can stop an insert by making the calculation of C2 result in a division by zero error.

Example:

dbadmin=> CREATE TABLE jim (c1 INT);
CREATE TABLE
dbadmin=> ALTER TABLE jim ADD COLUMN c2 INT

dbadmin->   DEFAULT 1 / DECODE(SIGN(c1), 1, 1, 0);
ALTER TABLE
dbadmin=> INSERT INTO jim (c1) VALUES (1);
OUTPUT
--------
1
(1 row)
dbadmin=> INSERT INTO jim (c1) VALUES (0);
ERROR 3117:  Division by zero
dbadmin=> INSERT INTO jim (c1) VALUES (-1);
ERROR 3117:  Division by zero
Notice that I was able to insert a value of 1 into the JIM table, but not a value of 0 or -1.

Have fun!

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.