Multiple-Column Constraint Format

This closely resembles the syntax of the Multiple Column Constraint Format of the CREATE TABLE statement, but the optional INDEX DISABLED keywords are not valid (and return an error) in foreign key constraints that the CREATE TABLE statement defines. This syntax fragment is part of the ADD CONSTRAINT Clause.

(1)

1 
2.1 1
2.2.2.1 NOT NULL
2.2.2.1 NULL
2.2.2.1 UNIQUE
2.2.2.1 2 DISTINCT
2.2.2.1 PRIMARY KEY
2.2.2.1  %REFERENCES Clause3
2.1  ( + ,4 column )
2.1  %CHECK Clause5
2.1 %FOREIGN KEY Definition
1? 2  %Constraint6
1  %FOREIGN KEY
Definition?  CONSTRAINT constraint 2 INDEX DISABLED7

1  FOREIGN KEY  ( + ,4 column )  %REFERENCES Clause3

As in the CREATE TABLE statement, the Multiple-Column Constraint format for ALTER TABLE differs from the Single-Column Constraint format by requiring the FOREIGN KEY keywords before the REFERENCES clause when you specify a foreign key constraint. In addition, as its name implies, the Multiple-Column format can specify a list of columns as the scope of the new constraint, but this syntax is also valid with a single column.

For information about the INDEX DISABLED keyword option, see Using the INDEX DISABLED keywords in a foreign key definition.

The statement fails with an error if you specify both a NOT NULL constraint and a NULL constraint on the same column, or if you define a NOT NULL constraint on a column whose default value is NULL.

You cannot define a NULL constraint on a column whose data type is LIST, MULTISET, SET, or IDSSECURITYLABEL.

If the constraint is on a set of columns that includes a column that stores encrypted data, HCL OneDB cannot enforce the constraint. You can declare a name for the constraint and set its mode with Constraint Definition.

If the ALTER TABLE ADD CONSTRAINT statement defines more than one referential constraints on the same table, each constraint requires its own REFERENCES clause, so that options like ON DELETE CASCADE can be specified (or omitted) for each individual constraint, rather than applied to all of the constraints.

If the database server implicitly creates an index on the same non-opaque column or set of columns as the referential constraint, distribution statistics are automatically calculated on the specified column, or on the lead column of a multiple-column constraint.

These distribution statistics are equivalent to distributions created by the UPDATE STATISTICS statement in HIGH mode, and are available to the query optimizer when it designs query plans for the table on which the new constraint was created. See also the section Automatic Calculation of Distribution Statistics in the description of the CREATE INDEX statement for additional information about statistical distributions that are calculated when an index or constraint is created on an existing table.

Example of adding a multiple-column referential constraint

ALTER TABLE catalog DROP CONSTRAINT aa;

ALTER TABLE catalog ADD CONSTRAINT
   (FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   CONSTRAINT ai DISABLED INDEX DISABLED);

ALTER TABLE catalog ADD CONSTRAINT
   FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   CONSTRAINT ai DISABLED INDEX DISABLED;