две таблицы, созданные с помощью этих скриптов:
create table user_auth
(
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
user_id varchar(36) NOT NULL,
PRIMARY KEY(username, password)
)engine=innodb;
create table user
(
user_id varchar(36) NOT NULL,
PRIMARY KEY(user_id),
FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;
Я получаю сообщение об ошибке: не могу создать таблицу 'xxx.user' (номер ошибки: 150)
Ошибка внешнего ключа.
ЕСЛИ вместо этого я делаю это (удаляю ограничение внешнего ключа, но по-прежнему ссылаюсь на user_auth):
create table user_auth
(
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
user_id varchar(36) NOT NULL,
PRIMARY KEY(username, password)
)engine=innodb;
create table user
(
user_id varchar(36) NOT NULL REFERENCES user_auth(user_id) ON DELETE CASCADE,
PRIMARY KEY(user_id),
)engine=innodb;
Все просто замечательно, НО я могу вставить user_id в таблицу user, не имея соответствующего ключа в user_auth, что создает брешь в моей ссылочной целостности.
А теперь для удовольствия скажу, что я делаю следующее (удалите составной ключ и сделайте user_id первичным ключом в user_auth):
create table user_auth
(
user_id varchar(36) NOT NULL,
PRIMARY KEY(username, password)
)engine=innodb;
create table user
(
user_id varchar(36) NOT NULL,
PRIMARY KEY(user_id),
FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;
Это тоже работает, хотя у меня нет составного имени пользователя и пароля для обеспечения уникальности.
У меня такое чувство, что мне не хватает чего-то очень важного в том, как работает MySQL. Просвети пожалуйста.
Спасибо за ваше время!
ОБНОВЛЕНИЕ
В статье ypercube, на которую ссылается их ответ, в третьем пункте упоминается порядок PK, и соответствующий FK должен быть одинаковым. Если я добавлю user_id
в качестве ПК в user_auth
в следующем порядке, скрипт заработает:
create table user_auth
(
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
user_id varchar(36) NOT NULL,
PRIMARY KEY(user_id, username, password)
)engine=innodb;
create table user
(
user_id varchar(36) NOT NULL,
PRIMARY KEY(user_id),
FOREIGN KEY(user_id) REFERENCES user_auth(user_id) ON DELETE CASCADE
)engine=innodb;
Таким образом, я все еще могу SELECT
и тому подобное, но теперь я не могу создавать повторяющиеся комбинации имени пользователя и пароля, и, таким образом, каждая учетная запись будет уникальной, поскольку должна существовать запись имени пользователя / пароля / идентификатора пользователя, прежде чем я смогу вставить данные пользователя.
user_auth
? - person ypercubeᵀᴹ   schedule 31.01.2013PRIMARY KEY(user_id)
в таблицеuser_auth
(вы говорите, что сделали, но код другой. Вы также можете добавитьUNIQUE KEY (username, password)
или простоUNIQUE KEY (username)
, в зависимости от того, что вам нужно. - person ypercubeᵀᴹ   schedule 31.01.2013user_auth
и ссылаться на таблицуuser
. - person ypercubeᵀᴹ   schedule 31.01.2013user
, прежде чем я сделаюuser_auth
, что неплохо, если, конечно, я не хочу видеть, существует ли уже комбинация имени пользователя и пароля. Затем мне нужно проверить свой PHP-код. Вместо этого наличиеuser
в качестве дочернего элемента означает, что комбинация имени пользователя и пароля должна быть доступна, прежде чем я смогу создать учетную запись пользователя, поэтому он покрывается в MySQL за счет ссылочной целостности. Кроме того, я бы предпочел ссылаться на данные пользователя по идентификатору пользователя, я действительно использую имя пользователя / пароль только для проверки логинов. - person PandemoniumSyndicate   schedule 31.01.2013PRIMARY KEY(user_id, username, password)
. Сделайте этоPRIMARY KEY(user_id), UNIQUE KEY (username, password)
- person ypercubeᵀᴹ   schedule 31.01.2013FOREIGN KEY
Ограничения , где говорится: Отклонение от стандартов SQL: ограничение FOREIGN KEY, которое ссылается на не-UNIQUE ключ, не является стандартным SQL. - person ypercubeᵀᴹ   schedule 31.01.2013