17.00 - CREATE CONSTRAINT Syntax Elements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)
constraint_name
The name of the row-level security constraint object. The constraint name becomes a column name if you include the constraint in the definition for a table, view, or index.
Constraint names must follow the system rules for database object names, and must be unique among:
  • constraints defined in the system
  • column names defined in any table that includes the constraint
For information on naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
data_type
The data type for constraint_name. One of the following:
  • SMALLINT

    Use the SMALLINT data type only for hierarchical (level) constraints.

    The range of valid constraint values is from 1 to 10,000 inclusive.

  • BYTE(n)

    Use the BYTE(n) data type only for non-hierarchical (category) constraints.

    n represents the number of bytes in the constraint value string for a table row. If you do not specify a value for n, the default is 1 byte. The maximum value for n is 32.

    To allow for more categories than the column could otherwise contain, the system automatically expresses each non-hierarchical value as a unique bit position, which allows a table row to contain up to 256 distinct values for the constraint column.

The data type you specify becomes the data type of the associated system-created constraint column in any table to which the constraint is assigned.
For detailed information about the uses of the SMALLINT and BYTE data types when designing classification systems and security constraints, see "Implementing Row Level Security” in Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.
NOT
Specifies that a constraint value for a specific table row cannot be null.
NULL
A constraint value for a specific table row can be null.
The default is NULL.
VALUES
The string to follow is a set of name:value pairs, which defines either a hierarchical (level) or non-hierarchical (category) classification system.
The set of name:value pairs defines the set of classifications for the constraint.
The maximum number of name :value pairs depends on whether the constraint is hierarchical (10,000) or non-hierarchical (256).
name
The name of a member of the classification system being defined for the constraint.
One or more of the classification names for the constraint can be assigned to a user or profile to define their access privileges on tables that contain the constraint.
value
The value code for the corresponding name in a name:value pair.
Value codes appear for each row in the corresponding constraint column of tables protected by the constraint.
For details about name:value pairs and how the value is determined for a table row, see “Implementing Row-Level Security” in Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.
DELETE, INSERT, SELECT, UPDATE
The SQL operations that can be enforced by a constraint.
You must specify between 1 and 4 SQL operations per constraint definition. Each specified SQL operation must be followed by the SYSLIB.function_name that enforces the operation.
You cannot specify an SQL operation type more than once in a constraint.
SYSLIB.function_name
The name of the UDF that enforces the corresponding DELETE, INSERT, SELECT, or UPDATE operation for the constraint.
The specified UDF must be contained within the SYSLIB database at the time you specify it in the constraint definition.
Each UDF named in SYSLIB.function_name specifications for a constraint defines the minimum classification required to perform the associated SQL operation on each row of a table that includes the constraint.
For more information about implementing row-level security, see Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.