CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
Zymus
Zymus

Reputation: 1698

Can two columns from one table have a foreign key to the same column in another table?

I have two tables in a database, Person and 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)
)

I am trying to reference the previous owner, and the current owner for each pet. I have also tried 

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

and

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

but I get the following error:

Error Code: 1215. Cannot add foreign key constraint

Is this even possible to accomplish? Or would I have to create some sort of bridge table to accommodate this?

Upvotes: 5

Views: 6379

Answers (2)

shad
shad

Reputation: 371

Please try the following:

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

Upvotes: 1

Zymus
Zymus

Reputation: 1698

The problem was the order I had the tables defined in the script. I had Pet come before Person. Once I put Person above Pet it worked fine. The example in the question was written from my head, as I didn't have the actual code handy when I posted it.

Upvotes: 0

Related Questions