Adding Constraints

Add constraints on one or more table columns using the following SQL commands:

CREATE TABLE: Add a constraint on one or more columns.
ALTER TABLE: Add or drop a constraint on one or more columns.

Vertica recommends naming a constraint but it is optional; if you specify the CONSTRAINT keyword, you must give a name for the constraint.

The examples that follow illustrate several ways of adding constraints. For additional details, see:

Adding Column Constraints with CREATE TABLE

There are several ways to add a constraint on a column using CREATE TABLE:

On the column definition using the CONSTRAINT keyword, which requires that you assign a constraint name, in this example, dim1PK:
```
CREATE TABLE dim1 (  c1 INTEGER CONSTRAINT dim1PK PRIMARY KEY,
  c2 INTEGER
);
```
On the column definition, omitting the CONSTRAINT keyword. When you omit the CONSTRAINT keyword, you cannot specify a constraint name:
```
CREATE TABLE dim1 (  c1 INTEGER PRIMARY KEY,
  c2 INTEGER
);
```
After the column definition, using the CONSTRAINT keyword and assigning a name, in this example, dim1PK:
```
CREATE TABLE dim1 (  c1 INTEGER,
  c2 INTEGER,
  CONSTRAINT dim1pk PRIMARY KEY(c1)
);
```

After the column definition, omitting the CONSTRAINT keyword:

CREATE TABLE dim1 (  c1 INTEGER,
  c2 INTEGER,
  PRIMARY KEY(c1)
);

Adding Two Constraints on a Column

To add more than one constraint on a column, specify the constraints one after another when you create the table column. For example, the following statement enforces both not NULL and unique constraints on the customer_key column, indicating that the column values cannot be NULL and must be unique:

CREATE TABLE test1 (   id INTEGER NOT NULL UNIQUE,
   ...
);

Adding a Foreign Key Constraint on a Column

There are four ways to add a foreign key constraint on a column using CREATE TABLE. The FOREIGN KEY keywords are not valid on the column definition, only after the column definition:

On the column definition, use the CONSTRAINT and REFERENCES keywords and name the constraint, in this example, fact1dim1PK. This example creates a column with a named foreign key constraint referencing the table (dim1) with the primary key (c1):
```
CREATE TABLE fact1 (  c1 INTEGER CONSTRAINT fact1dim1FK REFERENCES dim1(c1),
  c2 INTEGER
);
```
On the column definition, omit the CONSTRAINT keyword and use the REFERENCES keyword with the table name and column:
```
CREATE TABLE fact1 (  c1 INTEGER REFERENCES dim1(c1),
  c2 INTEGER
);
```
After the column definition, use the CONSTRAINT, FOREIGN KEY, and REFERENCES keywords and name the constraint:
```
CREATE TABLE fact1 (  c1 INTEGER,
  c2 INTEGER,
  CONSTRAINT fk1 FOREIGN KEY(c1) REFERENCES dim1(c1)
);
```

After the column definition, omitting the CONSTRAINT keyword:

CREATE TABLE fact1 (  c1 INTEGER,
  c2 INTEGER,
  FOREIGN KEY(c1) REFERENCES dim1(c1)
);

Each of the following ALTER TABLE statements adds a foreign key constraint on an existing column, with and without using the CONSTRAINT keyword:

ALTER TABLE fact2 
  ADD CONSTRAINT fk1 FOREIGN KEY (c1) REFERENCES dim2(c1);

ALTER TABLE fact2   ADD FOREIGN KEY (c1) REFERENCES dim2(c1);

For additional details, see Foreign Key Constraints.

Adding Multicolumn Constraints

The following example defines a primary key constraint on multiple columns by first defining the table columns (c1 and c2), and then specifying both columns in a PRIMARY KEY clause:

CREATE TABLE dim (   c1 INTEGER,
   c2 INTEGER,
   PRIMARY KEY (c1, c2)
);

To specify multicolumn (compound) primary keys, the following example uses CREATE TABLE to define the columns. After creating the table, ALTER TABLE defines the compound primary key and names it dim2PK:

CREATE TABLE dim2 (  c1 INTEGER,
  c2 INTEGER,
  c3 INTEGER NOT NULL,
  c4 INTEGER UNIQUE
);
ALTER TABLE dim2
  ADD CONSTRAINT dim2PK PRIMARY KEY (c1, c2);

In the next example, you define a compound primary key as part of the CREATE TABLE statement. Then you specify the matching foreign key constraint to table dim2 using CREATE TABLE and ALTER TABLE:

CREATE TABLE dim2 (  c1 INTEGER,
  c2 INTEGER,
  c3 INTEGER NOT NULL,
  c4 INTEGER UNIQUE,
  PRIMARY KEY (c1, c2)
);
CREATE TABLE fact2 (
  c1 INTEGER,
  c2 INTEGER,
  c3 INTEGER NOT NULL,
  c4 INTEGER UNIQUE
);
ALTER TABLE fact2
  ADD CONSTRAINT fact2FK FOREIGN KEY (c1, c2) REFERENCES dim2(c1, c2);

Specify a foreign key constraint using a reference to the table that contains the primary key. In the ADD CONSTRAINT clause, the REFERENCES column names are optional. The following ALTER TABLE statement is equivalent to the previous ALTER TABLE statement:

ALTER TABLE fact2 ADD CONSTRAINT fact2FK FOREIGN KEY (c1, c2) REFERENCES dim2;

Adding Constraints on Tables with Existing Data

When you add a constraint on a column with existing data, Vertica:

Verifies the validity of the column values only if you are adding a primary key, unique key, or check constraint enabled for automatic enforcement.
Does not verify the validity of column values for other constraint types.

If your data does not conform to the declared non-enabled constraints, your queries could yield unexpected results.

Use ANALYZE_CONSTRAINTS to check for constraint violations in your column. If you find violations, use the ALTER COLUMN SET/DROP parameters of the ALTER TABLE statement to apply or remove a constraint on an existing column.

Note: You can configure your system to automatically enforce primary key, unique key and check constraints during DML. For information on automatic enforcement, see Enforcing Primary Key, Unique Key, and Check Constraints Automatically.

Altering Column Constraints

The following example uses ALTER TABLE to add column b with not NULL and default 5 constraints to a table test6:

CREATE TABLE test6 (a INT);
ALTER TABLE test6 ADD COLUMN b INT DEFAULT 5 NOT NULL;

Use ALTER TABLE with the ALTER COLUMN and SET NOT NULL clauses to add the constraint on column a in table test6:

ALTER TABLE test6 ALTER COLUMN a SET NOT NULL;

Use the SET NOT NULL or DROP NOT NULL clause to add or remove a not NULL column constraint:

=> ALTER TABLE T1 ALTER COLUMN x SET NOT NULL;
=> ALTER TABLE T1 ALTER COLUMN x DROP NOT NULL;

Use these clauses so that the column has the proper constraints when you have added or removed a primary key constraint on a column. You can also use them any time you want to add or remove the NOT NULL constraint.

Note: A PRIMARY KEY constraint includes a NOT NULL constraint. However, if you drop the PRIMARY KEY constraint on a column, the NOT NULL constraint remains on that column.

Enforcing Constraints

Check constraints are enforced by default unless you disable individual constraints when you create or alter the constraint, or set the parameter EnableNewCheckConstraintsByDefault to 0 (disabled). See check constraints for more information.

To maximize query performance, Vertica checks for primary key and foreign key violations when loading into the fact table of a pre-join projection. For more details, see Enforcing Primary Key and Foreign Key Constraints.

Vertica checks for not NULL constraint violations when loading data, but it does not check for unique constraint violations for constraints that are not enabled.

To validate table data on constraints that are not enabled, load data without committing it by using the COPY with the NO COMMIT option. Then perform a post-load check using the ANALYZE_CONSTRAINTS function. If constraint violations are found, you can roll back the load because you have not committed it. For more details, see Detecting Constraint Violations with ANALYZE_CONSTRAINTS.

Note: Vertica enforces check constraints automatically by default. You can also enforce primary key and unique key constraints automatically. See Enforcing Primary Key, Unique Key, and Check Constraints Automatically.