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.
Mic
> 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" t
> 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 --
On 7/18/05, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> On 7/18/05, Andrus <[EMAIL PROTECTED]> 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 ju
On 7/18/05, Andrus <[EMAIL PROTECTED]> 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
> >
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 );
>
>> 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 DOM
On 7/18/05, Andrus <[EMAIL PROTECTED]> 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 departmen
>>>"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,
>>
> 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,
t
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
>
> [EMAIL PROTECTED] wrote on 07/15/2005 02:49:09 PM:
>
>> On Fri, Jul 15, 2005 at 20:08:32 +0300,
>> Andrus <[EMAIL PROTECTED]> wrote:
>> >
>> > So I'll think still continuing to use null as unrestricted department
>> > access.
>>
In article <[EMAIL PROTECTED]>, Andrus <[EMAIL PROTECTED]> 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
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.
---
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.
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 (
On Fri, 2005-07-15 at 15:16, [EMAIL PROTECTED] wrote:
> [EMAIL PROTECTED] wrote on 07/15/2005 02:49:09 PM:
>
> > On Fri, Jul 15, 2005 at 20:08:32 +0300,
> > Andrus <[EMAIL PROTECTED]> wrote:
> > >
> > > So I'll think still continuing to use null as unrestricted department
> > > access.
> > >
> >
[EMAIL PROTECTED] wrote on 07/15/2005 02:49:09 PM:
> On Fri, Jul 15, 2005 at 20:08:32 +0300,
> Andrus <[EMAIL PROTECTED]> wrote:
> >
> > So I'll think still continuing to use null as unrestricted department
> > access.
> >
> > Is it reasonable to create unique constraint using
> >
> > CREATE U
On Fri, Jul 15, 2005 at 20:08:32 +0300,
Andrus <[EMAIL PROTECTED]> 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 pe
> 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 a
> "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,
> UNIQU
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) REF
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_
>> 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
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
>
> CR
>> 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 N
"Andrus" <[EMAIL PROTECTED]> 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 cr
On 7/15/05, Andrus <[EMAIL PROTECTED]> 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
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 a
28 matches
Mail list logo
