Добавить уникальное ограничение для комбинации двух столбцов

У меня есть таблица и, как-то, тот же человек дважды попадал в мою таблицу Person. Прямо сейчас первичный ключ - это только номер автонабора, но существуют два других поля, которые я хочу заставить быть уникальными.

Например, поля:

ID  
Name  
Active  
PersonNumber

Я хочу только 1 запись с уникальным PersonNumber и Active = 1.
(Таким образом, сочетание двух полей должно быть уникальным)

Каков наилучший способ для существующей таблицы на SQL-сервере, я могу это сделать, если кто-то еще вставляет с тем же значением, что и существующее значение, он терпит неудачу, поэтому мне не нужно беспокоиться об этом в моем приложении код.

Ответ 1

Как только вы удалили свой дубликат (ы):

ALTER TABLE dbo.yourtablename
  ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);

или

CREATE UNIQUE INDEX uq_yourtablename
  ON dbo.yourtablename(column1, column2);

Конечно, часто бывает лучше сначала проверить это нарушение, прежде чем просто позволить SQL Server попытаться вставить строку и вернуть исключение (исключения дороги).

http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/

Если вы хотите, чтобы исключения не переходили в приложение, без внесения изменений в приложение, вы можете использовать триггер INSTEAD OF:

CREATE TRIGGER dbo.BlockDuplicatesYourTable
 ON dbo.YourTable
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 FROM inserted AS i 
    INNER JOIN dbo.YourTable AS t
    ON i.column1 = t.column1
    AND i.column2 = t.column2
  )
  BEGIN
    INSERT dbo.YourTable(column1, column2, ...)
      SELECT column1, column2, ... FROM inserted;
  END
  ELSE
  BEGIN
    PRINT 'Did nothing.';
  END
END
GO

Но если вы не сообщите пользователю, что они не выполнили вставку, они будут задаваться вопросом, почему данных нет, и никаких исключений не сообщалось.

EDIT вот пример, который делает именно то, о чем вы просите, даже используя те же имена, что и ваш вопрос, и доказывает это. Вы должны попробовать это, прежде чем предполагать, что вышеупомянутые идеи относятся только к одному столбцу или другому, а не к комбинации...

USE tempdb;
GO

CREATE TABLE dbo.Person
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(32),
  Active BIT,
  PersonNumber INT
);
GO

ALTER TABLE dbo.Person 
  ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 0, 22);
GO

-- fails:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

Данные в таблице после всего этого:

ID   Name   Active PersonNumber
---- ------ ------ ------------
1    foo    1      22
2    foo    0      22

Сообщение об ошибке в последней вставке:

Msg 2627, уровень 14, состояние 1, строка 3 Нарушение ограничения UNIQUE KEY "uq_Person". Невозможно вставить дубликат ключа в объект "dbo.Person". Заявление завершено.

Ответ 2

Это также можно сделать в графическом интерфейсе:

Под таблицей "Лицо" щелкните правой кнопкой мыши Индексы
Нажмите/наведите новый указатель
Нажмите Некластеризованный указатель...

Будет указано имя индекса по умолчанию, но вы можете его изменить.
Проверить уникальный флажок
Нажмите кнопку Добавить...

Проверьте столбцы, которые вы хотите включить

Нажмите ОК в каждом окне.