How are Postgre Constraints Handled
-
doublehelix
- Posts: 9
- Joined: Mon 28 Dec 2009 23:42
How are Postgre Constraints Handled
I've established a unique constraint in postgre but i am still able to ad record that violate the constraints. Why is this?
Unfortunately, the information you have provided is not enough to reproduce your issue. But I presume that you created unique a unique constraint on a text field. Maybe you try to insert two different text values that look the same but actually are different. For example, you fill memo control with text 'aaa' and try to insert it. But the first time it can be text 'aaa' and the second time it can be text 'aaa' + #13 (carriage return). In the memo control it looks the same but constraint will not be violated because this text is different.
-
doublehelix
- Posts: 9
- Joined: Mon 28 Dec 2009 23:42
Constraints
I have a unique constraint on 3 fields. 2 are integer one is a character. the character Field is not used often mainly for sub grouping of the first integer. For example item no 1 belonging to group 1. And with a sub group item 1 sub group 'a' belonging to group 1. I am attempting to prevent the same group being used again. So that once 1-1 or 1-a-1 is used the user will need to use the next item in series 2-1 or 2-a-1, and a unique exception occurs with 1-1 being entered again.
-
doublehelix
- Posts: 9
- Joined: Mon 28 Dec 2009 23:42
Constraints
It appears now that ALL three fields must be filled in order to trigger the constraint. I cannot use 1-null-1. Entering that same series again will not violate the constraint, but I attempt to use 1-a-1 more than once, and the constraint violation kicks. The constraint doesn't appear to work when one or more of the fields is a null value. This is definitely a bug.
Hello
This is not a bug. This is a PostgreSQL database feature.
The problem is that comparison with NULL always returns False (even NULLNULL). Therefore when unique constraint compares two records that have NULL values in the fields, comparison always will return false and unique constraint will allow to insert these records. In your case two records 1-null-1 and 1-null-1 are different because NULLNULL.
To resolve this issue create a unique constraint like this:
CREATE UNIQUE INDEX "UQ_CONSTRAINT_NAME" ON "public"."TABLE_NAME"
USING btree ("INT_COLUMN1", "INT_COLUMN2", COALESCE("STR_COLUMN", ''::character varying));
This is not a bug. This is a PostgreSQL database feature.
The problem is that comparison with NULL always returns False (even NULLNULL). Therefore when unique constraint compares two records that have NULL values in the fields, comparison always will return false and unique constraint will allow to insert these records. In your case two records 1-null-1 and 1-null-1 are different because NULLNULL.
To resolve this issue create a unique constraint like this:
CREATE UNIQUE INDEX "UQ_CONSTRAINT_NAME" ON "public"."TABLE_NAME"
USING btree ("INT_COLUMN1", "INT_COLUMN2", COALESCE("STR_COLUMN", ''::character varying));