Dropping constraints

ALTER TABLE drops constraints from tables in two ways:

• ALTER TABLE...DROP CONSTRAINT removes a named table constraint.

• ALTER TABLE...ALTER COLUMN removes a column's NOT NULL constraint.

For example, table store_orders_2018 specifies the following constraints:

• Named constraint pk identifies column order_no as a primary key.

• Named constraint IsYear2018 specifies a check constraint that allows only 2018 dates in column order_date.

• Named constraint Ship5dAfterOrder specifies a check constraint that disallows any ship_date value that is more than 5 days after order_date.

• Columns order_no and order_date are set to NOT NULL.

CREATE TABLE public.store_orders_2018 (
    order_no int NOT NULL CONSTRAINT pk PRIMARY KEY,
    product_key int,
    product_version int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date,
    CONSTRAINT IsYear2018 CHECK (DATE_PART('year', order_date)::int = 2018),
    CONSTRAINT Ship5dAfterOrder CHECK (DAYOFYEAR(ship_date) - DAYOFYEAR(order_date) <=5)
);

Dropping named constraints

You remove primary, foreign key, check, and unique constraints with ALTER TABLE...DROP CONSTRAINT, which requires you to supply their names. For example, you remove the primary key constraint in table store_orders_2018 as follows:

=> ALTER TABLE store_orders_2018 DROP CONSTRAINT pk;
ALTER TABLE
=> SELECT export_tables('','store_orders_2018');
                                                             export_tables
---------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE public.store_orders_2018
(
    order_no int NOT NULL,
    product_key int,
    product_version int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date,
    CONSTRAINT IsYear2018 CHECK (((date_part('year', store_orders_2018.order_date))::int = 2018)) ENABLED,
    CONSTRAINT Ship5dAfterOrder CHECK (((dayofyear(store_orders_2018.ship_date) - dayofyear(store_orders_2018.order_date)) <= 5)) ENABLED
);

Dropping NOT NULL constraints

You drop a column's NOT NULL constraint with ALTER TABLE...ALTER COLUMN, as in the following example:

=> ALTER TABLE store_orders_2018 ALTER COLUMN order_date DROP NOT NULL;
ALTER TABLE

Dropping primary keys

You cannot drop a primary key constraint if another table has a foreign key constraint that references the primary key. To drop the primary key, you must first drop all foreign keys that reference it.

Dropping constraint-referenced columns

If you try to drop a column that is referenced by a constraint in the same table, the drop operation returns with an error. For example, check constraint Ship5dAfterOrder references two columns, order_date and ship_date. If you try to drop either column, Vertica returns the following error message:

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date;
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:
Constraint Ship5dAfterOrder references column ship_date
HINT:  Use DROP .. CASCADE to drop or modify the dependent objects

In this case, you must qualify the DROP COLUMN clause with the CASCADE option, which specifies to drop the column and its dependent objects—in this case, constraint Ship5dAfterOrder:

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date CASCADE;
ALTER TABLE

A call to Vertica function EXPORT_TABLES confirms that the column and the constraint were both removed:

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date CASCADE;
ALTER TABLE
dbadmin=> SELECT export_tables('','store_orders_2018');
                                              export_tables
---------------------------------------------------------------------------------------------------------

CREATE TABLE public.store_orders_2018
(
    order_no int NOT NULL,
    product_key int,
    product_version int,
    order_date timestamp,
    shipper varchar(20),
    CONSTRAINT IsYear2018 CHECK (((date_part('year', store_orders_2018.order_date))::int = 2018)) ENABLED
);

(1 row)