Re: select based on multi-column primary keys

--

AFAIK: You cannot have multiple primary keys. How would you know which one
is the actual key ?

FYI: What you are really talking about are table contraints... When you have
multiple unique column constraints -- they are generally referred to as
"table constraints" not multiple primary keys... Unique nmulti-column table
constraints generally use an internal set of rules and triggers and an
index.

My advice would be to alter your table structure so that you have a "real"
PK not table constraints -- that would make it searchable....

There's this nifty little thing called a "sequence"... they make great
PK's.... BTW: If you implement a true PK... you can still retain your
UNIQUE(col1, col2, col3, ...) table constraints.

Regards,
Gregory P. Patnude
Vice President - Applications & Innovations Group

iDynaTECH, Inc
120 North Pine Street
STC - Suite 162
Spokane, WA 99202

(509) 343-3104 [voice]
http://www.idynatech.com

"mawrya" <mawrya(at)furthernorth(dot)net> wrote in message
news:45B16602(dot)2080508(at)furthernorth(dot)net(dot)(dot)(dot)
>I have set up a table with a multi-column primary key constraint:
>
> CREATE TABLE iopoints
> (
> enclosureid numeric(3) NOT NULL,
> pointid char(4) NOT NULL,
> equipmentgroup varchar(64) NOT NULL,
> deviceid varchar(8),
> devicetype varchar(24),
> operationdesc varchar(64) NOT NULL,
> entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
> time zone,
> systemid numeric(3) NOT NULL,
> CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
> )
> WITHOUT OIDS;
>
> If I had a row in the table where systemid=123, enclosureid=ab,
> pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
>
> I now want to run a select based on the Primary Key, something like:
>
> SELECT * FROM iopoints WHERE ID = 123ab56
>
> Is something like this even possible? Or am I forced to do:
>
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND
> pointid=56
>
> I have searched high and low but can not find a syntax example of how to
> select based on a multi-column primary key, any tips?
>
> Thanks,
>
> mawrya
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>