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
>