At 10:21 AM 19/11/2008, you wrote:

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

Can't get to that article using your link, but those pieces from the Knowledgebase database date back to IB 5.x days, anyway, when you had no choice but a system-generated name for constraint indexes....

>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.

Wrong.

> Is the system-generated name, therefore, colliding with a pre-existing name?

If you create a named PK or FK constraint, the supporting index gets the same name as the constraint. If I recall correctly, you were getting a uniqueness violation on RDB$.Indices.RDB$Index_Name. So that was telling you that there was already an index called <whatever-the-name-you-gave-to-the-constraint>. It doesn't mean that a primary key exists already: it could be just a unique index with that name or perhaps the supporting index for a UNIQUE constraint of that 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.

You won't answer your questions if you don't. You are looking for a pair of black socks in the dark until you do.

>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.

You could try to drop the index. If it is "just an index" then it will be allowed. If it really is the supporting index for a constraint, it won't be allowed. But it's *one way* to find out whether your 3rd-party software is really asking the right question.

If you *can* drop the index, you will know it is not a constraint index. You can then proceed to do an ALTER TABLE and add the *NAMED* PRIMARY KEY constraint plus the index of the same name. Don't forget to commit your transaction between the two operations.

Just don't overlook the possibility that the existing index is the supporting index for a UNIQUE constraint of that name. A UNIQUE constraint *is* a constraint but it is not a primary key.

Another possibility is that the index is not a single-key index and also not a genuine constraint index. If you don't know the history of the database structure, the possibility at least exists that it was just a "dumb conversion" from a legacy Paradox or dBase database, where "primary key" is implemented by stringing a unique index across a hierarchy of keys. Such databases do, unfortunately, exist...

> The table is rarely if ever updated anyway.

The database engine doesn't know that. ;-)

./heLen

>
>
>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]
>
>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Yahoo! Groups Links
>
>
>