I find it difficult to understand the desired behavior of ON UPDATE CASCADE when one field is NULL and this link is updated.
In the following example of city tables and statistics, when we have a city and its province is not (NULL), the ON UPDATE CASCADE trigger does not work properly while I expected it to update the province value according to statistics.
When we start an update around the city using EXPLAIN, we can easily see this behavior.
Note. I am using PostgreSQL 9.4.8
Creating tables to reproduce the case
CREATE TABLE city (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
country_id BIGINT NOT NULL,
province_id BIGINT,
CONSTRAINT city_city_country_province_un UNIQUE (id, country_id, province_id)
);
CREATE TABLE statistics (
id BIGSERIAL PRIMARY KEY,
city_id BIGINT NOT NULL,
country_id BIGINT NOT NULL,
province_id BIGINT,
some_data INTEGER NOT NULL DEFAULT 0,
CONSTRAINT statistics_city_country_province_fk FOREIGN KEY (city_id, country_id, province_id) REFERENCES city (id, country_id, province_id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT statistics_city_un UNIQUE (city_id)
);
Filling tables
INSERT INTO city (name, country_id, province_id) VALUES ('SAO CARLOS', 1, 1);
INSERT INTO city (name, country_id, province_id) VALUES ('VATICAN CITY', 2, NULL);
INSERT INTO statistics (city_id, country_id, province_id) VALUES (1, 1, 1);
INSERT INTO statistics (city_id, country_id, province_id) VALUES (2, 1, NULL);
City update when NOT NULL province
EXPLAIN ANALYZE VERBOSE UPDATE city SET province_id = 3 WHERE id = 1;
REQUEST PLAN
Update on public.city (cost=0.15..8.17 rows=1 width=168) (actual time=0.238..0.238 rows=0 loops=1)
-> Index Scan using city_city_country_province_un on public.city (cost=0.15..8.17 rows=1 width=168) (actual time=0.046..0.048 rows=1 loops=1)
Output: id, name, country_id, 3::bigint, ctid
Index Cond: (city.id = 1)
Planning time: 0.510 ms
Trigger RI_ConstraintTrigger_a_41406 for constraint statistics_city_country_province_fk on city: time=0.792 calls=1
Trigger RI_ConstraintTrigger_c_41408 for constraint statistics_city_country_province_fk on statistics: time=0.296 calls=1
Execution time: 1.412 ms
city after update
id | city_id | country_id | province_id | some_data
----+---------+------------+-------------+-----------
1 | 1 | 1 | 3 | 0
2 | 2 | 2 | | 0
statistics after update
id | city_id | country_id | province_id | some_data
----+---------+------------+-------------+-----------
1 | 1 | 1 | 3 | 0
2 | 2 | 2 | | 0
City update when the province is NULL
EXPLAIN ANALYZE VERBOSE UPDATE city SET province_id = 7 WHERE id = 2;
REQUEST PLAN
Update on public.city (cost=0.15..8.17 rows=1 width=168) (actual time=0.170..0.170 rows=0 loops=1)
-> Index Scan using city_city_country_province_un on public.city (cost=0.15..8.17 rows=1 width=168) (actual time=0.042..0.044 rows=1 loops=1)
Output: id, name, country_id, 7::bigint, ctid
Index Cond: (city.id = 2)
Planning time: 0.423 ms
Execution time: 0.225 ms
city after update
id | name | country_id | province_id
----+--------------+------------+-------------
1 | SAO CARLOS | 1 | 3
2 | VATICAN CITY | 2 | 7
statistics after update
id | city_id | country_id | province_id | some_data
----+---------+------------+-------------+-----------
1 | 1 | 1 | 3 | 0
2 | 2 | 2 | | 0
EDIT: work arround to update when field NULL
, NULL
CREATE OR REPLACE FUNCTION update_null_province_reference() RETURNS TRIGGER AS $$
BEGIN
IF (OLD.province_id IS NULL AND NEW.province_id IS NOT NULL) THEN
UPDATE statistics SET province_id = NEW.province_id WHERE city_id = NEW.id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_null_province_reference
AFTER UPDATE ON city
FOR EACH ROW
EXECUTE PROCEDURE update_null_province_reference();