Primary Key Constraints

A primary key (PK) is a single column or combination of columns (called a compound key) that uniquely identifies each row in a table. A primary key constraint contains unique, non-null values.

When you apply the primary key constraint, the NOT NULL and unique constraints are added implicitly. You do not need to specify them when you create the column. However, if you remove the primary key constraint, the NOT NULL constraint continues to apply to the column. To remove the NOT NULL constraint after removing the primary key constraint, use the ALTER COLUMN DROP NOT NULL parameter of the ALTER TABLE statement (see Dropping Constraints).

The following example shows how you can add a primary key constraint on the employee_id field:

CREATE TABLE employees (employee_id INTEGER PRIMARY KEY);

Alternatively, you can add a primary key constraint after the column is created:

CREATE TABLE employees (employee_id INTEGER);
ALTER TABLE employees 
   ADD PRIMARY KEY (employee_id);

Note: If you specify a primary key constraint using ALTER TABLE, the system returns the following message, which is informational only. The primary key constraint is added to the designated column.

WARNING 2623: Column "employee_id" definition changed to NOT NULL

You can also use primary keys to constrain more than one column:

CREATE TABLE employees (employee_id INTEGER,
   employee_gender CHAR(1),
   PRIMARY KEY (employee_id, employee_gender)
);

When you enable automatic enforcement of primary or unique key constraints, Vertica applies enforcement for:

Alternatively, rather than automatic enforcement, you can use ANALYZE_CONSTRAINTS to validate primary and unique key constraints after issuing these statements. For more information on enabling and disabling primary key constraints, refer to Enforcing Primary Key, Unique Key, and Check Constraints Automatically.