A name and a data type must be specified for each column defined for a table. Each specified column can be further defined with one or more attributes (see Column and Table Constraints ) or constraint definitions.
There are different specifications for constraints, some of which apply to multiple categories of constraints.
You cannot declare semantic database constraints on columns defined with XML, BLOB, CLOB, ARRAY/VARRAY, Period, JSON, or Geospatial data types.
- UNIQUE constraint definition on a single column.
UNIQUE constraints are implemented as USIs.
If a row-level security-protected table is defined with a UNIQUE constraint, enforcement of the UNIQUE constraint does not run any row-level security policy defined for the table.
UNIQUE constraints are applicable to all rows in a row-level security-protected table, not only to user-visible rows.
You cannot define a UNIQUE constraint on a row-level security constraint column of a row-level security-protected table.
If you do not specify either an explicit PRIMARY INDEX or NO PRIMARY INDEX, Vantage converts any UNIQUE constraints you define to either a unique primary index or a unique secondary index, depending on whether a primary key is also defined for the table (see Primary Index Defaults for details).
The following table shows what Vantage does if PrimaryIndexDefault is set to D or P and a CREATE TABLE request specifies this constraint on a column set without also specifying either a PRIMARY INDEX or NO PRIMARY INDEX option.
| Option | Vantage Behavior |
|---|---|
| PRIMARY INDEX | Converts column set defined as the primary key to the unique primary index for the table. Any additional column sets defined with UNIQUE constraints are redefined as unique secondary indexes. PRIMARY INDEX and UNIQUE constraints are implemented as unique secondary indexes, and are also explicitly redefined as unique secondary indexes in the SQL create text for the table definition. |
| UNIQUE | Converts first column set defined with a UNIQUE constraint to the unique primary index for the table. Any other column sets defined with UNIQUE constraints are redefined as either unique secondary indexes. |
- CHECK constraint definition on a single column.
CHECK constraints are not implemented as indexes.
If a row-level security-protected table is defined with a CHECK constraint, enforcement of the constraint does not run any security policy defined for the table.
CHECK constraints are applicable to all rows in a row-level security-protected table, not just to user-visible rows.
You cannot define a CHECK constraint on a row-level security constraint column of a row-level security-protected table.
- PRIMARY KEY constraint definition on a single column.
PRIMARY KEY constraints are implemented as USIs.
If a PRIMARY KEY constraint is defined where either or both the parent and child table are row-level security-protected, execution of the referential integrity constraint does not run any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row-level security-protected.
You cannot define a PRIMARY KEY constraint on a row-level security-protected column.
If you do not specify an explicit PRIMARY INDEX or NO PRIMARY INDEX option, Vantage converts any PRIMARY KEY constraint you define for a table to a unique primary index (see Primary Index Defaults for complete details).
- REFERENCES constraint definition on a single column.
REFERENCES constraints are not implemented as indexes.
If a REFERENCES constraint is defined where either or both the parent and child table are row-level security-protected, execution of the referential integrity constraint does not run any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row-level security-protected.
Temporal tables do not support foreign key REFERENCES constraints for standard or batch referential integrity.
- CHECK constraint definition on a composite column set.
- FOREIGN KEY ... REFERENCES constraint definition on a composite column set.
- PRIMARY KEY constraint definition on a composite column set.
- UNIQUE constraint definition on a composite column set.
You cannot specify constraints other than NULL or NOT NULL for global temporary trace tables (see CREATE GLOBAL TEMPORARY TRACE TABLE ).
Performance Issues for Referential Integrity Constraints
The following set of topics describes important performance issues that are included in enforcing referential integrity.
For more information on referential integrity, see Foreign Key Constraints.
Benefits of Referential Integrity
| Benefit | Description |
|---|---|
| Maintains data consistency | Vantage enforces integrity relationships between tables based on the definition of a PK or a FK. |
| Maintains data integrity | When performing INSERT, UPDATE, and DELETE requests, Vantage maintains data integrity between referencing and referenced tables. |
| Increases development productivity | You need not code applications to enforce referential constraints because Vantage automatically enforces referential integrity. |
| Requires fewer programs to be written | Vantage prevents update activities from violating
referential constraints. Vantage enforces referential integrity in all environments; you need no additional programs. |
Overhead Costs of Referential Integrity
Overhead costs includes building the reference index subtables and inserting, updating, and deleting rows in the referencing and referenced tables. Overhead for inserting, updating, and deleting rows in the referencing table is similar to that of USI subtable row handling.
Vantage redistributes a row for each reference to the AMP containing the USI or reference index subtable entry. Processing differs after that, and most of the additional cost is in message handling.
- Most importantly, the performance impact to update operations, which is frequently slowed when a referential integrity constraint must be enforced.
- INSERT performance slows for table because any referential integrity constraints defined for the table must be enforced.
- The cost of extra disk space for table maintenance resulting from referential integrity constraints.
- The cost of extra disk space for reference index subtables and savings on program maintenance and increased data integrity.
- The cost of DML integrity validity checking in applications and the cost of not checking.
Join Elimination and Referential Integrity
Join elimination is a process undertaken by the Optimizer to eliminate redundant joins based on information from referential integrity constraints.
- A referential integrity relationship exists between the two tables.
- Request conditions are conjunctive (ANDed rather than ORed).
Therefore, if any single condition in an ANDed set fails, the entire condition fails.
- The request does not contain reference columns from the PK table, other than the PK columns, including the SELECT, WHERE, GROUP BY, HAVING, ORDER BY columns.
- PK columns in the WHERE clause appear only in PK-FK joins.
- The PK join is removed from the query.
- All references to the PK columns in the query are mapped to the corresponding foreign key columns.
If foreign key columns are nullable, Vantage adds a NOT NULL condition to the request.
Standard Referential Integrity and Batch Referential Integrity
In standard referential integrity, whether you are doing row-at-time updates or set-processing INSERT SELECT requests, each child row is separately matched to a row in the parent table, one row at a time. A separate SELECT request against the parent table is performed for each child row. Depending on your demographics, Vantage may select parent rows more than once.
With batch referential integrity, all rows within a single request (even if only one row is affected) are spooled, sorted, and their references checked in a single operation, as a join to the parent table. Depending on the number of rows in the INSERT … SELECT request, batch referential integrity may be considerably faster than checking each parent-child relationship.
For row-at-time updates, there is little difference between standard referential integrity and batch referential integrity. But if you plan to load primarily using INSERT ... SELECT requests, batch referential integrity is recommended.