<Richard_D_Levine@xxxxxxxxxxxx> wrote in message news:OFC81E21F9.6AEDE7BE-ON0525703F.006F22CF-0525703F.006F5C10@xxxxxxxxxxxxxxxxx > > > pgsql-general-owner@xxxxxxxxxxxxxx wrote on 07/15/2005 02:49:09 PM: > >> On Fri, Jul 15, 2005 at 20:08:32 +0300, >> Andrus <eetasoft@xxxxxxxxx> wrote: >> > >> > So I'll think still continuing to use null as unrestricted department >> > access. >> > >> > Is it reasonable to create unique constraint using >> > >> > CREATE UNIQUE INDEX user_id_permission_id_department_id_unique_idx >> > ON permission (user_id, permission_id, COALESCE(department_id,'ALL')) >> >> If you are going to do this a partial index is a better way to go. >> Something like: >> CREATE UNIQUE INDEX user_id_permission_id_null ON permission >> WHERE department_id IS NULL; >> >> However either of these let you insert and entry for "ALL" while also >> having entries for individual departments. > > That's a lot of overhead for doing something very simple, like defining a > department key that means ALL and a row in the foreign table for it to > point to. Maintaining indices is a nontrivial performance trade-off. Yes, adding department ALL may be simpler solution. However, I reference department table from many other tables. In those other tables, department ALL is NOT ALLOWED. If I add ALL to department table, I must restrict all other tables of having ALL department. This is a big work and cannot be done nicely in Postgres. So I need to allow specify ALL department in privilege table without changing department table. Andrus. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
