mysql - 一个表中的两列是否可以具有指向另一个表中同一列的外键？

Question

我在数据库中有两个表，Person 和 Pet。

CREATE TABLE Person (
    id INT NOT NULL,
    PRIMARY KEY (id)
)

CREATE TABLE Pet (
    id INT NOT NULL,
    original_owner INT NOT NULL,
    current_owner INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (original_owner)
        REFERENCES Person(id),
    FOREIGN KEY (current_owner)
        REFERENCES Person(id)
)

我正在尝试引用每只宠物的前任主人和现任主人。我也试过了

CREATE TABLE Pet (
    id INT NOT NULL,
    original_owner INT NOT NULL,
    current_owner INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (original_owner, current_owner)
        REFERENCES Person(id, id)
)

和

CREATE TABLE Pet (
    id INT NOT NULL,
    original_owner INT NOT NULL,
    current_owner INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (original_owner, current_owner)
        REFERENCES Person(id)
)

但我收到以下错误:

Error Code: 1215. Cannot add foreign key constraint

这甚至有可能实现吗？或者我是否必须创建某种桥接表来适应这种情况？

Answer 1

请尝试以下操作:

CREATE TABLE IF NOT EXISTS `pet` (  
  `id` int(11) NOT NULL,  
  `original_owner` int(11) NOT NULL,  
  `current_owner` int(11) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `origin` (`original_owner`),  
  KEY `current` (`current_owner`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `pet`  
  ADD CONSTRAINT `pet_ibfk_2` FOREIGN KEY (`current_owner`) REFERENCES `person` (`id`),  
  ADD CONSTRAINT `pet_ibfk_1` FOREIGN KEY (`original_owner`) REFERENCES `person` (`id`);