MySQL UNIQUE KEY и FOREIGN KEY один и тот же столбец не создается

Question

У меня есть следующие таблицы /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

я пытаюсь создать одну таблицу с пользовательскими данными, а другую - с пользовательскими настройками. Когда я создаю таблицу userSettings, она не создает внешний ключ, что-то не так с синтаксисом создания? Это связано с созданием двух индексов для одного столбца?

Вот что я получаю после создания таблицы userSettings:

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;

Answer 1

Как вы обнаружили, MyISAM не поддерживает внешние ключи. И то и другое users а также userSettings должен быть InnoDB.

[Мне] просто любопытно, является ли хорошей практикой использование UNIQUE_KEY и FOREIGN_KEY в одном столбце

Это означает, что userSettings таблица может иметь не более одной строки для каждого userId, Я думаю, вам нужен только один ряд на userId, потому что вы храните "массив" настроек, закодированных как-то в вашем settingsArray TEXT колонка. Это не очень хорошая практика.

Вы должны либо сохранить каждый параметр в своем собственном столбце:

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`)
) 

Или хранить несколько строк в userId, с одним именем настройки и значением в строке.

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`)
) 

Также удивительно, почему вам нужен id столбец для первичного ключа, если userId уже НЕ NULL и UNIQUE, и это, вероятно, ключ, который вы будете использовать для поиска строк в любом случае. Вы можете сделать userId ПЕРВИЧНЫЙ КЛЮЧ, а также (или userId, setting во втором примере) и пропустите id колонка.

Answer 2

Просто понял, что для пользователей таблицы ENGINE был InnoDB, а для таблицы userSettings ENGINE был MyISAM, изменил это и работал, мне просто любопытно, если использование UNIQUE_KEY и FOREIGN_KEY в одном столбце является хорошей практикой