Modifying SET USING and DEFAULT Columns

Removing SET USING and DEFAULT Constraints

You remove a column's SET USING or DEFAULT constraint with ALTER TABLE…ALTER COLUMN, as follows:

ALTER TABLE table-name ALTER COLUMN column-name DROP { SET USING | DEFAULT };

Vertica removes the constraint from the specified column, but the column and its data are otherwise unaffected.

=> ALTER TABLE table1 ALTER COLUMN column1 DROP SET USING;

Modifying a SET USING and DEFAULT Expression

ALTER TABLE…ALTER COLUMN can set an existing column to SET USING or DEFAULT, or change the query expression of an existing SET USING or DEFAULT column. In both cases, existing values remain unchanged. Vertica refreshes column values only in the following cases:

• DEFAUT column: Refreshed only when you load new rows, or when you invoke UPDATE to set column values to DEFAULT.
• SET USING column: Refreshed only when you call REFRESH_COLUMNS on the table.

For details, see Defining Column Values

Dropping Columns Queried by SET USING or DEFAULT

Vertica enforces dependencies between a flattened table and the tables that it queries. Attempts to drop a queried column or its table return an error unless the drop operation, such as DROP TABLE, also includes CASCADE. Vertica implements CASCADE by removing the SET USING or DEFAULT constraint from the flattened table. The table column and its data are otherwise unaffected.

For example, attempts to drop column name in table custDim returns a rollback error, as this column is referenced by SET USING column cust_name in orderFact:

=> ALTER TABLE custDim DROP COLUMN name;
ROLLBACK 7302:  Cannot drop column "name" since it is referenced in the set using expression of table "public.orderFact", column "cust_name"

To drop this column, use the CASCADE option:

=> ALTER TABLE custDim DROP COLUMN name CASCADE;
ALTER TABLE

Vertica removes the SET USING constraint from orderFact.cust_name as part of the drop operation. However, cust_name retains the data that it derived from dropped column custDim.name:

=> SELECT EXPORT_TABLES('','orderFact');
                                                EXPORT_TABLES
------------------------------------------------------------------------------------------------------------
CREATE TABLE public.orderFact
(
    order_id int NOT NULL,
    cid int,
    cust_name varchar(20)
    cust_gender varchar(1) DEFAULT ( SELECT custDim.gender
 FROM public.custDim
 WHERE (custDim.cid = orderFact.cid)),
    amount numeric(12,2)
);
ALTER TABLE public.orderFact ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) ENABLED;
ALTER TABLE public.orderFact ADD CONSTRAINT C_FOREIGN FOREIGN KEY (cid) references public.custDim (cid);
(1 row)

=> SELECT * FROM orderFact;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      300 |   2 | Roz       | F           |  -50.00
      400 |   3 | Eva       | F           |  100.00
      100 |   1 | Alice     | F           |   15.00
      500 |   2 | Roz       | F           |  200.00
      200 |   1 | Alice     | F           | 1000.00
(5 rows)