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;
[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
- You cannot drop a primary key constraint if another table has a foreign key constraint that references the primary key.
- If you drop a primary or foreign key constraint, the system does not automatically drop the not NULL constraint on a column. You need to manually drop this constraint if you no longer want it.
- If you drop an enabled primary or unique key constraint, the system drops the associated projection if one was automatically created.