Naming Constraints
You can create and name constraints in different ways, as the following examples show. OpenText recommends that you name your constraints. If you do not name your constraints, Vertica assigns a name. The examples show various statement syntaxes for creating and altering named and unnamed constraints.
Naming a Primary Key Constraint
Create a table with a named primary key, where the named constraint is myconstraint
.
=> CREATE TABLE addapk (col1 INT CONSTRAINT myconstraint PRIMARY KEY ENABLED, col2 INT); CREATE TABLE
Query the PRIMARY_KEYS
table to view the constraint.
=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk'); constraint_name | column_name | constraint_type | is_enabled -----------------+-------------+-----------------+------------ myconstraint | col1 | p | t (1 row)
Creating a Primary Key Constraint Without Naming
Create a table with a primary key constraint, where you do not name the constraint. In this case, Vertica assigns a name to the constraint.
=> CREATE TABLE addapk (col1 INT PRIMARY KEY ENABLED, col2 INT);
Query the PRIMARY_KEYS
table to view the constraint. Vertica has named the constraint C_PRIMARY
.
=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk'); constraint_name | column_name | constraint_type | is_enabled -----------------+-------------+-----------------+------------ C_PRIMARY | col1 | p | t (1 row)
Altering Named and Unnamed Constraints
Alter a table that includes a constraint that you named.
=> ALTER TABLE addapk ALTER CONSTRAINT myconstraint DISABLED;
Alter a table with a constraint to which Vertica has assigned a name.
=> ALTER TABLE addapk ALTER CONSTRAINT C_PRIMARY DISABLED;
Alter a table by adding a newly named constraint.
=> CREATE TABLE addapk (col1 INT, col2 INT); CREATE TABLE => ALTER TABLE addapk ADD CONSTRAINT myconstraint PRIMARY KEY (COL1) ENABLED; WARNING 2623: Column "col1" definition changed to NOT NULL ALTER TABLE
Alter a table, and let Vertica name the constraint.
=> CREATE TABLE addapk (col1 INT, col2 INT); CREATE TABLE => ALTER TABLE addapk ADD PRIMARY KEY (COL1) ENABLED; WARNING 2623: Column "col1" definition changed to NOT NULL ALTER TABLE
Creating a Multi-Column Constraint
Create a table with a primary key that comprises three columns.
=> CREATE TABLE addapk (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, PRIMARY KEY (col1,col2,col3) ENABLED);
Query the PRIMARY_KEYS
table to view the multi-column primary key constraint.
=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk'); constraint_name | column_name | constraint_type | is_enabled -----------------+-------------+-----------------+------------ C_PRIMARY | col1 | p | t C_PRIMARY | col2 | p | t C_PRIMARY | col3 | p | t (3 rows)
Create a table with a primary key that comprises three columns, and name the constraint.
=> CREATE TABLE addapk (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, CONSTRAINT myconstraint PRIMARY KEY (col1,col2,col3) ENABLED); CREATE TABLE
Add a three-column primary key after you create the table.
=> CREATE TABLE addapk (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT); CREATE TABLE
=> ALTER TABLE addapk ADD CONSTRAINT myconstraint PRIMARY KEY (col1,col2,col3) ENABLED; WARNING 2623: Column "col1" definition changed to NOT NULL WARNING 2623: Column "col2" definition changed to NOT NULL WARNING 2623: Column "col3" definition changed to NOT NULL ALTER TABLE
Creating Multiple Constraint Types
The following example adds three constraints, including primary key, unique, and check constraints.
=> CREATE TABLE addapk (col1 INT CONSTRAINT myPKconstraint PRIMARY KEY ENABLED, col2 INT CONSTRAINT myUconstraint UNIQUE ENABLED, col3 INT CONSTRAINT myCHconstraint CHECK(col1<col2)); CREATE TABLE
Query the CONSTRAINT_COLUMNS
table to view the constraints.
=> SELECT column_name, constraint_name, constraint_type, is_enabled FROM constraint_columns WHERE constraint_type IN ('c','p','u') AND table_name IN ('addapk'); column_name | constraint_name | constraint_type | is_enabled -------------+-----------------+-----------------+------------ col1 | myCHconstraint | c | t col1 | myPKconstraint | p | t col2 | myUconstraint | u | t col2 | myCHconstraint | c | t (4 rows)