Modifying Tables

You can use the ALTER TABLE statement to:

• add new columns to tables
• add table constraints
• drop named table constraints

Adding a new column to a table:

• automatically adds the new column with a unique column name to all superprojections of the table

  A superprojection is a projection that contains every column of a table in the Logical Schema. A table can have multiple superprojections with different sort orders.

• populates the column according to the column-constraint (DEFAULT for example).

  Adds a referential integrity constraint to the metadata of a column. See Adding Join Constraints in the Database Administrator's Guide.

  Referential integrity in Vertica consists of a set of constraints (logical schema objects) that define primary key and foreign key columns. In a star schema or snowflake schema:

  • Each dimension table must have a PRIMARY KEY constraint.
  • The fact table must contain columns that can be used to join the fact table to dimension tables.
  • Fact table join columns must have FOREIGN KEY constraints in order to participate in pre-join projections.
  • Outer join queries produce expected results only when the fact table join column used in the query does not have a FOREIGN KEY constraint.

  Syntax

  [ CONSTRAINT constraint-name ]

  { [ NOT ] NULL

  | PRIMARY KEY

  | REFERENCES table-name [ ( column-name ) ]

  [ DEFAULT constant ]

  }

  Semantics

  CONSTRAINT constraint-name	optionally assigns a name to the constraint. Vertica recommends that you name all constraints.
  NULL	(default) specifies that the column is allowed to contain null values.
  NOT NULL	specifies that the column must receive a value during INSERT and UPDATE operations. If no DEFAULT value is specified and no value is provided, the INSERT or UPDATE statement returns an error because no default value exists.
  PRIMARY KEY	adds a referential integrity constraint defining the column as the primary key. Referential integrity in Vertica consists of a set of constraints (logical schema objects) that define primary key and foreign key columns. In a star schema or snowflake schema: Each dimension table must have a PRIMARY KEY constraint. The fact table must contain columns that can be used to join the fact table to dimension tables. Fact table join columns must have FOREIGN KEY constraints in order to participate in pre-join projections. Outer join queries produce expected results only when the fact table join column used in the query does not have a FOREIGN KEY constraint.
  REFERENCES	adds a referential integrity constraint defining the column as a foreign key. If column is omitted, the default is the primary key of table.
  table-name	specifies the table to which the REFERENCES constraint applies.
  column-name	specifies the column to which the REFERENCES constraint applies. If column is omitted, the default is the primary key of table-name.
  DEFAULT constant	specifies a constant to be used by default in any INSERT operation that does not specify a value for the column. The data type of the constant must match the data type of the column. If no default value is specified, the default is null.

  Notes

  • You must define primary key and foreign key constraints in all tables that participate in joins. See Adding Constraints.
  • You must specify NOT NULL constraints on columns that will be given PRIMARY and REFERENCES constraints.
  • Vertica does not support expressions in the DEFAULT clause.
• does not affect the K-Safety of the physical schema design.

  A physical schema consists of a set of projections used to store data on disk. The projections in the physical schema are based on the objects in the Logical Schema.

  K represents the maximum number of nodes in a database that can fail and recover with no loss of data. In Vertica V2.1, the value of K can be zero (0) or one (1). The value of K can be one (1) only when the Physical Schema design meets certain requirements. The designs generated by the Database Designer are K-Safe.

  A physical schema consists of a set of projections used to store data on disk. The projections in the physical schema are based on the objects in the Logical Schema.