Thread: enforcing with unique indexes..
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.
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.
> > 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
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)