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@furthernorth.net> wrote in message 
news:45B16602.2080508@furthernorth.net...
>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
>