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

sql teradata
Cacheing picture Cacheing · Jul 17, 2013 · Viewed 9.9k times · Source

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

dnoeth picture dnoeth · Jul 18, 2013

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.

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More