CODEWITHSUNDEEP
mysqlsql
CodeMed
CodeMed

Reputation: 9191

mysql unable to add foreign key: error 1215 (hy000)

I am getting error 1215 (hy000): cannot add foreign key constraint then I run the following .sql file. The code below is also not creating the documents table, probably because it fails to create its foreign keys. Can anyone show me how to fix the code below so it will create the documents table including its foreign keys without throwing errors: 

CREATE DATABASE IF NOT EXISTS petclinic;
GRANT ALL PRIVILEGES ON petclinic.* TO pc@localhost IDENTIFIED BY 'pc';

USE petclinic;

CREATE TABLE IF NOT EXISTS types (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80),
  INDEX(name)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS documenttypes (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80),
  INDEX(name)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS owners (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  address VARCHAR(255),
  city VARCHAR(80),
  telephone VARCHAR(20),
  INDEX(last_name)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS pets (
  id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30),
  birth_date DATE,
  type_id INT(4) UNSIGNED NOT NULL,
  owner_id INT(4) UNSIGNED NOT NULL,
  INDEX(name),
  FOREIGN KEY (owner_id) REFERENCES owners(id),
  FOREIGN KEY (type_id) REFERENCES types(id)
) engine=InnoDB;

CREATE TABLE IF NOT EXISTS documents (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id int(4) NOT NULL,
  type_id INT(4), 
  name varchar(200) NOT NULL,
  description text NOT NULL,
  filename varchar(200) NOT NULL,
  content mediumblob NOT NULL, 
  content_type varchar(255) NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES owners(id),
  FOREIGN KEY (type_id) REFERENCES documenttypes(id)
) engine=InnoDB;

I tried the advice at this link, but none of those suggestions work with this code. Can someone show me something that does work with this code?

Upvotes: 0

Views: 445

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

The foreign key columns have to be the exact same data type as the original columns.

Try this:

CREATE TABLE IF NOT EXISTS documents (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id int(4) UNSIGNED NOT NULL,
  type_id INT(4) UNSIGNED NOT NULL, 
  name varchar(200) NOT NULL,
  description text NOT NULL,
  filename varchar(200) NOT NULL,
  content mediumblob NOT NULL, 
  content_type varchar(255) NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES owners(id),
  FOREIGN KEY (type_id) REFERENCES documenttypes(id)
) engine=InnoDB;

Added UNSIGNED to client_id and UNSIGNED NOT NULL to type_id (the NOT NULL part in is not mandatory though)

sqlfiddle demo

Upvotes: 2

Related Questions