主页 / 问题 / 934032
Accepted
Angel Pensive
Asked:2020-01-19 20:49:04 +0000 UTC2020-01-19 20:49:04 +0000 UTC

没有合适的主键或唯一键

  • 772

尝试创建表但抱怨没有匹配的唯一键或主键。

CREATE TABLE category
(
    id NUMBER GENERATED ALWAYS as IDENTITY(START WITH 1 INCREMENT by 1),
    name VARCHAR(100),
    cat_id VARCHAR(20),
    CONSTRAINT pk_category PRIMARY KEY (id, name, cat_id)
);
CREATE TABLE manufacturer
(
    id NUMBER GENERATED ALWAYS as IDENTITY(START WITH 1 INCREMENT by 1),
    name VARCHAR(100),
    site VARCHAR(255),
    description BLOB,
    CONSTRAINT pk_manufacturer PRIMARY KEY (id, name)
);
CREATE TABLE product
(
    id NUMBER GENERATED ALWAYS as IDENTITY(START WITH 1 INCREMENT by 1),
    name VARCHAR(100),
    model VARCHAR(100),
    quantity NUMBER,
    price DEC(9,2),
    availability VARCHAR(1) CHECK(availability IN('y','n')),
    cat_id VARCHAR(20),
    manufacturer VARCHAR(100),
    CONSTRAINT pk_product PRIMARY KEY (id, cat_id, manufacturer),
    CONSTRAINT fk_product_cat_id FOREIGN KEY (cat_id)
        REFERENCES category(cat_id),
    CONSTRAINT fk_product_manufacturer FOREIGN KEY (manufacturer)
        REFERENCES manufacturer(name)
);
sql

1 个回答

  1. Best Answer

    需要修复什么。

    1. 表category：不清楚两个字段存在的意义是什么——id和cat_id。其中一个最好删除或重命名，因为在这种形式下它们会令人困惑。同时更改主键 - 保留一个字段：

      CONSTRAINT pk_category PRIMARY KEY (id)

    2. 表manufacturer。
      如果您需要名称的唯一性（在这种情况下通常需要），请更改主键（将其保留为一个字段） - 进行附加约束：

      CONSTRAINT pk_manufacturer PRIMARY KEY (id),
CONSTRAINT uq_manufacturer_name unique (name)

    3. 表product。
      剩下的只是更正列类型和外键：

      cat_id number,
manufacturer_id number,

CONSTRAINT fk_product_cat_id FOREIGN KEY (cat_id)
    REFERENCES category(id),
CONSTRAINT fk_product_manufacturer FOREIGN KEY (manufacturer_id)
    REFERENCES manufacturer(id)
    • 2

相关问题