MySQL UNIQUE KEY and FOREIGN KEY same column not getting created

Question

I have following tables/CREATE sintaxis:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parentId` int(10) unsigned DEFAULT NULL,
  `fullName` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `alias` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  UNIQUE KEY `uk_parentId_fullName_alias` (`parentId`,`fullName`,`alias`),
  KEY `fk_users_parentId` (`parentId`),
  CONSTRAINT `fk_users_parentId` FOREIGN KEY (`parentId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `userSettings` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) unsigned NOT NULL,
  `settingsArray` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userId` (`userId`),
  KEY `fk_userSettings_userId` (`userId`),
  CONSTRAINT `fk_userSettings_userId` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

im trying to create one table with user data and another one with the user settings, when i create the userSettings table it doesnt create the foreign key, is there something wrong with the create sintaxis? It is related with creating two indexes for same column?

Here what i get after creating the userSettings table:

CREATE TABLE `userSettings` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) unsigned NOT NULL,
  `settingsArray` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userId` (`userId`),
  KEY `fk_userSettings_userId` (`userId`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

@MohammadAliTaqvazadeh no error shown, it just creates the table as above — DSB, Nov 15 '18 at 20:52

score 2 · Answer 1 · answered Nov 15 '18 at 21:10

As you discovered, MyISAM doesn't support foreign keys. Both users and userSettings must be InnoDB.

[I'm] just curious if having a UNIQUE_KEY and FOREIGN_KEY in same column is a good practice

This means the userSettings table can have at most one row for each userId. I guess you need only one row per userId because you store an "array" of settings encoded somehow in your settingsArray TEXT column. This is not a good practice.

You should either store each setting in its own column:

CREATE TABLE `userSettings` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) unsigned NOT NULL,
  `isAdmin` bool NOT NULL,
  `timezone` varchar(10) NOT NULL,
  `theme` varchar(10) NOT NULL,
  ...other settings...
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userId` (`userId`),
  KEY `fk_userSettings_userId` (`userId`)
)

Or else store multiple rows per userId, with one setting name and value per row.

CREATE TABLE `userSettings` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) unsigned NOT NULL,
  `setting` varchar(20) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userId` (`userId`,`setting`),
  KEY `fk_userSettings_userId` (`userId`)
)

It's also puzzling why you need an id column for the primary key, if the userId is already NOT NULL and UNIQUE, and that's probably the key you'll use to look up rows anyway. You can make the userId the PRIMARY KEY as well (or userId, setting in the second example), and omit the id column.

Thanks, why is this considered a bad practice? Even tho every user has different settings and many users will have lots of columns empty its better to do it separately? — DSB, Dec 09 '18 at 06:08
Using a TEXT to store any array of settings means you can't do efficient searches for which users have a given setting. You can't use data types, indexes, constraints, either. You can't update the settings without fetching the whole array into your application and reformatting it. But if you store one setting at a time (like either of the designs I showed above), you have both more structure and more optimization. — Bill Karwin, Dec 09 '18 at 06:52

score 1 · Answer 2 · answered Nov 15 '18 at 20:55

1

Just realized for the table users the ENGINE was InnoDB and for the userSettings table ENGINE was MyISAM, changed that and worked, im just curious if having a UNIQUE_KEY and FOREIGN_KEY in same column is a good practice

answered Nov 15 '18 at 20:55

DSB

597
2
6
11

MySQL UNIQUE KEY and FOREIGN KEY same column not getting created

2 Answers2