Mimicking Enumerated Types
Jim_Knicely
Administrator
I used to work a lot with MySQL. It had a cool data type called "Enumerated Types".
Example in MySQL:
(myadmin@localhost) [jimk]> CREATE TABLE e (ecol ENUM('Bill', 'Sam', 'Jack')); Query OK, 0 rows affected (0.10 sec) (myadmin@localhost) [jimk]> INSERT INTO e VALUES('Bill'); Query OK, 1 row affected (0.00 sec) (dbadmin@localhost) [jimk]> INSERT INTO e values ('Sue'); ERROR 1265 (01000): Data truncated for column 'ecol' at row 1
How do we do this in Vertica? With a Check Constraint!
Example in Vertica:
dbadmin=> CREATE TABLE e (ecol VARCHAR(10) CONSTRAINT ecol_ck CHECK (ecol IN ('Bill', 'Sam', 'Jack'))); CREATE TABLE dbadmin=> INSERT INTO e VALUES('Bill'); OUTPUT -------- 1 (1 row) dbadmin=> INSERT INTO e VALUES ('Sue'); ERROR 7230: Check constraint 'public.e.ecol_ck' (e.ecol = ANY (ARRAY['Bill', 'Sam', 'Jack'])) violation: 'ecol=Sue'
Have fun!
0