I ran across this rather interesting article that describes my issue
exactly:



http://www.ibphoenix.com/main.nfs?a=ibphoenix
<http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;KNOWLEDGEBASE;ID='258>
&l=;KNOWLEDGEBASE;ID='258'



It doesn't really explain to me, though, why it happened to me in the case I
was trying. I was trying to add a primary key, which means I can't specify
a specific name for the index. Is the system-generated name, therefore,
colliding with a pre-existing name? I don't think this should happen, but
unless someone knows for sure, I'll take Helen's advice and dig into the
meta data more.



I'm not changing the data in the table at all, just trying to define a
primary key as my 3rd party software is looking for it. I could drop the
existing unique index and try again, since it is the same field and defining
a primary key has the same effect, if that would resolve the issue. The
table is rarely if ever updated anyway.



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Sunday, November 02, 2008 1:37 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Error while trying to add a primary key



At 03:43 AM 3/11/2008, you wrote:

>A query on RDB$INDICES returns this information about my table:
>
>
>
>"PK_MEDIATYPE ", "MEDIATYPE ", 1, 1,
><Null>, 1, <Null>, <Null>, <Null>, 0, <Null>, <Null>, 0
>
>
>
>Given the index name, I assume there already is a Primary Key (PK) on this
>column, which is what I want.

No, the table doesn't have a primary key. This query result tells you that
the index is unique (the fourth field) and that it is not system-defined
(that's the zero fourth from the end). On an index created for a primary key
constraint, that number would be > 0. Since Fb 1.5, named constraints would
pass the name of the constraint as the name of the system-generated index;
the default behaviour (and the only behaviour pre 1.5) is for the system to
name the PK's index as RDB$PRIMARYnnnn (where the nnnn part is just an
incrementing number).

>However, a call to SQLPrimaryKeys(), returns zero rows.
>
>Thus, I'm going to have to report this as a bug.

The designer of your product probably wouldn't see it as a bug, as long as
his software "works". It's likely the database was converted from some old
ISAM database engine where a "primary key" is not implemented as a
constraint but by reference to an associated file. It's not unusual for
people doing such conversions for home-baked applications to assume that a
unique index is "enough" to make a column behave like a constraint key.

> My program needs to know what the primary key field is, and that is the

purpose of calling SQLPrimaryKeys().

It's going to be a problem relying on knowing what a PK field is if you have
tables that don't have any.

It looks as if you need to get your hands on a native Firebird tool and
extract, or at least properly inspect, the metadata of your database. At the
very least, if you're relying on the presence of a constraint, you need to
know which tables can be expected to return a result to this function. In
isql (in Firebird's ..\bin\ directory) you can do a metadata extraction, but
this does require an understanding of the data definition language (DDL).
You'd get the info more easily with a free GUI utility tool such as
Flamerobin (www.flamerobin.org) or IB_SQL (www.ibobjects.com). Both of these
tools (along with many others you can find in the Contributed Downloads area
at www.ibphoenix.com) use the native Firebird API and are not inhibited by
generic interface limitations.

./heLen





[Non-text portions of this message have been removed]