
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'
Helpful link:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/ConstraintTypes/CheckConstraints.htmHave fun!