Self-Descriptive Constraint Names

Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

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!

Sign In or Register to comment.