How can I force a constraint only if the column is not null in Postgresql?

Question

I would like the solution to enforce the constraint only if the column is not null. I can't seem to find a way to do this in the documentation.

create table mytable(
  table_identifier_a INTEGER,
  table_identifier_b INTEGER,
  table_value1,...)

Make for the nature of the data, I will have identifier b and value when creating the table. After receiving additional data, I can fill in the identifier a. At this point, I would like to provide unique key of (identifier_a, value1), but only if identifier_a exists.

Hope this makes sense, does anyone have any ideas?

Answer 1

. NULL.

CREATE TABLE mytable (
    table_identifier_a   INTEGER    NULL,
    table_identifier_b   INTEGER    NOT NULL,
    table_value1         INTEGER    NOT NULL,

    UNIQUE(table_identifier_a, table_identifier_b)
);

, NULL, identifier_b :

test=# INSERT INTO mytable values(NULL, 1, 2);
INSERT 0 1
test=# INSERT INTO mytable values(NULL, 1, 2);
INSERT 0 1
test=# select * from mytable;
 table_identifier_a | table_identifier_b | table_value1 
--------------------+--------------------+--------------
                    |                  1 |            2
                    |                  1 |            2
(2 rows)

(a, b):

test=# update mytable set table_identifier_a = 3;
ERROR:  duplicate key value violates unique constraint "mytable_table_identifier_a_key"

, : . , . , .

Answer 2

, , postgres , :

START;
SET CONSTRAINTS <...> DEFERRED;
<SOME INSERT/UPDATE/DELETE>
COMMIT;

. : Postgres 7.4 Doc - Postgres 8.3 Doc

Answer 3

, , , . . - , , - . , , .

Answer 4

, .

Answer 5

, , , , .