时间戳

首页 > 解决方案 > 引用表 PostgreSQL 的给定键没有唯一约束匹配

sql - 引用表 PostgreSQL 的给定键没有唯一约束匹配

问题描述

我使用 PostgreSQL 创建了一个 ER 图，并尝试根据导出的 .sql 文件创建表，但出现错误。我可能会犯结构性错误。如果您发现有关实体关系图的结构错误，您能解释一下吗？

错误：

错误图片

这是我的ER图：

ER图图像

这是我导出的 SQL 脚本：

BEGIN;


CREATE TABLE public."Persons"
(
    id bigserial NOT NULL,
    name character varying(30) NOT NULL,
    surname character varying(30) NOT NULL,
    email character varying(30),
    PRIMARY KEY (id)
);

CREATE TABLE public."System_Personals"
(
    id bigserial NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Job_Seekers"
(
    id bigserial NOT NULL,
    ssn character(11) NOT NULL,
    birth_date date NOT NULL,
    password character varying(30) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Employers"
(
    id bigserial NOT NULL,
    company_name character varying(30) NOT NULL,
    website character varying(40) NOT NULL,
    phone_number character(11) NOT NULL,
    password character varying(30) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Jobs"
(
    id bigserial NOT NULL,
    role character varying(30) NOT NULL,
    definition character varying(500) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Job_Announcements"
(
    id bigserial NOT NULL,
    title character varying(100) NOT NULL,
    company_name character varying(30) NOT NULL,
    role character varying(30) NOT NULL,
    city_name character varying(30) NOT NULL,
    salary bigint NOT NULL,
    job_definition character varying(300) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Cities"
(
    id bigserial NOT NULL,
    name character varying(30) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Activations"
(
    id bigserial NOT NULL,
    email character varying(30) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Employer_Activations"
(
    id bigserial NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE public."Job_Seeker_Activations"
(
    id bigserial NOT NULL,
    ssn character(11) NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE public."System_Personals"
    ADD FOREIGN KEY (id)
    REFERENCES public."Persons" (id)
    NOT VALID;


ALTER TABLE public."Job_Seekers"
    ADD FOREIGN KEY (id)
    REFERENCES public."Persons" (id)
    NOT VALID;


ALTER TABLE public."Employers"
    ADD FOREIGN KEY (id)
    REFERENCES public."Persons" (id)
    NOT VALID;


ALTER TABLE public."System_Personals"
    ADD FOREIGN KEY (id)
    REFERENCES public."Jobs" (id)
    NOT VALID;


ALTER TABLE public."Employers"
    ADD FOREIGN KEY (id)
    REFERENCES public."Cities" (id)
    NOT VALID;


ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (city_name)
    REFERENCES public."Cities" (name)
    NOT VALID;


ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (role)
    REFERENCES public."Jobs" (role)
    NOT VALID;


ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (job_definition)
    REFERENCES public."Jobs" (definition)
    NOT VALID;


ALTER TABLE public."Employer_Activations"
    ADD FOREIGN KEY (id)
    REFERENCES public."Activations" (id)
    NOT VALID;


ALTER TABLE public."Job_Seeker_Activations"
    ADD FOREIGN KEY (id)
    REFERENCES public."Activations" (id)
    NOT VALID;


ALTER TABLE public."Job_Seeker_Activations"
    ADD FOREIGN KEY (ssn)
    REFERENCES public."Persons" (id)
    NOT VALID;


ALTER TABLE public."Activations"
    ADD FOREIGN KEY (email)
    REFERENCES public."Persons" (email)
    NOT VALID;

END;

为什么我得到那个错误？有什么帮助吗？

标签： sqlpostgresqlentity-relationship

解决方案

所有这些都违反了：

ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (city_name)
    REFERENCES public."Cities" (name)
    NOT VALID;

ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (role)
    REFERENCES public."Jobs" (role)
    NOT VALID;

ALTER TABLE public."Job_Announcements"
    ADD FOREIGN KEY (job_definition)
    REFERENCES public."Jobs" (definition)
    NOT VALID;

ALTER TABLE public."Activations"
    ADD FOREIGN KEY (email)
    REFERENCES public."Persons" (email)
    NOT VALID;

手册：

外键必须引用作为主键或形成唯一约束的列。

推荐阅读