Alternative to CHECK

[Rick]

I'm running Vertica 5, and i noticed that when making tables, vertica apparantly doesn't support CHECK constraints. I was wondering if there is any alternative to this in vertica?
Thanks

[id10t]

Hi!


>>I was wondering if there is any alternative to this in vertica?
No. Only ANALYZE_CONSTRAINTS - http://my.vertica.com/docs/6.0.0-1/HTML ... #11175.htm
Also - http://www.vertica-forums.com/viewtopic ... =101&p=143

[Rick]

Thanks

[JimKnicely]

I'm curious about how you guys feel about creating a look up table that stores the "permitted" values for a column and then using an FK and a projection to disallow any values to be stored in the base table that would typically have a CHECK constraint in other databases.

Here's a simple example where I only want to permit the values of "Y" or "N" to be stored in the col1 column of the test table:

Code: Select all

dbadmin=> create table test_lookup (col1 varchar(1) primary key);
CREATE TABLE
dbadmin=> create table test (col1 varchar(1) not null references test_lookup);
CREATE TABLE
dbadmin=> create projection test_pj as select * from test join test_lookup on test_lookup.col1 = test.col1 unsegmented all nodes;
CREATE PROJECTION
dbadmin=> insert into test_lookup values ('Y');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test_lookup values ('N');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('Y');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('X');
ERROR 4165:  Nonexistent foreign key value detected in FK-PK join [(public.test x public.test_lookup) using subquery and test_lookup_b0 (PATH ID: 1)]; value [X]

Although, I suppose using this methodology would snowflake the data model out of control! Plus, in the end, I think that the ETL process should make sure only the desired values are stored in our fact tables and that we shouldn't rely on Vertica to enforce check constraints.

[Rick]

Nice idea, could get messy though with large databases!