Teradata: how can I get the primary key and foreign key definition of a table?

Question

The tables are already there but I don't know how they are crated. Now I want to get the definition of primary key and foreign key of the tables. How can I do that?

Answer 1

There are several ways to get PK/FK info in Teradata, but all might fail if there's no PK/FK defined for a table. And there's a high probability (especialy for FKs) that they're not defined in a Data Warehouse. In that case you need to get some external documentation on your datamodel.

dbc.IndicesV holds all indexes, but there's one row per column per index, e.g. a 4 column PK results in 4 rows, defined in "ColumnPosition" order:

"UniqueFlag" = 'Y' indicates it's a unique index

"IndexType" indicates how it was defined in the Create Table: 'K' = Primary Key, 'U' = Unique Constraint, 'P' = Primary Index, 'S' = Secondary Index

dbc.All_ri_childrenV holds defined FKs.

If it's just for a single table there's "HELP INDEX my_table" for indexes, but in that case i prefer "SHOW TABLE my_table" which returns the full source code.