Self-Descriptive Constraint Names: Quick Tip

Jim Knicely authored this tip.

Constraints set rules on what data is allowed in table columns and help maintain data integrity. PRIMARY KEY, REFERENCES (foreign key), CHECK, and UNIQUE constraint must be named. If you omit assigning a name, Vertica automatically assigns one.

Example: dbadmin=> CREATE TABLE my_favorites_things (pk INT PRIMARY KEY, thing VARCHAR(100) NOT NULL UNIQUE); CREATE TABLE dbadmin=> INSERT INTO my_favorites_things SELECT 1, 'VERTICA'; OUTPUT -------- 1 (1 row) dbadmin=> SELECT constraint_name, constraint_type FROM table_constraints WHERE table_name = 'my_favorites_things'; constraint_name | constraint_type -----------------+----------------- C_PRIMARY | p C_UNIQUE | u (2 rows) Those Vertica-created constraint names aren’t very descriptive. It’s a good idea to name the constraints yourself so that they are more self-descriptive, perhaps identifying the associated table name and or column name.

Unfortunately we cannot directly rename constraints. I’ll have to drop and re-create them, but this time I’ll provide my own self-descriptive names.

Example: dbadmin=> ALTER TABLE my_favorites_things DROP CONSTRAINT C_PRIMARY; ALTER TABLE dbadmin=> ALTER TABLE my_favorites_things DROP CONSTRAINT C_UNIQUE; ALTER TABLE dbadmin=> ALTER TABLE my_favorites_things ADD CONSTRAINT my_favorites_things_pk PRIMARY KEY (pk); WARNING 4887: Table my_favorites_things has data. Queries using this table may give wrong results if the data does not satisfy this constraint HINT: Use analyze_constraints() to check constraint violation on data ALTER TABLE dbadmin=> ALTER TABLE my_favorites_things ADD CONSTRAINT thing_uk UNIQUE(thing); WARNING 4887: Table my_favorites_things has data. Queries using this table may give wrong results if the data does not satisfy this constraint HINT: Use analyze_constraints() to check constraint violation on data ALTER TABLE dbadmin=> SELECT constraint_name, constraint_type FROM table_constraints WHERE table_name = 'my_favorites_things'; constraint_name | constraint_type ------------------------+----------------- my_favorites_things_pk | p thing_uk | u (2 rows) Helpful link:

https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Constraints/NamingConstraints.htm

Have fun!