|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/18/2009 11:48:19 PM
Posts: 2,
Visits: 4
|
|
Hi,
Actually,I am new to this teradata but i know basic concepts.
we will create indexes when we create tables in teradata and will not mention primary keys.
how to find a primary key in existing teradata table?
Please let me know the solution.
suni
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/8/2009 1:16:14 PM
Posts: 79,
Visits: 105
|
|
Hi,
While creating a table if we specify Primary Key then this will be created as either UPI or USI.(If we are not specified any PI, then PK will be chosen as PI ie., UPI, else USI).
you can also use DBC views (Indices) to identify the type.
Regards,
Balamurugan
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 7/29/2009 9:23:21 AM
Posts: 27,
Visits: 34
|
|
Primary Keys can be enforced with by unique indexes or by using a UNIQUE or PRIMARY KEY constraint on the table. The first goal of your Primary Index is to identify the best access path to the data and/or join strategy in the overall data model not necessarily to enforce the integrity of the data in the table.
There are tables in DBC that will help you identify both indexes and table constraints.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/12/2009 10:59:03 AM
Posts: 23,
Visits: 24
|
|
If you show the definition of the table either by right clicking on the table listed in the tree view or type SHOW TABLE dbname.tablename;
it will display the definition of the table with all indices including the default one which was created automatically.
Thanks,
Uma
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
As a practice, database is not the right place to sort out what is the primary key of the table, that should be maintained in the data model.
If you explicitly define a primary key in the table definition, teradata will maintain it with indextype = 'K' in the DBC.Indices view.
Otherwise you are stuck to looking for unique indexes via uniquenessflag = 'y' in dbc.indices ( club on the index number for composite indexes) ...
So what will you do if there are two unique indexes in the table ? .... that's why it's better off maintained in the model.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/5/2009 6:07:39 AM
Posts: 9,
Visits: 10
|
|
In order to find the Primary key in a table,
Condition for Primary Key:
* the column should be not-null
* it should give unique record
Suppose, we are having 10 columns in a table(dw_users). choose one not-null column for eg:user_id column in dw_users table.
Query to find P.K
-------------------
sel user_id,count(*) from dw_users group by 1 having count(*)>1 -- give unique record means user_id is the P.k for this table.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/5/2009 12:34:37 PM
Posts: 10,
Visits: 26
|
|
Hello,
PI and PK are different. You dont have to look for PK in physical database design cos PK is a logical datamodel term to uniquely identify a row , whereas PI is a physical database design term and it is used by amp to access/distribute rows to teradata DB.
The below sql may be handy to guess your PK though :
sel colname ,count(*) from DB.tablename group by 1 having count(*)>1
note: you need to try out different combo of columns if your PK is a composite one.
Cheers,
Samp
|
|
|
|