sql - Oracle：错误 ORA-02270（此列列表没有匹配的唯一键或主键）

Question

我尝试创建 4 个表并将其链接在一起，但它显示此错误 ORA-02270。我怎样才能解决这个问题？当我声明导致此错误的外键和主键时是否有任何错误。

CREATE table City (
    city_name VARCHAR(128),
    state_name VARCHAR(128),
    population number,
    elevation number,
    PRIMARY KEY (city_name, state_name),
    FOREIGN KEY (city_name) REFERENCES Mayor(city_name)
);

CREATE table State (
    state_name VARCHAR(128) PRIMARY KEY,
    region VARCHAR(128),
    FOREIGN KEY (state_name) REFERENCES City(state_name),
    FOREIGN KEY (state_name) REFERENCES Mayor(state_name),
    FOREIGN KEY (state_name) REFERENCES Governor(state_name)
);

CREATE table Mayor (
    mayor_name VARCHAR(128),
    city_name VARCHAR(128),
    state_name VARCHAR(128),
    party VARCHAR(128),
    PRIMARY KEY (mayor_name, city_name, state_name)
);

CREATE table Governor (
    governor_name VARCHAR(128),
    state_name VARCHAR(128),
    party VARCHAR(128),
    PRIMARY KEY (governor_name, state_name)
);

标签： sqldatabaseoracleforeign-keyscreate-table

Answer 1

您的外键都混淆了，导致语法上无效的关系。我理解的关系如下：

• 州长引用州

• 一个城市代表一个国家

• 一位市长提到了一座城市

可能，您还应该有一个region表格，该state表格将引用该表格。

这是一个有效并实现上述关系的新版本：

CREATE table State (
    state_name VARCHAR(128) PRIMARY KEY,
    region VARCHAR(128)
);

CREATE table Governor (
    governor_name VARCHAR(128),
    state_name VARCHAR(128),
    party VARCHAR(128),
    PRIMARY KEY (governor_name, state_name),
    FOREIGN KEY (state_name) REFERENCES State(state_name)
);

CREATE table City (
    city_name VARCHAR(128),
    state_name VARCHAR(128),
    population number,
    elevation number,
    PRIMARY KEY (city_name, state_name),
    FOREIGN KEY (state_name) REFERENCES State(state_name)
);


CREATE table Mayor (
    mayor_name VARCHAR(128),
    city_name VARCHAR(128),
    state_name VARCHAR(128),
    party VARCHAR(128),
    PRIMARY KEY (mayor_name, city_name, state_name),
    FOREIGN KEY (state_name, city_name) REFERENCES City(state_name, city_name)
);

DB Fiddle 上的演示。