Ограничение на уникальность данных в группе записей, где некоторое значение является тем же
DBMS: SQL-сервер MS 2005, стандарт
Я хотел бы сделать ограничение таблицы, чтобы иметь только одну запись, имеют конкретное значение в подмножестве таблицы (где строки совместно используют значение в конкретном столбце). Действительно ли это возможно?
Пример: у Меня есть записи в myTable, которые имеют групповой внешний ключ (fk1), и немного столбца, названного isPrimary для размечания того этого конкретного, должно использоваться нашим приложением для специальной логики.
в кратком обзоре это похоже на это:
myTable
-------------
pk1 (int, not null)
name (varchar(50), null)
fk1 (int, not null)
isPrimary (bit, not null)
Я хочу удостовериться, что существует одна и только одна запись с набором флага isPrimary к 1 для каждого уникального значения fk1.
Пример данных: Это должно быть законно:
pk1 name fk1 isPrimary
---- ----- ----- ----------
1 Bill 111 1
2 Tom 111 0
3 Dick 222 1
4 Harry 222 0
Но это не должно быть (больше чем один где fk=111):
pk1 name fk1 isPrimary
---- ----- ----- ----------
1 Bill 111 1
2 Tom 111 1
3 Dick 222 1
4 Harry 222 0
И ни один не должен это (ни один где fk=222):
pk1 name fk1 isPrimary
---- ----- ----- ----------
1 Bill 111 1
2 Tom 111 0
3 Dick 222 0
4 Harry 222 0
Существует ли способ сделать это с ограничением таблицы?
ОБНОВЛЕНИЕ я пошел с ответом Martin Smith на данный момент, хотя я буду стремиться к JohnFx, осуществляет рефакторинг в предстоящем выпуске, поскольку это - лучшее долгосрочное решение. Однако я хотел отправить свой обновленный UDF на основе ответа Raze2dust, в случае, если будущие читатели решают, что это - лучшее пригодное для их потребностей.
CREATE FUNCTION [dbo].[OneIsPrimaryPerFK1](@fk1 INT, @dummyIsPrimary BIT)
RETURNS INT
AS
BEGIN
DECLARE @retval INT;
DECLARE @primarySum INT;
SET @retval = 0;
DECLARE @TempTable TABLE (
fk1 INT,
PrimarySum INT)
INSERT INTO @TempTable
SELECT fk1, SUM(CAST(isPrimary AS INT)) AS PrimarySum
FROM FacAdmin
WHERE fk1 = @fk1
GROUP BY fk1;
SELECT @primarySum = PrimarySum FROM @TempTable;
IF(@primarySum=1)
BEGIN
SET @retval = 1
END
RETURN @retval
END;
Изменения:
- Используемый @tempTable, а не
поддающийся соблазну (в памяти v. записанный в диск) как требуется udf
- переданный @fk1 в качестве параметра, таким образом, я могу выбрать для уникальности в одной группе значений fk1.
- хитрый должен был также передать isPrimary даже при том, что это не необходимо для логики функции, иначе оптимизатор SQL2005 не выполнит проверочное ограничение, когда isPrimary будет обновлен.
4 ответа
Использование UDF в проверочных ограничениях может не сработать при изоляции моментального снимка или многорядном обновлении.
Предполагая, что все ваши значения fk1 и pk1 в настоящее время (и всегда будут) положительными, вы можете создать вычисляемый столбец со следующим определением
CASE WHEN isPrimary = 1 THEN fk1 ELSE -pk1 END
а затем добавить к нему уникальное ограничение. Или, если это предположение не может быть сделано, тогда, возможно,
CASE WHEN isPrimary = 0 THEN 1.0/pk1 ELSE fk1 END
SQL 2005 не предоставляет возможности применять предложение Where к уникальному индексу, такому как SQL 2008. Однако есть несколько способов решить проблему в SQL 2005:
- Создайте Индексированное представление, которое фильтрует IsPrimary = 1 и добавляет уникальный индекс к этому представлению.
- Создайте триггер, в котором убедитесь, что только один может быть основным.
- Инкапсулируйте вашу логику в сохраненную процедуру и заставьте пользователей пройти через сохраненную процедуру для вставки или обновления из вашей таблицы.
Вы можете попробовать создать функцию, а затем использовать контрольное ограничение:
CREATE FUNCTION ChkFn()
RETURNS INT
AS
BEGIN
DECLARE @retval INT
DECLARE @distinct INT
DECLARE @top INT
SET @retval = 0
SELECT fk1 AS ForeignKey, SUM(isPrimary) AS PrimarySum
INTO #TempTable
FROM myTable
GROUP BY fk1
SELECT @distinct = COUNT(DISTINCT(PrimarySum)) FROM #TempTable
SELECT @top = top PrimarySum FROM #TempTable
IF(@distinct=1 AND @top=1)
BEGIN
@retval = 1
END
RETURN @retval
END;
GO
ALTER TABLE myTable
ADD CONSTRAINT chkFkPk CHECK (dbo.ChekFn() = 1 );
GO
Попробуйте и дайте мне знать, если это сработало. Хотя это не очень элегантно...
Начал новый ответ, так как сильно исказил первый.
Похоже, вы можете решить эту проблему, немного переосмыслив дизайн вашей таблицы, чтобы избежать перебора ограничений для реализации вашего бизнес-правила.
Как насчет удаления столбца IsPrimary из MyTable и добавления столбца PrimaryPersonID в другую таблицу, которая ссылается на первичного человека?
Таким образом, сама структура обеспечит, чтобы 1 и только 1 запись в таблице FK была первичной для каждого человека.