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 attribute (see Teradata Vantage™ - Data Types and Literals, B035-1143) or constraint definitions.
There are several 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, BLOB-based UDT, CLOB-based UDT, XML-based UDT, 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 execute any row-level security policy defined for the table.
UNIQUE constraints are applicable to all rows in a row-level security-protected table, not just to user-visible rows.
You can specify UNIQUE constraints on columns having a UDT data type as long as the UDT is not based on XML, BLOB, or CLOB data.
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).
| 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 … | THEN Vantage converts the … |
|---|---|
| PRIMARY INDEX | 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. They are also explicitly redefined as unique secondary indexes in the SQL create text for the table definition. |
| UNIQUE | 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 execute 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 execute any security policy UDFs defined for the constraints on the table. Execution continues as if the tables were not row-level security-protected.
You can specify PRIMARY KEY constraints on columns having a UDT data type.
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 execute 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. See Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182 for details.
- 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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144).
Performance Issues for Referential Integrity Constraints
The following set of topics describes some of the more important performance issues that are included in enforcing referential integrity.
For more information on referential integrity, see The Referential Integrity Rule and 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 | It is not necessary to 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 versus savings on program maintenance and increased data integrity.
- The cost of DML integrity validity checking in applications versus 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, meaning they are ANDed
rather than ORed.
This means that 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.
| IF… | THEN… |
|---|---|
| the preceding conditions are met |
|
| 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 might select parent rows more than once.
With batch referential integrity, all of the 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 could be considerably faster, compared to checking each parent-child relationship individually.
For row-at-time updates, there is very 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.
Referential Constraints
To maximize the usefulness of join elimination, you can specify Referential Constraints that Vantage does not enforce.
You can specify the REFERENCES WITH NO CHECK OPTION option to specify CREATE TABLE and ALTER TABLE statements with Referential Constraints, and the Optimizer can use the constraints without incurring the penalty of database-enforced referential integrity.
But when you use a REFERENCES WITH NO CHECK OPTION clause, Vantage does not enforce the Referential Constraints that you define. This means that a row having a non-null value for a referencing column can exist in a table even if an equal value does not exist in a referenced column. When you specify Referential Constraints, Vantage does not return error messages that would otherwise occur when RI constraints are violated.
If you specify a column name for a Referential Constraint, it need not refer to the single column PK of the referenced table or to a single column alternate key in the referenced table defined as UNIQUE, though best practice dictates that it should. The key acting as the PK in the referenced table need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.
The candidate key must always be unique even if it is not explicitly declared to be so, otherwise the referential integrity can produce incorrect results, and corrupt your databases if you do not take care to ensure that data integrity is maintained.
Specifying Referential Constraints relies heavily on your knowledge of your data. If the data does not actually satisfy the Referential Constraints that you provide, then requests can easily produce incorrect results.
The Optimizer can use the Referential Constraints without incurring the penalty of database-enforced referential integrity, but with the likelihood that Vantage can return corrupted result data.
Naming Constraints
Naming constraints is a good practice to follow because it permits a programmer to debug an embedded SQL or stored procedure application by fetching the name of a violated constraint from the SQLSTATE area. If constraints are not named, debugging is more difficult because it can be very difficult to determine which constraints belong to which system-defined names.
Row-level security constraints must be named. CHECK, UNIQUE, FOREIGN KEY, and PRIMARY KEY constraint specifications should be named.
Constraint names must conform to the rules for Vantage object names and be unique among all other constraint, primary index, and secondary index names specified on the table.
- Uppercase and lowercase alphabetic characters
- Integers
- Any of the following special characters.
- -
- #
- $
Vantage does not assign system-generated names to unnamed constraints.
CHECK Constraints
CHECK constraints are the most general type of SQL constraint specifications. Depending on its position in the CREATE or ALTER TABLE SQL text, a CHECK constraint can apply either to an individual column or to multiple columns.
Vantage derives a table-level index partitioning CHECK constraint from the partitioning expression for a PPI table. The text for this derived constraint cannot exceed 16,000 characters; otherwise, the system aborts the request and returns an error to the requestor. See Partitioning CHECK Constraints and Partitioning CHECK Constraints for Single-Level Partitioning for more information about this.
- You can define CHECK constraints at column-level or at table-level.
- The specified predicate for a CHECK constraint must be a
simple boolean search condition.
Subqueries, aggregate expressions, and CASE expressions are not valid search conditions for CHECK constraint definitions.
- You cannot specify CHECK constraints at any level for volatile tables or global temporary trace tables.
- Be aware that a combination of table-level, column-level, and WITH CHECK OPTION on view constraints can create a constraint expression that is too large to be parsed for INSERT and UPDATE requests.
- Vantage tests CHECK constraints for character columns using
the current session collation.
As a result, a CHECK constraint might be met for one session collation, but violated for another, even though the identical data is inserted or updated.
The following is an example of the potential importance of this. A CHECK constraint is checked on insert and update of a base table character column, and might affect whether a sparse join index defined with that character column gets updated or not for different session character collations, in which case different request results might occur if the index is used in a query plan compared to the case where there is no sparse join index to use.
- Vantage considers unnamed CHECK constraints specified with
identical text and case to be duplicates, and returns an error when you
submit them as part of a CREATE TABLE or ALTER TABLE request.
For example, the following CREATE TABLE request is valid because the case of f1 and the case of F1 are different.
CREATE TABLE t1 (f1 INTEGER, CHECK (f1>0), CHECK (F1>0));
The following CREATE TABLE request, however, is not valid because the case of the two unnamed f1 constraints is identical. This request aborts and returns an error to the requestor.
CREATE TABLE t1 (f1 INTEGER, CHECK (f1>0), CHECK (f1>0));
- The principal difference between defining a CHECK constraint at column-level or at table-level is that column-level constraints cannot reference other columns in their table, while table-level constraints, by definition, must reference other columns in their table.
- If a row-level security-protected table is defined with one or more CHECK constraints, the enforcement of those constraints does not execute any UDF security policies that are defined for the table. The enforcement of the CHECK constraint applies to the entire table. This means that CHECK constraints apply to all rows in a row-level security-protected table, not just to the rows that are user-visible.
- You can specify multiple column-level CHECK constraints on
a single column.
If you define more than one unnamed distinct CHECK constraint for a column, Vantage combines them into a single column-level constraint.
However, Vantage handles each named column-level CHECK constraint separately, as if it were a table-level named CHECK constraint.
- A column-level CHECK constraint cannot reference any other columns in its table.
- A table-level constraint usually references at least two columns from its table.
- Table-level CHECK constraint predicates cannot reference columns from other tables.
- You can define a maximum of 100 table-level constraints for a table at one time.
Foreign Key Constraints
Foreign key constraints permit you to specify referential primary key-foreign key relationships between a unique column set in the current base table and an alternate key column set in a different base table. The FOREIGN KEY keywords are required for table-level foreign key definitions but cannot be used for column-level foreign key definitions;. If you specify FOREIGN KEY, then you must also specify a REFERENCES clause. Vantage uses referential integrity constraints to enforce referential integrity (see The Referential Integrity Rule and Inclusion Compatibilities) and to optimize SQL requests (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142). The overhead of enforcing standard referential integrity constraints is summarized in Referential Integrity Constraint Checks and Overhead Costs of Standard Referential Integrity.
- Standard Referential Integrity
- Batch Referential Integrity
- Referential Constraints
- Temporal Relationship Constraints
Temporal tables can only be defined for so-called “soft” referential integrity relationships, or Referential Constraints, and for Temporal Relationship Constraints, and do not permit foreign key constraints for Standard or Batch Referential Integrity. See Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182 or for details.
- A row having a value, meaning that the implication is false if the referencing column is null, for a referencing column can exist in a table even when no equivalent parent table value exists.
- A row can, in some circumstances, match multiple rows in
its parent table when the referenced and referencing column values are
compared.
This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See the list of rules for Referential Constraints later in this topic for details.
The various types have different applications as described in the following table.
| Referential Constraint Type | Application | |||
|---|---|---|---|---|
| Referential Integrity Constraint (see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144) |
|
|||
| Batch Referential Integrity Constraint (see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144) |
|
|||
| Referential Constraint (see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144) |
|
|||
| Temporal Relationship Constraint
(TRC) (see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182) |
|
|||
Referential Constraints do not enforce primary key-foreign key constraints between tables, so they avoid the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints should be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.
For more information, see Inclusion Compatibilities and the information about CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
The table on the following pages summarizes the differences among the different referential constraints.
| Referential Constraint Type | CREATE/ALTER TABLE Definition Clause | Description | Does It Enforce Referential Integrity? | Level of Referential Integrity Enforcement | Join Elimination Optimizations? | Derived Statistics Propagated Between Child and Parent Tables? | Pros | Cons |
|---|---|---|---|---|---|---|---|---|
| Standard Referential Integrity Constraint | REFERENCES | ANSI/ISO SQL:2011 compliant. Integrity enforcement done using a Referential Index (see Sizing a Reference Index Subtable). |
Yes | Row | Yes | Yes |
|
Not efficient for medium to large updates because it is enforced one row at a time. |
| Batch Referential Integrity Constraint | REFERENCES WITH CHECK OPTION | Teradata extension to ANSI/ISO
SQL:2011 standard. All or nothing for update operations. Enforced using the following methods:
|
Yes | Implicit transaction | Yes | Yes | Efficient for medium to large updates because uses the Optimizer to determine best way to make the join. |
|
| Referential Constraint (“soft referential constraint”) |
REFERENCES WITH NO CHECK OPTION | Teradata extension to
ANSI/ISO SQL:2011 standard. No RI enforcement by Vantage. You must ensure the integrity of the relationship yourself. Not necessary to define a UNIQUE constraint for the primary or alternate key column set. |
No | None | Yes | Yes | No cost for enforcing RI or uniqueness. |
|
| Temporal Relationship Constraint (“TRC constraint”) | For more information on temporal relationship constraints, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182. | |||||||
- A row having a non-null value for a referencing column can exist in a table even when no equivalent parent table value exists.
- A row can, in some circumstances, match multiple rows in
its parent table when the referenced and referencing column values are
compared.
This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See the list of rules for Referential Constraints later in this topic for details.
Referential Constraints do not enforce primary key-foreign key constraints between tables, so they avoid the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints should be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.
For more information see Inclusion Compatibilities and the information about CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Rules for Both Column-Level and Table-Level Foreign Key Constraints
- Teradata does not support the following ANSI/ISO SQL:2011 referential action options for FOREIGN KEY ... REFERENCES constraints:
- MATCH {FULL | PARTIAL | SIMPLE}
- ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
- ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
- The specified column_name list must be identical to a set of columns in the referenced table that is defined as one of the following.
- PRIMARY KEY
- UNIQUE
- A unique secondary index
This rule is not mandatory for Referential Constraints. See Foreign Key Constraints for details.
The specified table_name refers to the referenced table, which must be a user base data table, not a view.
- A maximum of 64 foreign keys can be defined for a table and a maximum of 64 referential constraints can be defined for a table.
Similarly, a maximum of 64 other tables can reference a single table. Therefore, there is a maximum of 128 Reference Indexes that can be stored in the table header per table.
The limit on Reference Indexes includes both references to and from the table and is derived from 64 references to other tables plus 64 references from other tables = 128 Reference Indexes.
However, only 64 Reference Indexes are stored per Reference Index subtable for a table, those that define the relationship between the table as a parent and its children.
Column-level CHECK constraints that reference alternate keys in other tables do not count against this limit.
- Each individual foreign key can be defined on a maximum of 64 columns.
- Note the following attributes of foreign key constraints:
- They can be null.
- They are rarely unique.
An example of when a foreign key would be unique is the case of a vertical partitioning of a logical table into multiple tables.
- Each column in the foreign key constraint must correspond with a column of the referenced table, and the same column name must not be specified more than once.
- The referencing column list should contain the same number of column names as the referenced column list. The ith column of the referencing list corresponds to the ith column identified in the referenced list. The data type of each referencing column must be the same as the data type of the corresponding referenced column.
- The user issuing the CREATE TABLE request that specifies a foreign key constraint must either have the REFERENCE privilege on the referenced table or on all specified columns of the referenced table.
- If REFERENCES is specified in a column_constraint,then table_name defines the referenced table. Note that table_name must be a base table, not a view.
- Referential constraints are not supported for global temporary, global temporary trace, or volatile tables.
- While it is possible to create a child table at a time that its parent table does not yet exist, a REFERENCES constraint that makes a forward reference to a table that has not yet been created cannot qualify the parent table name with a database name.
In other words, the forward-referenced parent table that has not yet been created must be assumed to be “contained” in the same database as its child table that is currently being created.
- You cannot define a foreign key constraint on a row-level security-protected column.
Rules for Column-Level Foreign Key Constraints Only
- You cannot specify the keywords FOREIGN KEY or specify a
referencing column set in the definition of a column-level foreign key
constraint.
The referencing column for a column-level foreign key constraint is the column on which the foreign key constraint is defined by default.
- You cannot define a multicolumn foreign key constraint using the column-level foreign key syntax.
- If you omit column_name, the referenced table must have a single-column primary key, and the specified foreign key column references that primary key column of the referenced table.
- If you specify column_name, it must refer to the
single-column primary key of the referenced table or to a single-column
alternate key in the referenced table defined as UNIQUE.
This rule does not apply for Referential Constraints. In such cases, the candidate key acting as the primary key in the referenced table need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.
However, the candidate key in the relationship actually must always be unique even if it is not explicitly declared to be so. This is true by definition. See Identifying Candidate Primary Keys and PRIMARY KEY Constraints for details.
The uniqueness rule for candidate keys is necessary because the Optimizer always assumes that candidate keys are unique when it generates its query plans, and if that assumption is not true, it is not only possible to produce incorrect results to queries, but to corrupt databases. Because of this, you must always ensure that all candidate key values are unique even if they are not explicitly declared to be so.
As is always true when you specify Referential Constraints, you must assume responsibility for ensuring that any candidate key in a referential integrity relationship is unique, just as you must assume responsibility for ensuring that the referential relationship it anchors holds true in all cases, because Vantage does not enforce either constraint.
Rules for Table-Level FOREIGN KEY ... REFERENCES Constraints Only
- You must specify a complete FOREIGN KEY (referencing_column_set) REFERENCES (referenced_table_name) specification for the foreign key definition.
- If you do not specify the optional referenced_column_set in the foreign key definition, Vantage assumes that the columns in the referencing column set have the identical names as the implied columns in the referenced column set.
- If the referenced column set columns do not have the same names as their counterparts in the referencing_column_set list, you must specify their names using the FOREIGN KEY (referencing_column_set) REFERENCES referenced_table_name (referenced_column_set) syntax.
- The optional keywords WITH CHECK OPTION and WITH NO CHECK OPTION define a foreign key constraint as being either a batch referential integrity constraint or a Referential Constraint, respectively.
If you specify neither set of keywords, the foreign key constraint defines a traditional foreign key constraint by default.
- A maximum of 100 table-level constraints can be defined for any table.
Rules for FOREIGN KEY ... REFERENCES Referential Constraints Only
Other than their not actually enforcing referential integrity, most of the rules for Referential Constraints are identical to those documented by Rules for Both Column-Level and Table-Level Foreign Key Constraints and by Rules for Table-Level FOREIGN KEY ... REFERENCES Constraints Only.
- You can specify standard RI, batch RI, and Referential Constraints in the same table, but not for the same column set.
- You can specify Referential Constraints for both of the
following constraint types:
- FOREIGN KEY (FK_column_set) REFERENCES (parent_table_PK_column_set)
- (NFK_column_set) REFERENCES (parent_table_AK_column_set)
where NFK indicates non-foreign key and parent_table_AK_column_set indicates an alternate key in the parent table.
- Referential Constraint references count toward the maximum of 64 references permitted for a table referenced as a parent even though they are not enforced by the system.
- INSERT, DELETE, and UPDATE requests are not permitted against tables with unresolved, inconsistent, or non-valid Referential Constraints. This rule is identical to the rule enforced for standard and batch RI.
- The candidate key acting as the primary key in the referenced table in the constraint need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.
Referential Integrity Constraint Checks
- A referential integrity constraint is added to a populated table.
- A row is inserted or deleted.
- A parent or foreign key is modified.
The following table summarizes these actions.
| Action Taken | Constraint Check Performed |
|---|---|
| INSERT into parent table | None. |
| INSERT into child table | Must have matching parent key value if the foreign key is not null. |
| DELETE from parent table | Abort the request if the deleted parent key is referenced by any foreign key. |
| DELETE from child table | None. |
| UPDATE parent table | Abort the request if the parent key is referenced by any foreign key. |
| UPDATE child table | New value must match the parent key when the foreign key is updated. |
Overhead Costs of Standard Referential Integrity
By implementing standard referential integrity, you incur certain overhead costs that can have a negative effect on performance. The following table lists the various referential integrity overhead operations that affect performance.
| Operation | Description | |||
|---|---|---|---|---|
| Insert, update, or delete rows in a referencing table. | Overhead is similar to
that for USI subtable row handling. A redistributed spool for each reference is dispatched to the AMP containing the subtable entry. BYNET traffic incurs the majority of the cost of this operation. |
|||
| Insert a row into a referencing table | A referential integrity
check is made against the Reference Index subtable.
|
|||
| Delete a row from a referencing table | A referential integrity
check is made against the Reference Index subtable and its count for
the referenced field is decremented. When the count decrements to 0, then the subtable entry for the Referenced field is deleted. |
|||
| Update a referenced field in a referencing table | Overhead is similar to
that for changing the value of a USI column. A referential integrity check is made against the Reference Index subtable. Both the inserting-a-row and deleting-a-row operations execute on the Reference Index subtable, decrementing the count of the old Referenced column value and incrementing the count of the new Reference column value. |
|||
| Delete a row from a referenced table | The Reference Index
subtable is checked to verify that the corresponding Referenced
column does not exist. When nonexistence is confirmed, the row is
deleted from the Referenced table. No BYNET traffic is involved because the Referenced column is the same value in the Referenced table and the Reference Index subtable. |
|||
PRIMARY KEY Constraints
PRIMARY KEY constraints specify the primary key column set in a table definition. Vantage uses primary keys to enforce both row uniqueness and referential integrity.
Whether a PRIMARY KEY constraint is treated as a column-level constraint or a table-level constraint depends on whether the primary key is simple or composite.
- Only one primary key can be defined per table.
- The following table explains the column limits for column-level and table-level primary key constraints.
| IF the PRIMARY KEY constraint is … | THEN you must define it at this level … |
|---|---|
| simple, or defined on a single column | column. You can define a simple PRIMARY KEY constraint at table-level, but there is no reason to do so. |
| composite, or defined on multiple columns | table. Defining a table-level PRIMARY KEY constraint is the only way you can create a multicolumn primary key. |
- Defining a primary key for a table is never required, though it is recommended for documentation purposes as part of a policy of enforcing data integrity in those cases where the logical primary key is not chosen to be the unique primary index.
- A primary key can be defined on a maximum of 64 columns.
- A PRIMARY KEY constraint cannot be defined on the same column set as the set used to define the nonunique primary index for a table.
| PRIMARY KEY constraints are treated as … | When the primary key is defined on this many columns … |
|---|---|
| column-level constraints | 1 |
| table-level constraints | > 1 |
- A primary key constraint can be defined on the same columns
as a unique secondary index or unique primary index. A primary key is
implemented as follows:
- If the table is defined explicitly with a primary index or unique primary index on different columns than the primary key columns or if the table is defined as NO PRIMARY INDEX or PRIMARY AMP INDEX, then the primary key is implemented as a unique secondary index.
- If a nonpartitioned, nontemporal table does not have an explicit primary index, explicit primary AMP index, or explicit NoPI and no USI or UNIQUE constraint is specified on the same columns as the primary key, then the default is a unique primary index on the same columns as the primary key constraint.
Note that in physical database design, candidate keys, whether chosen to be a primary key or not, are always defined internally as either a UNIQUE NOT NULL secondary index or as a single-table join index.
- You cannot define a PRIMARY KEY on a row-level security-protected column.
UNIQUE Constraints
UNIQUE constraints specify that the column set they modify must contain unique values. Vantage implements UNIQUE constraints as either a unique primary index, a unique secondary index, or as a single-table join index.
- UNIQUE constraints should always be specified with a NOT
NULL attribute specification.
Otherwise, it is possible for a single null to be inserted into a uniquely constrained column. The semantics of a unique null “value” are uncertain at best, and almost certainly violate the intent of the uniqueness constraint.
- UNIQUE constraints can be defined at column-level (simple)
or at table-level (composite).
The following table explains the column limits for column-level and table-level primary key constraints.
| IF the UNIQUE constraint is … | THEN you must define it at this level … |
|---|---|
| simple, or defined on a single column | column. You can define a simple UNIQUE constraint at table-level, but there is no reason to do so. |
| composite, or defined on multiple columns | table. Defining a table-level constraint is the only way you can create a multicolumn UNIQUE constraint. |
- Column-level UNIQUE constraints refer only to the column on which they are specified.
- Table-level UNIQUE constraints can be defined on multiple columns by specifying a column name list.
- A table-level UNIQUE constraint can be defined on a maximum of 64 columns.
- A maximum of 100 table-level constraints can be defined for any table.
- You can define a UNIQUE constraint for a column-partitioned table.
- If a row-level security-protected table is defined with a UNIQUE constraint, enforcement of the constraint does not execute any security policy defined for the table. UNIQUE constraints are applicable to all rows in a row-level security-protected table, not just to user-visible rows.
- You cannot define a UNIQUE constraint on a row-level security constraint column of a row-level security-protected table.