How to create unique constraint on NULL columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to create unique constraint on NULL columns

Andrus Moor-2
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.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Dawid Kuroczko
On 7/15/05, Andrus <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Tom Lane-2
In reply to this post by Andrus Moor-2
"Andrus" <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
>> 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.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Berend Tober-2
In reply to this post by Andrus Moor-2
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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
In reply to this post by Scott Marlowe
>> 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.



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
In reply to this post by Berend Tober-2
> "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.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
In reply to this post by Scott Marlowe
> 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.



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Bruno Wolff III
In reply to this post by Andrus Moor-2
On Fri, Jul 15, 2005 at 20:08:32 +0300,
  Andrus <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Richard_D_Levine


[hidden email] wrote on 07/15/2005 02:49:09 PM:

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Scott Marlowe
On Fri, 2005-07-15 at 15:16, [hidden email] wrote:

> [hidden email] wrote on 07/15/2005 02:49:09 PM:
>
> > On Fri, Jul 15, 2005 at 20:08:32 +0300,
> >   Andrus <[hidden email]> 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.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Berend Tober-2
In reply to this post by Andrus Moor-2
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.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Patrick TJ McPhee
In reply to this post by Andrus Moor-2
In article <db8s6o$f36$[hidden email]>, Andrus <[hidden email]> 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
[hidden email]

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Michael Schmidt
In reply to this post by Tom Lane-2
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.
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Ron Mayer
In reply to this post by Andrus Moor-2
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
In reply to this post by Richard_D_Levine

<[hidden email]> wrote in message
news:[hidden email]...

>
>
> [hidden email] wrote on 07/15/2005 02:49:09 PM:
>
>> On Fri, Jul 15, 2005 at 20:08:32 +0300,
>>   Andrus <[hidden email]> 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 [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
In reply to this post by Berend Tober-2
>>>"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.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: How to create unique constraint on NULL columns

Andrus Moor-2
In reply to this post by Michael Schmidt

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
12