mysql - Cannot add foreign key constraint (GUID)

I developing a synchronization module in vb.net, so for avoid duplicate id on different record I'm using the GUID. Now what I'm trying to do is set the GUID as primary key (PK) on my table. This is the structure of the two table:

USERS

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `GUID` char(36) NOT NULL,
  `first_name` varchar(256) DEFAULT NULL,
  `last_name` varchar(512) DEFAULT NULL,
  `email` varchar(512) DEFAULT NULL,
  `mobile_number` varchar(128) DEFAULT NULL,
  `phone_number` varchar(128) DEFAULT NULL,
  `address` varchar(256) DEFAULT NULL,

  `city` varchar(256) DEFAULT NULL,
  `state` varchar(128) DEFAULT NULL,
  `zip_code` varchar(64) DEFAULT NULL,
  `notes` text,
  `id_roles` bigint(20) unsigned NOT NULL,
  `data` int(11) NOT NULL,
  `lastUpdated` varchar(36),
  PRIMARY KEY (`id`),
  KEY `id_roles` (`id_roles`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8;

USER_SETTINGS

CREATE TABLE IF NOT EXISTS `user_settings` (
  `id_users` bigint(20) unsigned NOT NULL,
  `GUID` char(36) NOT NULL,
  `username` varchar(256) DEFAULT NULL,
  `password` varchar(512) DEFAULT NULL,
  `salt` varchar(512) DEFAULT NULL,

  `working_plan` text,
  `notifications` tinyint(4) DEFAULT '0',
  `google_sync` tinyint(4) DEFAULT '0',
  `google_token` text,
  `google_calendar` varchar(128) DEFAULT NULL,
  `sync_past_days` int(11) DEFAULT '5',
  `sync_future_days` int(11) DEFAULT '5',
  `lastUpdated` varchar(36),
  PRIMARY KEY (`GUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How you can see I've set the FK as GUID field in user_settings table, the GUID is taken from the users table by this:

ALTER TABLE `user_settings`
ADD CONSTRAINT `user_settings_ibfk_1` FOREIGN KEY (`GUID`) REFERENCES `users` (`GUID`) ON DELETE CASCADE ON UPDATE CASCADE;

But I ge this error message:

1215 - Cannot add foreign key constraint

What I did wrong?

Answer

The parent field (field in users) needs to be the primary key. You have the GUID field in the user_settings table defined as primary key. That doesnt make sense. You should have the GUID in the users as primary key, in the user_settings as simple field. Then the relationship works. Or you create an ID field in user_settings and use the 2 id fields to create the foreign key constraint.

CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `GUID` char(36) NOT NULL,
  `first_name` varchar(256) DEFAULT NULL,
  `last_name` varchar(512) DEFAULT NULL,
  `email` varchar(512) DEFAULT NULL,
  `mobile_number` varchar(128) DEFAULT NULL,
  `phone_number` varchar(128) DEFAULT NULL,
  `address` varchar(256) DEFAULT NULL,
  `city` varchar(256) DEFAULT NULL,
  `state` varchar(128) DEFAULT NULL,
  `zip_code` varchar(64) DEFAULT NULL,

  `notes` text,
  `id_roles` bigint(20) unsigned NOT NULL,
  `data` int(11) NOT NULL,
  `lastUpdated` varchar(36),
  PRIMARY KEY (`id`),
  KEY `id_roles` (`id_roles`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `user_settings` (

  `id_users` bigint(20) unsigned NOT NULL,
  `id` bigint(20) unsigned NOT NULL,
  `GUID` char(36) NOT NULL,
  `username` varchar(256) DEFAULT NULL,
  `password` varchar(512) DEFAULT NULL,
  `salt` varchar(512) DEFAULT NULL,
  `working_plan` text,
  `notifications` tinyint(4) DEFAULT '0',
  `google_sync` tinyint(4) DEFAULT '0',
  `google_token` text,

  `google_calendar` varchar(128) DEFAULT NULL,
  `sync_past_days` int(11) DEFAULT '5',
  `sync_future_days` int(11) DEFAULT '5',
  `lastUpdated` varchar(36),
  PRIMARY KEY (`GUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `user_settings`
ADD CONSTRAINT `user_settings_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

With this example all user_settings for a user will be deleted.