Re: Re: Primary Key

Hmm, your using ColdFusion, so that goes through the ODBC driver, which
picks up the 'primary key' by looking for an index named 'foo_pkey',
I think. Ah, here it is:

in interfaces/odbc/info.c:

sprintf(tables_query, "select ta.attname, ia.attnum"
" from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i"
" where c.relname = '%s_pkey'"
" AND c.oid = i.indexrelid"
" AND ia.attrelid = i.indexrelid"
" AND ta.attrelid = i.indrelid"
" AND ta.attnum = i.indkey[ia.attnum-1]"
" order by ia.attnum", pktab);

So, don't name the primary key constraint, or name it 'something_pkey'
and you should be fine. Something's falling back to trying to use
oid if it can't find a primary key: I'm note sure if that's inside the
ODBC driver, or in ColdFusion.

Hmm, seems we have other Access specific hacks in the ODBC driver:

/*
* I have to hide the table owner from Access, otherwise it
* insists on referring to the table as 'owner.table'. (this
* is valid according to the ODBC SQL grammar, but Postgres
* won't support it.)
*
* set_tuplefield_string(&row->tuple[1], table_owner);
*/

I bet PgAdmin would like to have that info.

Ross

On Tue, Jun 19, 2001 at 06:11:12PM -0400, P. Dwayne Miller wrote:
> My bad on the syntax of all three. I used your syntax (which is what I had originally used) and
> got the same results with the \d command that you show.
>
> I'm only using Cold Fusion to read data from the resulting table, not create the table... and I
> still get an error when I have created the primary key using the table constraint syntax. Cold
> Fusion is reporting that the primary key has been defined for the column oid. Using the correct
> syntax with the first two CREATE TABLE statements, Cold Fusion reports the primary key field as
> msgid.
>
> Thanks for your reply,
> Dwayne
>