Обсуждение: enforcing with unique indexes..

Поиск
Список
Период
Сортировка

enforcing with unique indexes..

От
"Rajesh Kumar Mallah."
Дата:
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.




Re: enforcing with unique indexes..

От
"Rajesh Kumar Mallah."
Дата:

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.




Re: enforcing with unique indexes..

От
Дата:
>
> 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




Re: enforcing with unique indexes..

От
Jean-Luc Lachance
Дата:
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)