Self-Descriptive Constraint Names
Jim_Knicely
- Select Field - Administrator
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!