Teradata 简明教程

Teradata - Primary Index

主键索引用于指定数据在 Teradata 中所在的位置。它用于指定哪个 AMP 获取数据行。Teradata 中的每个表格都需要定义一个主键索引。如果未定义主键索引，则 Teradata 会自动分配主键索引。主键索引提供了访问数据的最快速方式。主键最多可以有 64 列。

Primary index is used to specify where the data resides in Teradata. It is used to specify which AMP gets the data row. Each table in Teradata is required to have a primary index defined. If the primary index is not defined, Teradata automatically assigns the primary index. Primary index provides the fastest way to access the data. A primary may have a maximum of 64 columns.

主键索引在创建表格时定义。主键索引有 2 种类型。

Primary index is defined while creating a table. There are 2 types of Primary Indexes.

  1. Unique Primary Index(UPI)

  2. Non Unique Primary Index(NUPI)

Unique Primary Index (UPI)

如果将表格定义为具有 UPI，则被视为 UPI 的列不应具有任何重复值。如果插入任何重复值，它们将被拒绝。

If the table is defined to be having UPI, then the column deemed as UPI should not have any duplicate values. If any duplicate values are inserted, they will be rejected.

Create Unique Primary Index

以下示例创建 Salary 表格，其列 EmployeeNo 为唯一主键索引。

The following example creates the Salary table with column EmployeeNo as Unique Primary Index.

CREATE SET TABLE Salary (
   EmployeeNo INTEGER,
   Gross INTEGER,
   Deduction INTEGER,
   NetPay INTEGER
)
UNIQUE PRIMARY INDEX(EmployeeNo);

Non Unique Primary Index (NUPI)

如果将表格定义为具有 NUPI，则被视为 UPI 的列可以接受重复值。

If the table is defined to be having NUPI, then the column deemed as UPI can accept duplicate values.

Create Non Unique Primary Index

以下示例创建员工帐户表格，其列 EmployeeNo 为非唯一主键索引。EmployeeNo 定义为非唯一主键索引，因为一名员工可以在表格中拥有多个帐户：一个用于薪资帐户，另一个用于报销帐户。

The following example creates the employee accounts table with column EmployeeNo as Non Unique Primary Index. EmployeeNo is defined as Non Unique Primary Index since an employee can have multiple accounts in the table; one for salary account and another one for reimbursement account.

CREATE SET TABLE Employee _Accounts (
   EmployeeNo INTEGER,
   employee_bank_account_type BYTEINT.
   employee_bank_account_number INTEGER,
   employee_bank_name VARCHAR(30),
   employee_bank_city VARCHAR(30)
)
PRIMARY INDEX(EmployeeNo);