How to create unique constraint on NULL columns

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.

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.

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

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.

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