Given the following CREATE TABLE instructions...
1)
CREATE TABLE message
( int4 msgid PRIMARY KEY NOT NULL, text msgtext
);
2)
CREATE TABLE message
( int4 msgid not null, text msgtext, PRIMARY KEY (msgid)
);
3)
CREATE TABLE message
( int4 msgid not null, text msgtext, CONSTRAINT cons_001_pk PRIMARY KEY on (msgid)
);
The first two actually create a PRIMARY KEY on msgid. The third seems
to have a PRIMARY KEY on 'oid', not 'msgid', though it does create a
unique index on 'msgid'. One of the applications I'm using (Cold
Fusion) looks for the PRIMARY KEY and checks that I have included that
column(s) in my data statement.
The first two work, the third does not. Cold Fusion reports that I did
not provide 'oid' as one of the data elements.
Cold Fusion is accessing the database using ODBC.
Database is Postgres v7.1.1 on Red Hat Linux 7.0
I'm not looking for a fix as I can create the table using the syntax
that gives the expected results, but just wanted to alert someone that
there is some inconsistency in the way a PRIMARY KEY is used or
designated.
BTW, I did not try the COLUMN CONSTRAINT syntax.
Thanks