向PostgreSQL的movie_revenues表插入记录时触发唯一键冲突错误

问题描述 #

向movie_revenues表插入记录时触发以下错误：

duplicate key value violates unique constrain "movie_revenues_pkey"
Key (revenue_id) = (9) already exists

尝试插入的SQL代码：

INSERT INTO movie_revenues(
     movie_id, domestic_takings, international_takings)
    VALUES (54, 120, 420);

相关表结构与触发器代码 #

movie_revenues表创建语句 #

CREATE TABLE IF NOT EXISTS public.movie_revenues
(
    revenue_id integer NOT NULL DEFAULT nextval('movie_revenues_revenue_id_seq'::regclass),
    movie_id integer,
    domestic_takings numeric(6,2),
    international_takings numeric(6,2),
    CONSTRAINT movie_revenues_pkey PRIMARY KEY (revenue_id),
    CONSTRAINT movie_revenues_movie_id_fkey FOREIGN KEY (movie_id)
        REFERENCES public.movies (movie_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

movies表创建语句 #

CREATE TABLE IF NOT EXISTS public.movies
(
    movie_id integer NOT NULL DEFAULT nextval('movies_movie_id_seq'::regclass),
    movie_name character varying(50) COLLATE pg_catalog."default",
    movie_length integer,
    movie_lang character varying(20) COLLATE pg_catalog."default",
    release_date date,
    age_certificate character varying(5) COLLATE pg_catalog."default",
    director_id integer,
    CONSTRAINT movies_pkey PRIMARY KEY (movie_id),
    CONSTRAINT movies_director_id_fkey FOREIGN KEY (director_id)
        REFERENCES public.directors (director_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

top_movies表与触发器代码 #

-- Create the top_movies table
CREATE TABLE top_movies (
    revenue_id SERIAL PRIMARY KEY,
    movie_id INTEGER REFERENCES movies(movie_id),
    international_takings NUMERIC
);

-- Create the trigger on the movie_revenue table
CREATE OR REPLACE FUNCTION insert_top_movies()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the international_takings is greater than 4000
    IF NEW.international_takings > 4000 THEN
        -- Insert a new row in the top_movies table
        INSERT INTO top_movies (movie_id, international_takings)
        VALUES (NEW.movie_id, NEW.international_takings)
        ORDER BY movie_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER top_movies_trigger
AFTER INSERT ON movie_revenues
FOR EACH ROW
EXECUTE FUNCTION insert_top_movies();

错误原因分析 #

出现唯一键冲突的核心原因是**movie_revenues_revenue_id_seq序列的当前值落后于movie_revenues表中已存在的revenue_id最大值**。

常见触发场景包括：

• 手动插入过指定revenue_id值的记录，导致序列未同步更新（比如直接执行过INSERT INTO movie_revenues(revenue_id, ...) VALUES(9, ...)）
• 表数据被手动导入或恢复过，但对应的序列没有重新校准

你的触发器代码本身不会导致这个错误（触发器是AFTER INSERT执行，且操作的是top_movies表，和movie_revenues的主键无关），但需要注意触发器里的判断条件写的是>4000，而你插入的international_takings是420，不会触发top_movies的插入，这和你“大于400就复制到top_movies”的需求不符，属于额外的逻辑问题。

解决方法 #

1. 校准序列值：执行以下SQL，让序列的当前值直接跳到movie_revenues表中revenue_id的最大值+1，确保后续自动生成的ID不会重复：

SELECT setval('movie_revenues_revenue_id_seq', (SELECT MAX(revenue_id) FROM movie_revenues) + 1);

2. 修复触发器判断条件：如果你的需求是当international_takings大于400就插入到top_movies，把触发器函数里的4000改成400，同时去掉插入语句中多余的ORDER BY movie_id（单条插入无需排序）：

CREATE OR REPLACE FUNCTION insert_top_movies()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.international_takings > 400 THEN
        INSERT INTO top_movies (movie_id, international_takings)
        VALUES (NEW.movie_id, NEW.international_takings);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者user2371684