SQL Foregin key “Does not exist”

I’ve been trying to connect my tables with with Foregin key, but I always get an error that my veriable does not exists. I set index for my primary key for counting. My code for table with primary key looks like this:

CREATE TABLE data_animal(
name VARCHAR(30) NOT NULL,
birth_date DATE NOT NULL, 
sex ENUM('Male', 'Female') NOT NULL,
animal_car ENUM('Brave', 'Lazy', 'Agressive', 'Nice', 'Nervous') NOT NULL,
des VARCHAR (250),
type ENUM('Cat', 'Dog', 'Bird', 'Snake', 'Fish') NOT NULL,
steril ENUM('Yes', 'No') NOT NULL,
Breed VARCHAR(20) NOT NULL,
id_animal INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE INDEX idx_id_animal
ON data_animal (id_animal);

My code for another table where I need id_animal as foregin key is:

CREATE TABLE photo(
  id_picture INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  FOREIGN KEY (id_animal) REFERENCES data_animal (id_animal)
);

I tried few defferent approaches how to insert foreign key as:

  id_animal int FOREIGN KEY REFERENCES data_animal(id_animal)

or

CREATE TABLE photo(
  id_picture INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  FOREIGN KEY (id_animal) REFERENCES data_animal (id_animal)
)ENGINE=INNODB;

or

CONSTRAINT data_animal FOREIGN KEY (id_animal)
REFERENCES data_animal(id_animal)

I always get this error:

(Key column ‘id_animal’ doesn’t exist in table)

I don’t know what else could be wrong. Thanks for the help.

Answer

The foreign key has to be a column in the table you’re defining, so you need to add an id_animal column to the photos table before you can make it a foreign key.

CREATE TABLE photo(
  id_picture INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  id_animal INT,
  FOREIGN KEY (id_animal) REFERENCES data_animal (id_animal)
);