internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12

Question

Can anyone help on how to handle this scenario . I am trying to make a relationship between two tables.

 CREATE TABLE `saccos` (
`id` bigint(20) UNSIGNED NOT NULL,
`saccosName` bigint(20) NOT NULL,
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The above creation succed.

CREATE TABLE customers ( no_c INT NOT NULL UNIQUE AUTO_INCREMENT, 
saccos_id bigint(20) NOT NULL,
first_name VARCHAR(70) NOT NULL, 
PRIMARY KEY(no_c,first_name,saccos_id, 
FOREIGN KEY (saccos_id) REFERENCES saccos (id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The above gives error:

ERROR 1005 (HY000): Can't create table saccos_system.customers (errno: 150 "Foreign key constraint is incorrectly formed")

I had to perfom the below command

 MariaDB [saccos_system]> SHOW ENGINE INNODB STATUS;

I found the below information:

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.

How do I handle this?

Answer 1

You received error:

ERROR 1005 (HY000): Can't create table saccos_system.customers (errno: 150 "Foreign key constraint is incorrectly formed")

Reason: column saccos_id not the same data type with column id (bigint(20) and bigint(20) UNSIGNED)

How to fix it follow below:

1. You need to set them to the same data type (e.g: change column saccos_id bigint(20) UNSIGNED NOT NULL)
2. After if you get this error:

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'customers_ibfk_1' in the referenced table 'saccos'

You can fix by: Add index column id table saccos or simply set it is PRIMARY KEY