From: | "codeWarrior" <gpatnude(at)hotmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: select based on multi-column primary keys |
Date: | 2007-01-20 03:45:40 |
Message-ID: | eos326$13fv$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
--
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2007-01-20 04:57:37 | Re: select based on multi-column primary keys |
Previous Message | Bruno Wolff III | 2007-01-20 01:45:24 | Re: select based on multi-column primary keys |