Re: How to create unique constraint on NULL columns

Re: How to create unique constraint on NULL columns

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.

Re: How to create unique constraint on NULL columns

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.

Re: How to create unique constraint on NULL columns

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  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.

Re: How to create unique constraint on NULL columns

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

Re: How to create unique constraint on NULL columns

"Andrus"  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,
            "", then the result of the  is true; otherwise, the result of the 
            is false.

(SQL92 8.9  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

Re: How to create unique constraint on NULL columns

On Fri, Jul 15, 2005 at 20:08:32 +0300,
  Andrus  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.

Re: How to create unique constraint on NULL columns

In article , Andrus  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

Re: How to create unique constraint on NULL columns

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.

Re: How to create unique constraint on NULL columns

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.

Re: How to create unique constraint on NULL columns

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

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 ) 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  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

Re: How to create unique constraint on NULL columns

On 7/15/05, Andrus  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

Re: How to create unique constraint on NULL columns

On 7/18/05, Andrus  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

Re: How to create unique constraint on NULL columns

On 7/18/05, Andrus  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

Re: How to create unique constraint on NULL columns

On 7/18/05, Dawid Kuroczko  wrote:
> On 7/18/05, Andrus  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

Re: How to create unique constraint on NULL columns

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. 

Re: How to create unique constraint on NULL columns

>> 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. 

Re: How to create unique constraint on NULL columns

>> 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. 

Re: How to create unique constraint on NULL columns

> "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. 

Re: How to create unique constraint on NULL columns

> 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. 

Re: How to create unique constraint on NULL columns

 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  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. 

Re: How to create unique constraint on NULL columns

>>>"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. 

Re: How to create unique constraint on NULL columns

> 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 ) 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  department, this must 
be added into department table also and pollutes this table.

Andrus. 

Re: How to create unique constraint on NULL columns

>> 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. 

Re: How to create unique constraint on NULL columns

> 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. 

Re: How to create unique constraint on NULL columns

> 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. 

Re: How to create unique constraint on NULL columns

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  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