mariadb - MySQL Using Same Foreign key for two different table columns

Question

I found this thread similar to my queries How to Link Foreign Key with Different Name But unfortunately, with the answer, my problem didn't get solved. As on my table example, it doesn't creates any primary key. All foreign keys only.

Here is the code:

CREATE TABLE ref_data(
user_id INT(11) NOT NULL,
ref_id INT(11) NOT NULL,
ref_name VARCHAR(30) NOT NULL,
ref_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT FK_user_id FOREIGN KEY(user_id) REFERENCES client (user_id),
CONSTRAINT FK_ref_id FOREIGN KEY(ref_id) REFERENCES client (user_id),
CONSTRAINT FK_ref_name FOREIGN KEY(ref_name) REFERENCES client (firstname)
);

It gives me:

errno: 150 "Foreign key constraint is incorrectly formed"

Here I am using user_id two times with 1st as "user_id" 2nd as "ref_id". Also using firstname as "ref_name".

client table:

   CREATE TABLE client (
   `user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(30) NOT NULL UNIQUE KEY,
  `email` VARCHAR(50) NOT NULL UNIQUE KEY,
  `firstname` VARCHAR(30) NOT NULL,
   `lastname` VARCHAR(30) NOT NULL,
  `password` CHAR(128) NOT NULL
  );

Answer 1

Ok, when trying to create the ref_data table, after the foreign key error, I see this:

LATEST FOREIGN KEY ERROR

------------------------ 2017-07-13 01:07:00 37ec Error in foreign key constraint of table ref_data:

FOREIGN KEY(ref_name) REFERENCES client (firstname) ):

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition. Create table 'test.ref_data' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' FOREIGN KEY(ref_name) REFERENCES client (firstname) )'.

What that error is basically saying: (in the bold text)

There is no index on 'firstname' for the 'client' table (the portion after the REFERENCES clause of the FOREIGN KEY

But it's a simple fix. Run this SQL on the client table:

ALTER TABLE `client` ADD INDEX(`firstname`);

... and then run the ref_data table SQL again.