Cannot add foreign key constraint (GUID)

Question

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?

"How you can see I've set the PK as GUID field in user_settings table" - No, you tried to add the FK but not the PK. — Daniel W., Dec 29 '15 at 11:18
Yes I wrong to type, anyway, I want use the GUID PK of user_settings reference to the GUID of users table. I edited the wrong typo. — Dillinger, Dec 29 '15 at 11:20

score 3 · Accepted Answer · answered Dec 29 '15 at 11:30

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.

score 0 · Answer 2 · answered Dec 29 '15 at 11:33

You are trying to use one column i.e. 'GUID' of user_setting table as primary key as well as foreign key. You can't use same column as PK and FK.

A Primary Key is used to enforce uniqueness within a table, and be a unique identifier for a certain record.

A Foreign Key is used for referential integrity, to make sure that a value exists in another table.

The Foreign key needs to reference the primary key in another table.

If you want to have a foreign key that is also unique, you could make a FK constraint and add a unique index/constraint to that same field.

Instead make 'id_users' column a primary key and 'GUID' column a foreign key of 'user_setting' table. And make 'GUID' column of 'users' table a primary key. Then, it will work fine.

Cannot add foreign key constraint (GUID)

2 Answers2