View previous topic :: View next topic
|
Author |
Message |
sakthi_ksv
New User
Joined: 20 Jul 2006 Posts: 48 Location: Chennai
|
|
|
|
Hi,
Is it possible to find the primary key of a table by querying the SYSIBM tables? If so how?? I was asked this question in an interview. I wish to know whether this is possible and the solution also.
Thanks and Regards,
Sakthi. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
search the forums the answer has been replied to many times |
|
Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007 Posts: 1287 Location: Chennai, India
|
|
Back to top |
|
|
sakthi_ksv
New User
Joined: 20 Jul 2006 Posts: 48 Location: Chennai
|
|
|
|
hi,
Through one of the forum given by Aaru I tried to find the PK using the following query
SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'creater id'
AND TBNAME = 'table-name'
AND KEYSEQ > 0
ORDER BY KEYSEQ;
None of the columns of tables have KEYSEQ >0. Does that mean that all the tables wont have a PK. Also none of the tables have entries in SYSKEYS or SYSINDEXES tables. So does that mean that there is no PK or index in my application??
special thanks to those who helped me in searching the forum to find the solution.
Thanks and Regards,
Sakthi. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
sakthi_ksv wrote: |
hi,
Through one of the forum given by Aaru I tried to find the PK using the following query
SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'creater id'
AND TBNAME = 'table-name'
AND KEYSEQ > 0
ORDER BY KEYSEQ;
None of the columns of tables have KEYSEQ >0. Does that mean that all the tables wont have a PK. Also none of the tables have entries in SYSKEYS or SYSINDEXES tables. So does that mean that there is no PK or index in my application??
special thanks to those who helped me in searching the forum to find the solution.
Thanks and Regards,
Sakthi. |
Are you sure you are using the correct values for creator-id and table-name. Tables with no Primary Keys and no indexes sounds a little strange. |
|
Back to top |
|
|
sakthi_ksv
New User
Joined: 20 Jul 2006 Posts: 48 Location: Chennai
|
|
|
|
Ya, I am sure... I am using right values. Does that mean that there is no PK for the tables. If so if there is anyother means to know the PK??
Please let me know.
Thanks in advance,
Sakthivel. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
It is not likely that there are no primary keys for multiple tables.
Try the query without naming the creator or the table name and see how many primary keys are identified. If none are found, it is almost surely a problem with the query. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
Why wouldn't you be going against SYSINDEXES to find the name of the primary key on the table. Then finding the columns in that index? |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
As Steve said, The following query on SYSIBM.SYSINDEXES can also be used for finding out the primary key.
SELECT TBCREATOR, TBNAME, NAME, CREATOR, DBNAME, INDEXSPACE FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = ’creater id’ AND TBNAME = ’table name’ AND UNIQUERULE = ’P’; |
|
Back to top |
|
|
|