我只想只有3列(downure_city_id，arrival_city_id，emp_number).但是，当我插入值

Question

对于EPMS表，我只想拥有3列（Deckiture_city_id，arrary_city_id，emp_number）。但是，当我插入值时，它说我有4列。额外的city_id。我想要的是避免在此表中使用city_id列。我声明它是因为我需要它作为FK。

CREATE TABLE City (
City_Id char(3),
state varchar(30),
Primary key (City_Id)
);

create table Emps (
Emp_number varchar(30) primary key,
City_Id char(3),
departure_City_Id char(3),
arrival_City_Id char(3),
 FOREIGN KEY (City_Id)
    REFERENCES City(City_Id),
 FOREIGN KEY (City_Id)
    REFERENCES City(City_Id)
);

Answer 1

这很简单，只需指定例如, FOREIGN KEY (arrival_city_id) REFERENCES city(city_id)，您将获得索引。

CREATE TABLE emps (
  emp_number          varchar(30)  NOT NULL,
  city_id             char(3)      NOT NULL,
  departure_city_id   char(3)      NOT NULL,
  arrival_city_id     char(3)      NOT NULL,
  PRIMARY KEY (emp_number),
  KEY city_id (city_id),
  KEY departure_city_id (departure_city_id),
  KEY arrival_city_id (arrival_city_id),
  CONSTRAINT emps_ibfk_1 FOREIGN KEY (city_id) REFERENCES city (city_id),
  CONSTRAINT emps_ibfk_2 FOREIGN KEY (departure_city_id) REFERENCES city (city_id),
  CONSTRAINT emps_ibfk_3 FOREIGN KEY (arrival_city_id) REFERENCES city (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Answer 2

"我要避免在此表中拥有city_id列。我声明它是因为我需要它为fk。"

但是您不需要它。外键列是到达和出发列，这些是您必须在约束声明中引用的列：

create table Emps (
    Emp_number varchar(30) primary key,
    departure_City_Id char(3),
    arrival_City_Id char(3),
     FOREIGN KEY (departure_City_Id)
        REFERENCES City(City_Id),
     FOREIGN KEY (City_Id)
        REFERENCES City(arrival_City_Id)
);

命名约束是可选的，但是在调试外键故障时，您可能会发现它很有帮助，尤其是当表具有多个参考同一父键的多个约束时。