主页 / dba / 问题 / 317459
Accepted
JLCarveth
Asked: 2022-09-28 05:18:09 +0800 CST

具有外部约束的重复键问题

  • 772

我有一个包含以下表格的数据库：items、categories和category_items. （简化，省略字段）模式如下：

CREATE TABLE IF NOT EXISTS items (
    id SERIAL primary key not null,
    partNo text unique not null
);

CREATE TABLE IF NOT EXISTS categories (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS category_items (
    category_id SERIAL PRIMARY KEY,
    partno TEXT NOT NULL,
    FOREIGN KEY (partno) REFERENCES items(partno),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

在我想在表中插入一条记录之前，这个模式似乎工作正常category_items。在第一次插入时category_items，它可以工作，但是如果我尝试插入同一类别的另一个项目，则会出现重复键错误：duplicate key value violates unique constraint "category_items_pkey" Detail: Key (category_id)=(2) already exists.

问题是，category_id从来没有指定必须是唯一的，那么为什么这会导致约束错误呢？

这些是我试图插入的记录：

INSERT INTO category_items (category_id, partno, size) VALUES
    ((SELECT id FROM categories WHERE name = 'category1'), 'part1', '1"');
    
INSERT INTO category_items (category_id, partno, size) VALUES
    ((SELECT id FROM categories WHERE name = 'category1'), 'part2', '4"');

使用 PostgreSQL 14。另外，如果这很重要，我正在 sqlfiddle.com 上运行它

postgresql

2 个回答

  1. Best Answer

    第三张表的设计有两个/三个问题：

    CREATE TABLE IF NOT EXISTS category_items (
    category_id SERIAL PRIMARY KEY,
    partno TEXT NOT NULL,
    FOREIGN KEY (partno) REFERENCES items(partno),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);
    • category_id不应在此表中，SERIAL因为它对. 将新行插入表中时会自动创建值，您需要使用这些值，而不是创建新行。FOREIGN KEYcategoriescategories
    • category_id不应该是PRIMARY KEY这个表的，因为您可能有一些与多个部分相关联的类别。
    • 在这个表中你根本不需要序列，因为自然PRIMARY KEY是类别和部分的组合。

    经过以上建议，设计变为：

    CREATE TABLE IF NOT EXISTS category_items (
    category_id INT NOT NULL,
    partno TEXT NOT NULL,
    -- other columns, i.e.
    -- size TEXT NOT NULL,   
    PRIMARY KEY (category_id, part_no),
    FOREIGN KEY (partno) REFERENCES items (partno),
    FOREIGN KEY (category_id) REFERENCES categories (id)
);
    • 1

  2. 我认为这是一个非常简单的修复，我只是因为错过它而感到有点傻。由于 incategory_items是category_idPRIMARY KEY，所以它默认为 UNIQUE。我需要一个单独的 ID，category_items如下所示：

    CREATE TABLE IF NOT EXISTS category_items (
    id SERIAL PRIMARY KEY,
    category_id SERIAL NOT NULL,
    partno TEXT NOT NULL,
    size TEXT NOT NULL,
    FOREIGN KEY (partno) REFERENCES items(partno),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);
    • 0

相关问题