为什么我不能在 PostgreSQL 中设置这个唯一约束？

Question

我不断得到：

SQL 错误：错误：无法创建 唯一索引 “service_import_checksum_key”详细信息： 密钥（校验和）=() 重复。

在声明中：

更改表“public”。“service_import”添加约束“service_import_checksum_key”唯一（“校验和”）

但此约束不是重复的。整个数据库中的任何地方都没有像这样的其他约束，我不知道为什么它一直坚持认为它是重复的。我假设这是我在这里缺少的 postgres 的一些奇怪的细微差别。

我做错了什么？

表转储：

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: service_import; Type: TABLE; Schema: public; Owner: cvs_tar; Tablespace: 
--

CREATE TABLE service_import (
    id integer NOT NULL,
    name character varying(32) NOT NULL,
    importfile character varying(64) NOT NULL,
    reportfile character varying(64) NOT NULL,
    percent smallint NOT NULL,
    message text NOT NULL,
    stamp timestamp without time zone DEFAULT now() NOT NULL,
    complete smallint DEFAULT 0 NOT NULL,
    checksum character varying(40) NOT NULL
);


ALTER TABLE public.service_import OWNER TO cvs_tar;

--
-- Name: service_imports_id_seq; Type: SEQUENCE; Schema: public; Owner: cvs_tar
--

CREATE SEQUENCE service_imports_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.service_imports_id_seq OWNER TO cvs_tar;

--
-- Name: service_imports_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cvs_tar
--

ALTER SEQUENCE service_imports_id_seq OWNED BY service_import.id;


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: cvs_tar
--

ALTER TABLE service_import ALTER COLUMN id SET DEFAULT nextval('service_imports_id_seq'::regclass);


--
-- Name: service_import_name_key; Type: CONSTRAINT; Schema: public; Owner: cvs_tar; Tablespace: 
--

ALTER TABLE ONLY service_import
    ADD CONSTRAINT service_import_name_key UNIQUE (name);


--
-- Name: service_import_pkey; Type: CONSTRAINT; Schema: public; Owner: cvs_tar; Tablespace: 
--

ALTER TABLE ONLY service_import
    ADD CONSTRAINT service_import_pkey PRIMARY KEY (id);


--
-- Name: service_import_complete_idx; Type: INDEX; Schema: public; Owner: cvs_tar; Tablespace: 
--

CREATE INDEX service_import_complete_idx ON service_import USING btree (complete);


--
-- Name: service_import_stamp_idx; Type: INDEX; Schema: public; Owner: cvs_tar; Tablespace: 
--

CREATE INDEX service_import_stamp_idx ON service_import USING btree (stamp);


--
-- PostgreSQL database dump complete
--

Answer 1

再次阅读错误消息：

SQL 错误：错误：无法创建唯一索引“service_import_checksum_key”
详细信息：密钥（校验和）=() 重复。

看起来它告诉您 checksum 列中存在重复值，并且您正在尝试通过约束强制该列的唯一性。约束不重复，数据有重复。

此外，“()”部分表示 checksum 列中有多个空字符串。唯一约束允许多个 NULL 值（因为 NULL = NULL 是 NULL，这不是真的），但空字符串不是 NULL代码>.

澄清正在发生的情况的示例：

=> CREATE TABLE x (s VARCHAR NULL);
CREATE TABLE
=> INSERT INTO x (s) VALUES (''), ('a'), ('');
INSERT 0 3
=> ALTER TABLE x ADD CONSTRAINT ux UNIQUE(s);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "ux" for table "x"
ERROR:  could not create unique index "ux"
DETAIL:  Key (s)=() is duplicated.
=> delete from x where s='';
DELETE 2
=> INSERT INTO x (s) VALUES ('a');
INSERT 0 1
=> ALTER TABLE x ADD CONSTRAINT ux UNIQUE(s);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "ux" for table "x"
ERROR:  could not create unique index "ux"
DETAIL:  Key (s)=(a) is duplicated.

特别是，请注意 ERROR 和 DETAIL 的内容，并将其与 INSERT 进行比较。