Thread: select based on multi-column primary keys
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),
devicetypevarchar(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
On Fri, Jan 19, 2007 at 16:44:50 -0800, mawrya <mawrya@furthernorth.net> wrote:
> I have set up a table with a multi-column primary key constraint:
>
> 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
SELECT * FROM iopoints WHERE systemid=123 AND enclosureid='ab' AND pointid=56
While in theory you could concatenate the columns and test that against a
particular value, you probably don't want to do that. (If you do, use a
functional index.)
-- 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 >
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid='ab' AND pointid=56 A slight variation of the syntax would be: select * from iopoints where (systemid, enclosureid, pointid) = (123,'ab',56); this table and fields sounds alot like a control system be being modeled. :-) Regards, Richard Broersma Jr.
On Fri, Jan 19, 2007 at 07:45:40PM -0800, codeWarrior wrote: > AFAIK: You cannot have multiple primary keys. How would you know which one > is the actual key ? You can have a multi-column primary key, though. That's a perfectly legitimate approach. > FYI: What you are really talking about are table contraints... When you have No, it's a multi-column primary key. > My advice would be to alter your table structure so that you have a "real" > PK not table constraints -- that would make it searchable.... This is already searchable. What you are talking about is not a real primary key, but an artificial one. The OP already has a real primary key. SQL purists think artificial primary keys mean that you haven't done enough normalisation. I'm going to remain silent on that topic, though, so that we don't get a Thread That Does Not End :) A -- Andrew Sullivan | ajs@crankycanuck.ca If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA
No... its a table constraint using a unique, implicit index on the listed columns. . PRIMARY KEY ( column [, ...] ) The PRIMARY KEY table constraint is similar to the PRIMARY KEY column constraint. As a table constraint, PRIMARY KEY allows multiple columns to be defined in a parenthetical expression, separated by commas. An implicit index will be created across columns. The combination of values for each column specified must therefore amount to only unique and non-NULL values, as with the PRIMARY KEY column constraint. hehehe.... "Andrew Sullivan" <ajs@crankycanuck.ca> wrote in message news:20070121152838.GB3533@phlogiston.dyndns.org... > On Fri, Jan 19, 2007 at 07:45:40PM -0800, codeWarrior wrote: >> AFAIK: You cannot have multiple primary keys. How would you know which >> one >> is the actual key ? > > You can have a multi-column primary key, though. That's a perfectly > legitimate approach. > >> FYI: What you are really talking about are table contraints... When you >> have > > No, it's a multi-column primary key. > >> My advice would be to alter your table structure so that you have a >> "real" >> PK not table constraints -- that would make it searchable.... > > This is already searchable. What you are talking about is not a real > primary key, but an artificial one. The OP already has a real > primary key. SQL purists think artificial primary keys mean that you > haven't done enough normalisation. I'm going to remain silent on > that topic, though, so that we don't get a Thread That Does Not End > :) > > A > > > -- > Andrew Sullivan | ajs@crankycanuck.ca > If they don't do anything, we don't need their acronym. > --Josh Hamilton, on the US FEMA > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Tue, Jan 23, 2007 at 01:07:19PM -0800, codeWarrior wrote: > No... its a table constraint using a unique, implicit index on the listed > columns. . Sure. But since these get added as table constraints after dump anyway, I'm not sure I see why it makes a difference. In any case, the point is that what you call a "real" primary key is an artificial one, and what SQL weenies would think is a "real" primary key is in fact, a natural primary key based on the data in the table. That may well be multiple columns of data. (I know of one really nasty case where it was five. Yes, the application needed rewriting. But it was normalised :) A -- Andrew Sullivan | ajs@crankycanuck.ca A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton