pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedenforcing with unique indexes..
4+ messages / 3 participants
[nested] [flat]
* enforcing with unique indexes..
@ 2002-10-05 06:49 Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 2 replies; 4+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-10-05 06:49 UTC (permalink / raw)
To: pgsql-sql
Hi ,
can anyone tell me how can i enforce below in a table.
I want that no more that one distinct userid exists for a given group_id
in the table.
ie i want 1 to 1 mapping between group_id and userid so that , there shud not be a
single group_id having more that one kind of userid.
SELECT group_id from eyp_listing group by group_id having count(distinct userid) > 1 ;
always returns empty.
can it be done with some sort of UNIQUE INDEX?
Regds
MAllah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: enforcing with unique indexes..
@ 2002-10-05 07:18 Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-10-05 07:18 UTC (permalink / raw)
To: Bhuvan A <bhuvansql@myrealbox.com>; +Cc: pgsql-sql
Sorry Bhuvan it wont work,
COMPOSITE UNIQUE INDEX will prevent entry of rows like (group_id,user_id)
1 1
1 1
what i want to prevent is this:
1 1
1 2
did you notice the distinct inside the count?
regds
mallah.
On Saturday 05 October 2002 12:36, Bhuvan A wrote:
> > SELECT group_id from eyp_listing group by group_id having
> > count(distinct userid) > 1 ;
> >
> > always returns empty.
> >
> > can it be done with some sort of UNIQUE INDEX?
>
> Yes it can be done using UNIQUE INDEX. Create a composite unique index on
> these 2 fields and it will do the rest. For more details, try
>
> $ \h CREATE INDEX
>
> regards,
> bhuvaneswaran
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: enforcing with unique indexes..
@ 2002-10-05 14:13 dev@archonet.com
parent: Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 0 replies; 4+ messages in thread
From: dev@archonet.com @ 2002-10-05 14:13 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
>
> ie i want 1 to 1 mapping between group_id and userid so that , there
> shud not be a single group_id having more that one kind of userid.
>
> can it be done with some sort of UNIQUE INDEX?
If you want multiple groups but the user should always be the same
1 1 ok
1 1 ok
2 1 ok
3 2 ok
3 3 bad
Perhaps you should normalise a bit further with a separate group_user
table - you could restrict that to being unique on group then.
- Richard Huxton
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: enforcing with unique indexes..
@ 2002-10-07 15:55 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-10-07 15:55 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: pgsql-sql
Try a rule or a triger that checks for
NOT EXISTS ( select 1 from eyp_listing where group_id = New.group_id and
userid != New.userid)
"Rajesh Kumar Mallah." wrote:
>
> Hi ,
>
> can anyone tell me how can i enforce below in a table.
> I want that no more that one distinct userid exists for a given group_id
> in the table.
>
> ie i want 1 to 1 mapping between group_id and userid so that , there shud not be a
> single group_id having more that one kind of userid.
>
> SELECT group_id from eyp_listing group by group_id having count(distinct userid) > 1 ;
>
> always returns empty.
>
> can it be done with some sort of UNIQUE INDEX?
>
> Regds
> MAllah.
>
> --
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)6152172 (221) (L) ,9811255597 (M)
>
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-07 15:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-05 06:49 enforcing with unique indexes.. Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-10-05 14:13 ` dev@archonet.com
2002-10-07 15:55 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-10-05 07:18 Re: enforcing with unique indexes.. Rajesh Kumar Mallah. <mallah@trade-india.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox