Ограничение SQL UNIQUE для нескольких столбцов. Должен ли составной из них быть уникальным или хотя бы один из них?
Я здесь новичок в SQL, и я играю с ограничением UNIQUE при создании таблицы. Я, очевидно, не понимаю здесь чего-то фундаментального, так что терпите меня.
Допустим, я хочу, чтобы у пользователя было уникальное имя пользователя и уникальный адрес электронной почты. Другими словами, я не хочу иметь двух пользователей с одинаковым именем пользователя или двух пользователей с одинаковым адресом электронной почты.
У меня есть таблица User, определенная следующим образом:
CREATE TABLE User
(
id int,
username nvarchar(20),
email nvarchar(20),
PRIMARY KEY (id),
UNIQUE(username, email)
);
С ограничением UNIQUE, определенным выше, кажется, что следующие вставки будут успешными:
INSERT INTO User (id, username, email)
VALUES
(1, 'aaron', 'aaron-email'),
(2, 'aaron', 'aaron-email-2'),
(3, 'bob', 'bob-email'),
(4, 'bob', 'aaron-email');
Он требует, чтобы комбинация имени пользователя и электронной почты была уникальной, чтобы ограничение сработало. Однако это, очевидно, не имеет смысла, поскольку теперь у нас есть несколько пользователей с одним и тем же именем пользователя и несколько пользователей с одним и тем же адресом электронной почты.
Как обеспечить уникальность столбцов имени пользователя и адреса электронной почты?
Чтобы сделать их не обнуляемыми, добавьте not null после id, username и email.
Ответы 1
Синтаксис UNIQUE(username, email) предполагает, что комбинация столбцов должна быть уникальной.
Вы можете добавить несколько отдельных ограничений UNIQUE, чтобы применить уникальные условия для каждого из столбцов. Используемый синтаксис позволяет использовать несколько операторов:
CREATE TABLE User (
id int,
username nvarchar(20),
email nvarchar(20),
PRIMARY KEY (id),
UNIQUE(username),
UNIQUE(email)
);
INSERT INTO User (id, username, email)
VALUES
(1, 'aaron', 'aaron-email'),
(2, 'aaron', 'aaron-email-2'),
(3, 'bob', 'bob-email'),
(4, 'bob', 'aaron-email')
;
Duplicate entry 'aaron' for key 'User.username'
Пример: https://dbfiddle.uk/07o7fr-C
P.S. Того же результата можно добиться и с другим (и более простым) синтаксисом, добавив квалификаторы UNIQUE к самим столбцам, например:
CREATE TABLE User (
id int,
username nvarchar(20) UNIQUE,
email nvarchar(20) UNIQUE,
PRIMARY KEY (id)
);
К вашему сведению, поведение может отличаться в разных реализациях SQL-сервера, если ваши столбцы могут быть нулевыми. В некоторых реализациях ваши столбцы в настоящее время обнуляемые, потому что это значение по умолчанию. Возможно, вы захотите это проверить. Например, если ваш столбец допускает значение NULL в MariaDB, то Мария фактически игнорирует этот столбец, если он является частью уникального ограничения. (Что странно и не имеет смысла, но именно так это реализовано.)