I have a problem with creating a new branch_id column in the table tn_whatsapp.
Below are my SQL queries steps :
1. ALTER TABLE tn_whatsapp ADD branch_id varchar(40) NOT NULL AFTER tenant_id;
2. ALTER TABLE `tn_whatsapp` CHANGE COLUMN `branch_id` `branch_id` VARCHAR(40) NOT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `tenant_id`;
3. ALTER TABLE `tn_whatsapp` ADD CONSTRAINT `ibfk_branch_id` FOREIGN KEY (`branch_id`) REFERENCES `tn_branch` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
Only in Step 3 query cannot add a foreign key and the error messages show -
SQL Error (3780): Referencing column 'branch_id' and referenced column 'id' in foreign key constraint 'ibfk_branch_id' are incompatible.
This is my tn_whatapps table structure:
This is my tn_branch table structure, id is branch_id in the tn_whatsapp table
I want the expected result like the below picture:
I am using HeidiSQL - version 12.0.0.6468
I have followed this StackOverflow answer ERROR 3780: Referencing column and referenced column in foreign key constraint are incompatible also cannot solve my problem.
May I know which parts I am getting wrong in step 3? Thanks.
