Board index » delphi » Database Primary Key

Trac Taylor

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Database Primary Key

Hello everyone:

I was wandering, if you have a table name is there anyway to find the
primary key of the table?
I am using interbase.  I saw in the interbase docs that you can do
select RDB$... from Relations....  But I really do not know if this is
the right track.  Thanks for any help that you can give me.

Trac

 
Craig Stunt

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Database Primary Key


Quote
Trac Taylor wrote:

> I was wandering, if you have a table name is there anyway to find the
> primary key of the table?
> I am using interbase.  

SELECT
  S.RDB$FIELD_NAME
FROM
  RDB$RELATION_CONSTRAINTS RC
  INNER JOIN RDB$INDEX_SEGMENTS S
    ON S.RDB$INDEX_NAME = RC.RDB$INDEX_NAME
WHERE
  RC.RDB$RELATION_NAME = :Table_name
  AND RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
  S.RDB$FIELD_POSITION

        Keep in mind that a table might have no primary key, or the primary key
may have more than one field, in which case this query will give you
more than one record back.

        HTH,

        -Craig

--
Craig Stuntz            Vertex Systems Corporation
Senior Developer        http://www.vertexsoftware.com

Trac Taylo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Database Primary Key


Hi Craig:

I tried the code you gave me in the sqlExplorer that comes with delphi.  I
substituted a table name that I know has a primary key and the query return
nothing.  Do you know why this is?  Is this type of query possible to run in the
sqlExplorer?

Thanks

Trac

Quote
Craig Stuntz wrote:
> Trac Taylor wrote:

> > I was wandering, if you have a table name is there anyway to find the
> > primary key of the table?
> > I am using interbase.

> SELECT
>   S.RDB$FIELD_NAME
> FROM
>   RDB$RELATION_CONSTRAINTS RC
>   INNER JOIN RDB$INDEX_SEGMENTS S
>     ON S.RDB$INDEX_NAME = RC.RDB$INDEX_NAME
> WHERE
>   RC.RDB$RELATION_NAME = :Table_name
>   AND RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
> ORDER BY
>   S.RDB$FIELD_POSITION

>         Keep in mind that a table might have no primary key, or the primary key
> may have more than one field, in which case this query will give you
> more than one record back.

>         HTH,

>         -Craig

> --
> Craig Stuntz            Vertex Systems Corporation
> Senior Developer        http://www.vertexsoftware.com

Craig Stunt

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Database Primary Key


Quote
Trac Taylor wrote:

> I tried the code you gave me in the sqlExplorer that comes with delphi.  I
> substituted a table name that I know has a primary key and the query return
> nothing.  Do you know why this is?  Is this type of query possible to run in the
> sqlExplorer?

        Yes; I ran it in SQL explorer before I posted.  The table name will be
case sensitive and needs to be in quotes -- are your sure the case is
correct?

        If it still doesn't work for you, post the SQL exactly as you're
entering it in SQL explorer, and the DDL for the table in question.
FWIW, I'm using IB 5.6.

        HTH,

        -Craig

--
Craig Stuntz            Vertex Systems Corporation
Senior Developer        http://www.vertexsoftware.com

Trac Taylo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Database Primary Key


Hey Craig:

Yes, you were right (ofcourse) I did not have the proper case for the table name.

Thanks alot, I have been trying to find this info for a few months now.

Trac

Quote
Craig Stuntz wrote:
> Trac Taylor wrote:

> > I tried the code you gave me in the sqlExplorer that comes with delphi.  I
> > substituted a table name that I know has a primary key and the query return
> > nothing.  Do you know why this is?  Is this type of query possible to run in the
> > sqlExplorer?

>         Yes; I ran it in SQL explorer before I posted.  The table name will be
> case sensitive and needs to be in quotes -- are your sure the case is
> correct?

>         If it still doesn't work for you, post the SQL exactly as you're
> entering it in SQL explorer, and the DDL for the table in question.
> FWIW, I'm using IB 5.6.

>         HTH,

>         -Craig

> --
> Craig Stuntz            Vertex Systems Corporation
> Senior Developer        http://www.vertexsoftware.com

Other Threads

1. Primary key or not primary key.........

2. find primary key against database server table?

3. Retrieve Primary Keys from Database

4. Primary Keys and Identity on two Databases

5. How to know from Delphi the Primary Key of a table in relational database

6. detecting primary keys, creating databases

7. Paradoxe Database and Primary Key

8. change Primary Key when duplicate key error during post

9. Primary Key vs Unique Key

10. Key violation for auto increment Primary keys