Abills. Solving the error Cannot change column ‘id’: used in a foreign key constraint ‘users_contacts_ibfk_2’

Once executed db_check.pl on a server with Abills 0.78.16:

cd /usr/abills/misc/db_check
./db_check.pl

And got an error:

Current: SMALLINT(6) AUTO_INCREMENT NOT NULL
Change to : SMALLINT(6) UNSIGNED AUTO_INCREMENT NOT NULL
ALTER TABLE `users_contact_types` MODIFY COLUMN `id` SMALLINT(6) UNSIGNED AUTO_INCREMENT NOT NULL;
DBD::mysql::db do failed: Cannot change column 'id': used in a foreign key constraint 'users_contacts_ibfk_2' of table 'abills.users_contacts' at /usr/abills/misc/db_check/../../Abills/mysql/dbcore.pm line 262, <STDIN> line 3.

Current: SMALLINT(6)
Change to : SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0
ALTER TABLE `users_contacts` MODIFY COLUMN `type_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0;
DBD::mysql::db do failed: Cannot change column 'type_id': used in a foreign key constraint 'users_contacts_ibfk_2' at /usr/abills/misc/db_check/../../Abills/mysql/dbcore.pm line 262, <STDIN> line 4

I tried to disable the “FOREIGN KEY” check and execute sql queries again, but other errors occurred:

SET FOREIGN_KEY_CHECKS = 0;
SET GLOBAL FOREIGN_KEY_CHECKS=0;

ALTER TABLE `users_contact_types` MODIFY COLUMN `id` SMALLINT(6) UNSIGNED AUTO_INCREMENT NOT NULL;
ERROR 1025 (HY000): Error on rename of './abills/#sql-33e_4eccf' to './abills/users_contact_types' (errno: 150 - Foreign key constraint is incorrectly formed)

ALTER TABLE `users_contacts` MODIFY COLUMN `type_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0;
ERROR 1025 (HY000): Error on rename of './abills/#sql-33e_4eccf' to './abills/users_contacts' (errno: 150 - Foreign key constraint is incorrectly formed)

The structure of the tables was as follows:

CREATE TABLE `users_contacts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  `type_id` smallint(6) DEFAULT NULL,
  `value` varchar(250) NOT NULL DEFAULT '',
  `priority` smallint(6) unsigned NOT NULL DEFAULT '0',
  `comments` text,
  PRIMARY KEY (`id`),
  KEY `type_id` (`type_id`),
  KEY `_uid_contact` (`uid`),
  CONSTRAINT `users_contacts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE,
  CONSTRAINT `users_contacts_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `users_contact_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12002 DEFAULT CHARSET=utf8 COMMENT='Main user contacts table'

CREATE TABLE `users_contact_types` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `is_default` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `hidden` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='Types of user contacts'

CREATE TABLE `users` (
  `id` varchar(20) NOT NULL DEFAULT '',
  `activate` date NOT NULL DEFAULT '0000-00-00',
  `expire` date NOT NULL DEFAULT '0000-00-00',
  `credit` double(10,2) NOT NULL DEFAULT '0.00',
  `reduction` double(6,2) NOT NULL DEFAULT '0.00',
  `reduction_date` date NOT NULL DEFAULT '0000-00-00',
  `registration` date DEFAULT '0000-00-00',
  `password` blob NOT NULL,
  `uid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `gid` smallint(6) unsigned NOT NULL DEFAULT '0',
  `disable` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `company_id` int(11) unsigned NOT NULL DEFAULT '0',
  `bill_id` int(11) unsigned NOT NULL DEFAULT '0',
  `ext_bill_id` int(10) unsigned NOT NULL DEFAULT '0',
  `credit_date` date DEFAULT '0000-00-00',
  `domain_id` smallint(6) unsigned NOT NULL DEFAULT '0',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `id` (`domain_id`,`id`),
  KEY `bill_id` (`bill_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8013 DEFAULT CHARSET=utf8 COMMENT='Users list'

Solved the problem by removing both “FOREIGN KEY” from the users_contacts table:

ALTER TABLE users_contacts DROP FOREIGN KEY users_contacts_ibfk_1;
ALTER TABLE users_contacts DROP FOREIGN KEY users_contacts_ibfk_2;

After that, I executed the SQL queries of db_check.pl and then created both “FOREIGN KEY” as before:

ALTER TABLE users_contacts
  ADD FOREIGN KEY (uid)
        REFERENCES users(uid)
        ON DELETE CASCADE;

ALTER TABLE users_contacts
  ADD FOREIGN KEY (type_id)
        REFERENCES users_contact_types(id)
        ON DELETE CASCADE;

See also my article:
Installing and configuring the billing system ABillS