Dropping Constraints

To drop named constraints, use the ALTER TABLE command.

The following example drops the constraint fact2fk:

=> ALTER TABLE fact2 DROP CONSTRAINT fact2fk;			

To drop constraints that you did not assign a name to, query the system table TABLE_CONSTRAINTS, which returns both system-generated and user-named constraint names. For example:

=> SELECT * FROM TABLE_CONSTRAINTS;

If you do not specify a constraint name, Vertica assigns a constraint name that is unique to that table. In the following output, note the system-generated constraint name C_PRIMARY and the user-defined constraint name fk_inventory_date:

-[ RECORD 1 ]--------+--------------------------
constraint_id        | 45035996273707984
constraint_name      | C_PRIMARY
constraint_schema_id | 45035996273704966
constraint_key_count | 1
foreign_key_count    | 0
table_id             | 45035996273707982
foreign_table_id     | 0
constraint_type      | p
-[   ...   ]---------+--------------------------
-[ RECORD 9 ]--------+--------------------------
constraint_id        | 45035996273708016
constraint_name      | fk_inventory_date
constraint_schema_id | 0
constraint_key_count | 1
foreign_key_count    | 1
table_id             | 45035996273708014
foreign_table_id     | 45035996273707994
constraint_type      | f

Once you know the name of the constraint, you can then drop it using the ALTER TABLE command. (If you do not know the table name, use table_id to retrieve table_name from the ALL_TABLES table.)

Remove NOT NULL Constraints

When a column is a primary key and you drop the primary key constraint, the column retains the NOT NULL constraint. To specify that the column now can contain NULL values, use [DROP NOT NULL] to remove the NOT NULL constraint.

Remove (Drop) a NOT NULL constraint on the column using [DROP NOT NULL]:

ALTER TABLE T1 ALTER COLUMN x DROP NOT NULL;
Important: Using the [SET | DROP] NOT NULL clause does not validate whether the column data conforms to the NOT NULL constraint. Use ANALYZE_CONSTRAINTS to check for constraint violations in a table.

Limitations of Dropping Constraints

See Also

ALTER TABLE