Thread: How to create unique constraint on NULL columns
I have table CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, UNIQUE (col1, col2) ); This table allows to insert duplicate rows if col2 is NULL: INSERT INTO test VALUES ( '1', NULL ); INSERT INTO test VALUES ( '1', NULL ); does NOT cause error! How to create constraint so that NULL values are treated equal and second insert is rejected ? Andrus.
On 7/15/05, Andrus <eetasoft@online.ee> wrote: > CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, > UNIQUE (col1, col2) ); > INSERT INTO test VALUES ( '1', NULL ); > INSERT INTO test VALUES ( '1', NULL ); > does NOT cause error! > > How to create constraint so that NULL values are treated equal and second > insert is rejected ? Please read: http://www.postgresql.org/docs/8.0/interactive/indexes-unique.html ...or this list archives. In short: NULL is not equal to NULL. NULL is a state, not a value. Regards, Dawid
"Andrus" <eetasoft@online.ee> writes:
> CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR,
> UNIQUE (col1, col2) );
> This table allows to insert duplicate rows if col2 is NULL:
> INSERT INTO test VALUES ( '1', NULL );
> INSERT INTO test VALUES ( '1', NULL );
> does NOT cause error!
> How to create constraint so that NULL values are treated equal and second
> insert is rejected ?
Rethink your data design --- this behavior is required by the SQL
standard. A unique constraint is defined in terms of a "unique
predicate", which is defined as
2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.
(SQL92 8.9 <unique predicate> general rule 2)
In general NULL should be used to mean "I don't know the value of this
field", not as a special value.
regards, tom lane
>> How to create constraint so that NULL values are treated equal and second >> insert is rejected ? > > Rethink your data design --- this behavior is required by the SQL > standard. I have a table of users permissions by departments CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, department_id CHAR(10) REFERENCES department , UNIQUE ( user_id, permission_id, department_id ) ) permission_id is a permission name: Invoice, Waybill etc. department _id is a code of department whose documents user is authorized to access. if department _id is NULL, user has access to all departments data. By this design it is meaningless to have two records with same user_id and permission_id both having department_id NULL So I want that Postgres does not allow to insert them. How I should rethink this data design to be implemented in CREATE TABLE statement ? Andrus.
On Fri, 2005-07-15 at 10:26, Andrus wrote: > >> How to create constraint so that NULL values are treated equal and second > >> insert is rejected ? > > > > Rethink your data design --- this behavior is required by the SQL > > standard. > > I have a table of users permissions by departments > > CREATE TABLE permission ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > department_id CHAR(10) REFERENCES department , > UNIQUE ( user_id, permission_id, department_id ) ) > > permission_id is a permission name: Invoice, Waybill etc. > > department _id is a code of department whose documents user is authorized to > access. > > if department _id is NULL, user has access to all departments data. > > By this design it is meaningless to have two records with same user_id and > permission_id both having department_id NULL > > So I want that Postgres does not allow to insert them. > > How I should rethink this data design to be implemented in CREATE TABLE > statement ? You should use a value like 'ALL' to denote that they have access to all departments. Null, in this case, would mean you don't know which departments they have access to, and this is not really the case.
Andrus wrote: >>>How to create constraint so that NULL values are treated equal and second >>>insert is rejected ? >>> >>> >>Rethink your data design --- this behavior is required by the SQL >>standard. >> >> > >I have a table of users permissions by departments > >CREATE TABLE permission ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > department_id CHAR(10) REFERENCES department , > UNIQUE ( user_id, permission_id, department_id ) ) > >permission_id is a permission name: Invoice, Waybill etc. > >department _id is a code of department whose documents user is authorized to >access. > >if department _id is NULL, user has access to all departments data. > > >By this design it is meaningless to have two records with same user_id and >permission_id both having department_id NULL > >So I want that Postgres does not allow to insert them. > >How I should rethink this data design to be implemented in CREATE TABLE >statement ? > > > "if department _id is NULL, user has access to all departments data." This is your problem. You've assigned meaning to the "value" NULL. CREATE TABLE permission ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, UNIQUE (user_id, permission_id)); CREATE TABLE permission_department ( id serial, user_id CHAR(10) NOT NULL REFERENCES user, permission_id CHAR(10) NOT NULL REFERENCES privilege, department_id CHAR(10) REFERENCES department , UNIQUE (user_id, permission_id, department_id)); Any person who is authorized to access documents of a department MUST have a corresponding row in permission_department: If they are authorized to view documents of all departments, then they must have a row corresponding to every department.
>> I have a table of users permissions by departments >> >> CREATE TABLE permission ( >> id serial, >> user_id CHAR(10) NOT NULL REFERENCES user, >> permission_id CHAR(10) NOT NULL REFERENCES privilege, >> department_id CHAR(10) REFERENCES department , >> UNIQUE ( user_id, permission_id, department_id ) ) >> if department _id is NULL, user has access to all departments data. >> How I should rethink this data design to be implemented in CREATE TABLE >> statement ? > You should use a value like 'ALL' to denote that they have access to all > departments. Null, in this case, would mean you don't know which > departments they have access to, and this is not really the case. Using department ALL breaks the department_id reference to department table. Inserting department_id ALL is rejected since there is no such department. So I can use department ALL ? Andrus.
On Fri, 2005-07-15 at 10:51, Andrus wrote: > >> I have a table of users permissions by departments > >> > >> CREATE TABLE permission ( > >> id serial, > >> user_id CHAR(10) NOT NULL REFERENCES user, > >> permission_id CHAR(10) NOT NULL REFERENCES privilege, > >> department_id CHAR(10) REFERENCES department , > >> UNIQUE ( user_id, permission_id, department_id ) ) > > >> if department _id is NULL, user has access to all departments data. > > >> How I should rethink this data design to be implemented in CREATE TABLE > >> statement ? > > > You should use a value like 'ALL' to denote that they have access to all > > departments. Null, in this case, would mean you don't know which > > departments they have access to, and this is not really the case. > > Using department ALL breaks the department_id reference to department table. > Inserting department_id ALL is rejected since there is no such department. > > So I can use department ALL ? Then redesign this as a many to many relation. That way someone can have access to one, two, three, four, or all departments.
> "if department _id is NULL, user has access to all departments data." > > This is your problem. You've assigned meaning to the "value" NULL. > > CREATE TABLE permission ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > UNIQUE (user_id, permission_id)); > > > CREATE TABLE permission_department ( > id serial, > user_id CHAR(10) NOT NULL REFERENCES user, > permission_id CHAR(10) NOT NULL REFERENCES privilege, > department_id CHAR(10) REFERENCES department , > UNIQUE (user_id, permission_id, department_id)); > > Any person who is authorized to access documents of a department MUST have > a corresponding row in permission_department: If they are authorized to > view documents of all departments, then they must have a row corresponding > to every department. I don't understand why the permission_department table is required ? If user is authorized to all departments, I can add separate row for each department to former permission table. So the permission_department table is not required at all. Unfortunately, this approach causes loss of information: it loses the fact that user is allowed to see all departments data. If new department is added, this department should be made accessible for all users which have marked as "access all departments". 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')) Andrus.
> Then redesign this as a many to many relation. That way someone can > have access to one, two, three, four, or all departments. This means adding separate row for each department into permission table. If new department is added, I must determine in some way users which are allowed access to all departments and add nw rows to permission table automatically. It seems more reasonable to use NULL department value as "do'nt know, all departments allowed" Andrus.
On Fri, Jul 15, 2005 at 20:08:32 +0300, Andrus <eetasoft@online.ee> 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.
pgsql-general-owner@postgresql.org wrote on 07/15/2005 02:49:09 PM: > On Fri, Jul 15, 2005 at 20:08:32 +0300, > Andrus <eetasoft@online.ee> 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
On Fri, 2005-07-15 at 15:16, Richard_D_Levine@raytheon.com wrote: > pgsql-general-owner@postgresql.org wrote on 07/15/2005 02:49:09 PM: > > > On Fri, Jul 15, 2005 at 20:08:32 +0300, > > Andrus <eetasoft@online.ee> 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. So, does your system currently support >1 departments for those that would need it? Because if the way you're doing it now doesn't, and you have to change it to support that at some later date, that will be much more work than doing it now.
Andrus wrote: >>"if department _id is NULL, user has access to all departments data." >> >>This is your problem. You've assigned meaning to the "value" NULL. >> >>CREATE TABLE permission ( >> id serial, >> user_id CHAR(10) NOT NULL REFERENCES user, >> permission_id CHAR(10) NOT NULL REFERENCES privilege, >> UNIQUE (user_id, permission_id)); >> >> >>CREATE TABLE permission_department ( >> id serial, >> user_id CHAR(10) NOT NULL REFERENCES user, >> permission_id CHAR(10) NOT NULL REFERENCES privilege, >> department_id CHAR(10) REFERENCES department , >> UNIQUE (user_id, permission_id, department_id)); >> >>Any person who is authorized to access documents of a department MUST have >>a corresponding row in permission_department: If they are authorized to >>view documents of all departments, then they must have a row corresponding >>to every department. >> >> >I don't understand why the permission_department table is required ? > > I didn't include this because I thought it would be obvious: You have to put a unique constraint on that table so as to eliminate the possibility of redundant departmental permission rows, as you thought was your original problem. >If user is authorized to all departments, I can add separate row for each >department to former permission table. So the permission_department table is >not required at all > > Except that when abusing the meaning of NULL you can add duplicate rows indicating permission for all departments redundantly, which is what you originally misidentified as being the problem for which you sought a means to put a unique constraint on NULL values. As the first respondent said, the problem IS with the design. >Unfortunately, this approach causes loss of information: it loses the fact >that user is allowed to >see all departments data. If new department is added, this department should >be made accessible >for all users which have marked as "access all departments". > > That information is not lost, but it is a little more work to get it: You know how many departments there are. Any user that has a count of departments equal to the number of existing departments is an "all departments" user. You can thus use aggregation to identify the "all departments" users and then add a row for them corresponding to the new department.
I was faced with a similar issue. One suggestion I got from the Internet was to create a shadow column that contains the values used in the Index, with a dummy entry (in my case, the string <NULL>) for those records in which the primary column is NULL. It works well for my app.
Andrus wrote: >>Then redesign this as a many to many relation. ... > > This means adding separate row for each department into permission table. Not really. You can of course store an Array of department IDs in that same table. That would probably cause the minimum impact on your queries too.
Re: How to create unique constraint on NULL columns
From
ptjm@interlog.com (Patrick TJ McPhee)
Date:
In article <db8s6o$f36$1@news.hub.org>, Andrus <eetasoft@online.ee> wrote: % > Then redesign this as a many to many relation. That way someone can % > have access to one, two, three, four, or all departments. % % This means adding separate row for each department into permission table. % If new department is added, I must determine in some way users which are % allowed access to all % departments and add nw rows to permission table automatically. % % It seems more reasonable to use NULL department value as "do'nt know, all % departments allowed" But wouldn't you want to have an entry in the department table with NULL for the department ID? I mean, why should NULL act like NULL wrt foreign keys, but not wrt unique constraints? -- Patrick TJ McPhee North York Canada ptjm@interlog.com
<Richard_D_Levine@raytheon.com> wrote in message news:OFC81E21F9.6AEDE7BE-ON0525703F.006F22CF-0525703F.006F5C10@ftw.us.ray.com... > > > pgsql-general-owner@postgresql.org wrote on 07/15/2005 02:49:09 PM: > >> On Fri, Jul 15, 2005 at 20:08:32 +0300, >> Andrus <eetasoft@online.ee> 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.
>>>"if department _id is NULL, user has access to all departments data."
>>>This is your problem. You've assigned meaning to the "value" NULL.
>>>
>>>CREATE TABLE permission (
>>> id serial,
>>> user_id CHAR(10) NOT NULL REFERENCES user,
>>> permission_id CHAR(10) NOT NULL REFERENCES privilege,
>>> UNIQUE (user_id, permission_id));
>>>
>>>
>>>CREATE TABLE permission_department (
>>> id serial,
>>> user_id CHAR(10) NOT NULL REFERENCES user,
>>> permission_id CHAR(10) NOT NULL REFERENCES privilege,
>>> department_id CHAR(10) REFERENCES department ,
>>> UNIQUE (user_id, permission_id, department_id));
>>>
>>>Any person who is authorized to access documents of a department MUST
>>>have a corresponding row in permission_department: If they are authorized
>>>to view documents of all departments, then they must have a row
>>>corresponding to every department.
>>>
>>I don't understand why the permission_department table is required ?
>>
> I didn't include this because I thought it would be obvious: You have to
> put a unique constraint on that table so as to eliminate the possibility
> of redundant departmental permission rows, as you thought was your
> original problem.
I'm sorry but I still do not understand.
If I add all departments to former permission table, I can put this unique
constraint to former permission table. In this case there is no NULL
department and constranit will work OK.
>>If user is authorized to all departments, I can add separate row for each
>>department to former permission table. So the permission_department table
>>is not required at all
>>
> Except that when abusing the meaning of NULL you can add duplicate rows
> indicating permission for all departments redundantly, which is what you
> originally misidentified as being the problem for which you sought a means
> to put a unique constraint on NULL values. As the first respondent said,
> the problem IS with the design.
>
>>Unfortunately, this approach causes loss of information: it loses the fact
>>that user is allowed to
>>see all departments data. If new department is added, this department
>>should be made accessible
>>for all users which have marked as "access all departments".
>>
> That information is not lost, but it is a little more work to get it: You
> know how many departments there are. Any user that has a count of
> departments equal to the number of existing departments is an "all
> departments" user. You can thus use aggregation to identify the "all
> departments" users and then add a row for them corresponding to the new
> department.
It is advicable to have two types of users: one user ("super department
user") has access to all existing and all departments to be added in future.
Second type of user can have access to all currently existing departments
but NOT in new departments.
Using non-null departments both of those users have exactly the same records
in permission table.
So those users are not distinguished.
Using NULL as meaning of "don'nt know, allow access to all" distinguishes
both tohse users.
Andrus.
> I was faced with a similar issue. One suggestion I got from the Internet > was to create a shadow column that contains the values used in the Index, > with a dummy entry (in my case, the string <NULL>) for those records in > which the primary column is NULL. It works well for my app. Michael, thank you. This is not working in my case. Uniquenes sis not sufficient: I need referential integrity with uniqueness. If I add <NULL> department, this must be added into department table also and pollutes this table. Andrus.
On 7/18/05, Andrus <eetasoft@online.ee> wrote:
> > 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.
Not true. :) You simply need to add CHECK (departament_id <> 0) (assuming
0 is the ID of ALL departaments. You can even CREATE DOMAIN with this
check "built in" to save you some typing. :)
If, for some reason, you want to be sure that 'ALL deparaments' is not
visible, you can create a view which will SELECT WHERE departament <> 0;
Basically -- I think you should get some pre-declared values, like
departament_id
of 0 and simply restrict it where it is not allowed. It's better than
forcing NULL
to become a value. :)
Regards,
Dawid
>> 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. > > Not true. :) You simply need to add CHECK (departament_id <> 0) (assuming > 0 is the ID of ALL departaments. You can even CREATE DOMAIN with this > check "built in" to save you some typing. :) > > If, for some reason, you want to be sure that 'ALL deparaments' is not > visible, you can create a view which will SELECT WHERE departament <> 0; > > Basically -- I think you should get some pre-declared values, like > departament_id > of 0 and simply restrict it where it is not allowed. It's better than > forcing NULL > to become a value. :) Dawid, I have meaningful primary key in department table (department code used inside enterptise), not a surrogate number (I use meaningful primary keys whenever possible). Some usres may use floow numbers as department codes. They want to use 0 as ground floor. Using your suggestion my applicaton does not allow to use some code as department code. Should I switch to surrogate primary key in department table? This is major re-write.
On Fri, 2005-07-15 at 13:46 +0300, Andrus wrote: > I have table > > CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, > UNIQUE (col1, col2) ); > > This table allows to insert duplicate rows if col2 is NULL: > > INSERT INTO test VALUES ( '1', NULL ); > INSERT INTO test VALUES ( '1', NULL ); > > does NOT cause error! The two rows are not duplicated. NULL means "any value" in that context, so you can only say that the values for the first column are equal. Nothing can be said about the values in the second column. The database can't say they are the same, and can't say they are different either. http://en.wikipedia.org/wiki/Null_%28SQL%29 > How to create constraint so that NULL values are treated equal and second > insert is rejected ? I think you can do that with special operators (such as IS DISTINCT FROM) but you're using NULL as a normal value, that is not what it's meant to be in the first place, and I advice to be careful: http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html please read the paragraphs on ... = NULL, IS NULL, and IS DISTINCT FROM. NULL is meant to represent the lack of knowledge ("unknown"). If you are ever treating NULL as a real value (i.e. comparing it to other values or or other NULLs), you must think twice about your design. IMVHO, the only place for IS NULL and IS DISTINCT FROM are meta-operations on data, administrative tasks and so on. There should be no need to use them in "normal" queries. Unless you're coding quick and dirty hacks when you really know what you're doing but don't care about the correctness of your design, of course. .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@ESI.it
On 7/18/05, Andrus <eetasoft@online.ee> wrote: > >> 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. > > > > Not true. :) You simply need to add CHECK (departament_id <> 0) (assuming > > 0 is the ID of ALL departaments. You can even CREATE DOMAIN with this > > check "built in" to save you some typing. :) > > > > If, for some reason, you want to be sure that 'ALL deparaments' is not > > visible, you can create a view which will SELECT WHERE departament <> 0; > > > > Basically -- I think you should get some pre-declared values, like > > departament_id > > of 0 and simply restrict it where it is not allowed. It's better than > > forcing NULL > > to become a value. :) > > Dawid, > > I have meaningful primary key in department table (department code used > inside enterptise), not a surrogate number (I use meaningful primary keys > whenever possible). OK, so then just define the UNIQUE INDEX to be exactly what you need, for example: CREATE UNIQUE INDEX my_special_unique ON permission ( user_id, permisson_id, (department_id IS NULL), (CASE when department_id IS NULL THEN 0 ELSE department_id END) ); This should work for any department_id type. With one little drawback: person can have permission to ALL departaments (NULL) _and_ also an explicit permission for any of already existing ones. HTH, HAND
On 7/18/05, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On 7/18/05, Andrus <eetasoft@online.ee> wrote:
> > I have meaningful primary key in department table (department code used
> > inside enterptise), not a surrogate number (I use meaningful primary keys
> > whenever possible).
>
> OK, so then just define the UNIQUE INDEX to be exactly what you need,
> for example:
Or better:
CREATE UNIQUE INDEX permission_unique_key (user_id,permission_id,department_id);
CREATE UNIQUE INDEX permission_uninull_key (user_id,permission_id)
WHERE department_id IS NULL;
...you may want to add WHERE department_id IS NOT NULL to the first query
(or not). Anyway -- this way these indexes could be used by searches easily
than using previous index I've sent.
Regards,
Dawid
> Or better: > CREATE UNIQUE INDEX permission_unique_key > (user_id,permission_id,department_id); > CREATE UNIQUE INDEX permission_uninull_key (user_id,permission_id) > WHERE department_id IS NULL; > > ...you may want to add WHERE department_id IS NOT NULL to the first query > (or not). Anyway -- this way these indexes could be used by searches > easily > than using previous index I've sent. > Dawid, I see no difference of adding/not adding IS NOT NULL to the first CREATE UNIQUE INDEX . So why the explicit creation of first unique index is required ? It would be better to create unique constraint on (user_id,permission_id,department_id) instead on explicity creating index. Andrus.
Andrus wrote: >>I was faced with a similar issue. One suggestion I got from the Internet >>was to create a shadow column that contains the values used in the Index, >>with a dummy entry (in my case, the string <NULL>) for those records in >>which the primary column is NULL. It works well for my app. > > > Michael, > > thank you. > This is not working in my case. Uniquenes sis not sufficient: I need > referential integrity with uniqueness. If I add <NULL> department, this must > be added into department table also and pollutes this table. Although others have suggested that you're going to need an additional table, I've not seen anyone explicitly state why this is causing you problems. Clearly, NULL should not be used to mean "Any". However, the only reason you're doing this is because you want a FK to the "department" table. Nothing wrong with that, except that you're not storing a department-ID. How do I know? Because you want to store "Any" and that isn't a department-ID. So - the obvious solution is a "department_access" table that you can refer to, and which in turn refers to various departments. Of course, that's a fair bit of work and you'd like a short-cut. I'm afraid there isn't one. Half the problems I have with systems I design are where I've tried to save effort now and end up paying twice over later on. -- Richard Huxton Archonet Ltd
> Although others have suggested that you're going to need an additional > table, I've not seen anyone explicitly state why this is causing you > problems. > > Clearly, NULL should not be used to mean "Any". However, the only reason > you're doing this is because you want a FK to the "department" table. > Nothing wrong with that, except that you're not storing a department-ID. > How do I know? Because you want to store "Any" and that isn't a > department-ID. > > So - the obvious solution is a "department_access" table that you can > refer to, and which in turn refers to various departments. > > Of course, that's a fair bit of work and you'd like a short-cut. I'm > afraid there isn't one. Half the problems I have with systems I design are > where I've tried to save effort now and end up paying twice over later on. Richard, Thank you. I asked this hoping there is a simple and correct solution. Now I understand that implementing this correctly reguires re-design my user permission tables. Acually, there can be any number of duplicate records in user permission table. This does not affect my application logic and does not cause incorrect behaviour since permission table is not joined with other tables. Avoiding duplicates is required only for joins to be working correctly. So I'm currently staying in a hack by creating unique index and will continue struggling with other problems. There are number of other tables (about 4 from 100 tables) which have similar referential problems. It seems now that implementing referential integrity for those tables requries also major re-design of database and application logic. I'm in beginning of porting huge application to Postgres in Windows. There are number of other problems which seems to be more critical. So I think I will not try to struggle to implement referential integrity of those tables in Postgres. Or will I post referential integrity implementation problem with document/transactions table ... I cannot resist .. ? Andrus.