All Forums Database
tdreturn 25 posts Joined 10/12
01 Apr 2015
MULTISET table from dbc.tables?

Is there a column I can tap into to figure out if a table is a multiset. RequestText in DBC.Tables is not a good column as it has the latest requesttext, so if the table was altered it has that DDL and not the CREATE TABLE statement? The CheckOpt column also does not seem to be a good indication of a MultiSet table. We are on the Release. 

tomnolan 594 posts Joined 01/08
01 Apr 2015

There was another forum thread about this topic a year ago:
The Teradata Database Data Dictionary Reference book documents the DBC.TablesV.CheckOpt column as indicating whether or not the table allows duplicate rows: Y = Duplicate rows are allowed, N = Duplicate rows are not allowed.
As the old forum thread noted, if CheckOpt contains a Y for the table, then you are guaranteed that the table is a MULTISET table. But if CheckOpt contains an N, it's less clear.
SET table --> CheckOpt = "N"
MULTISET table with no constraint --> CheckOpt = "Y"
MULTISET table with unique constraint added --> CheckOpt = "N"
MULTISET table after constraint dropped --> CheckOpt = "Y"
I don't believe there is a column in the Data Dictionary that directly indicates whether a table is SET versus MULTISET.
A possible workaround would be to use the SHOW IN XML TABLE command. The output from the SHOW IN XML TABLE command will contain a <table> tag with a kind attribute whose value will be "Set" or "Multiset".
<Table ... kind="Set"
<Table ... kind="Multiset"

tdreturn 25 posts Joined 10/12
01 Apr 2015

Thanks Tom

itche_scratche 4 posts Joined 03/07
18 Aug 2015

So how can i tell the difference between SET table with UPI and Multiset table with UPI?
They both will have CheckOpt = "N", and both will have UniqueFlag = "Y" in dbc.indicesv.

You must sign in to leave a comment.