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
