Oracle: Error ORA-02270 (no matching unique or primary key for this column-list)

Question

I try to create 4 tables and link it together but it shows this error ORA-02270. How can I fix this? Is there any error when I declaring foreign and primary key that leads to this error.

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)
);

Answer 1

Your foreign keys are all mixed up, resulting in syntacically invalid relations. I understand the relationships as follows:

• a governor references a state

• a city refences a state

• a mayor references a city

Possibly, you should also have a region table, which the state table would reference.

Here is a new versin that is valid and implements the above relations:

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)
);

Demo on DB Fiddle.