Foreign key constrains in mysql when trying to add foreign key relationship

Question

I got two tables called activity and user. I need to make foreign key relationship with these two tables. username is the primary key of my user table which is a varchar value. But when I am trying to make the relationship I get a foreign key constrain error with mysql error code 1215. Below is my DDL statements related to two tables I mentioned.

Can anybody please help me to resolve this.

thanks

CREATE TABLE `user` (
  `username` varchar(50) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `password` varchar(500) DEFAULT NULL,
  `activated` tinyint(1) DEFAULT '0',
  `activationkey` varchar(50) DEFAULT NULL,
  `resetpasswordkey` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`username`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE activity (
  activity_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  activity_type_id     INT UNSIGNED,
  activity_property_id  INT UNSIGNED,
  added_by             varchar(50),
  updated_by           varchar(50),
  activity_code        VARCHAR(50),
  activity_description VARCHAR(100),
  start_date           TIMESTAMP NULL,
  end_date             TIMESTAMP NULL,
  start_time           VARCHAR(10),
  end_time             VARCHAR(10),
  added_date           TIMESTAMP NULL,
  updated_date         TIMESTAMP NULL,
  is_available         TINYINT(1),
  PRIMARY KEY (activity_id),

  CONSTRAINT fk_ADDED_BY_FOR_ACTIVITY FOREIGN KEY (added_by) REFERENCES user (username),
  CONSTRAINT fk_UPDATED_BY_FOR_ACTIVITY FOREIGN KEY (updated_by) REFERENCES user (username)
)
  ENGINE = InnoDB;

Answer 1

Just add DEFAULT CHARSET=latin1. Try out the below query.

    CREATE TABLE activity (
  activity_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  activity_type_id     INT UNSIGNED,
  activity_property_id  INT UNSIGNED,
  added_by             VARCHAR(50) DEFAULT NULL,
  updated_by           VARCHAR(50) DEFAULT NULL,
  activity_code        VARCHAR(50),
  activity_description VARCHAR(100),
  start_date           TIMESTAMP NULL,
  end_date             TIMESTAMP NULL,
  start_time           VARCHAR(10),
  end_time             VARCHAR(10),
  added_date           TIMESTAMP NULL,
  updated_date         TIMESTAMP NULL,
  is_available         TINYINT(1),
  PRIMARY KEY (activity_id),
  CONSTRAINT fk_ADDED_BY_FOR_ACTIVITY FOREIGN KEY (added_by) REFERENCES user (username),
  CONSTRAINT fk_UPDATED_BY_FOR_ACTIVITY FOREIGN KEY (updated_by) REFERENCES user (username)
)
  ENGINE = InnoDB DEFAULT CHARSET=latin1;

Answer 2

Your mistake is that FK column types don't match the PK column types. In particular, the character sets differ. Read the documentation sections re foreign keys.

13.1.18.6 Using FOREIGN KEY Constraints

• Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

So activity needs

ENGINE=InnoDB DEFAULT CHARSET=latin1;

You will also find

In addition to SHOW ERRORS, in the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.