Обеспечение 1:1 и 1: много кардинальности в денормализованном складском столе с составным первичным ключом

У меня есть стол под названием "Accounts"с composite primary key состоящий из 2 столбцов: Account_key а также Account_Start_date оба с типом данных int и другой не ключевой столбец с именем Accountnumber(bigint).

Account_key должен иметь один или несколько Accountnumber(bigint) и не наоборот, означая, что 1 или многие Accountnumber могут иметь только 1 Account_key. Если вы попытаетесь вставить один и тот же Account_key и тот же Account_Start_date, то primary key constraint останавливает это, конечно, потому что они вместе первичный ключ.

Однако, если вы вставляете существующий Account_key с другим, не существующим Account_Start_date, вы можете вставить произвольный Accountnumber по своему усмотрению без каких-либо ограничений на это, и вдруг у вас появятся строки со многими отношениями между Account_key и Accountnumber, и мы этого не хотим.

Я пытался с большим количеством ограничений без какой-либо удачи. Я просто не знаю, что я делаю не так, поэтому, пожалуйста, помогите мне, спасибо! (Примечание: я не думаю, что изменение составного первичного ключа является вариантом, потому что тогда мы потеряем медленно изменяющуюся функциональность даты измерения)

Есть еще одна таблица (случай), где 1 "Account_Key" может быть связан только с 1 "AccountNumber", означающим отношение 1..1, все остальные вещи такие же, за исключением того, что между ними должно быть отношение 1..1. Уникальный индекс не работает для меня, по крайней мере, просто подумайте, хочу ли я изменить Accounts таблица или поставить триггер или даже индекс, так что это будет 1..1 отношение между "Account_Key" и "AccountNumber",?

Источник

3 ответа

Решение

Если бы это была таблица OLTP, решением было бы надлежащим образом нормализовать данные в две таблицы, но это таблица DW, поэтому имеет смысл иметь все это в одной таблице.

В этом случае вы должны добавить FOR / AFTER Спусковой крючок ON INSERT, UPDATE что делает запрос против inserted псевдо-таблицы. Запрос может быть простым COUNT(DISTINCT Account_Key), присоединяясь обратно к основной таблице (чтобы отфильтровать только AccountNumber значения добавляются / обновляются), делая GROUP BY на AccountNumber а потом HAVING COUNT(DISTINCT Account_Key) > 1, Оберните этот запрос в IF EXISTS и если строка возвращается, то выполнить ROLLBACK чтобы отменить операцию DML, RAISERROR отправить сообщение об ошибке, почему операция отменяется, а затем RETURN,

CREATE TRIGGER dbo.TR_TableName_PreventDuplicateAccountNumbers
ON dbo.TableName
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;

IF (EXISTS(
   SELECT COUNT(DISTINCT tab.Account_Key)
   FROM   dbo.TableName tab
   INNER JOIN INSERTED ins
                    ON ins.AccountNumber = tab.AccountNumber
   GROUP BY  tab.AccountNumber
   HAVING COUNT(DISTINCT tab.Account_Key) > 1
   ))
BEGIN
  ROLLBACK;
  RAISERROR(N'AccountNumber cannot be associated with more than 1 Account_Key', 16, 1);
  RETURN;
END;

Для "другой" таблицы, где отношения между Account_Key а также AccountNumber 1:1, вы можете попробовать сделать что-то вроде:

DECLARE @Found BIT = 0;

;WITH cte AS
(
  SELECT DISTINCT tab.Account_Key, tab.AccountNumber
  FROM   dbo.TableName tab
  INNER JOIN INSERTED ins
          ON ins.Account_Key = tab.Account_Key
          OR ins.AccountNumber = tab.AccountNumber
), counts AS
(
  SELECT c.[Account_Key],
         c.[AccountNumber],
         ROW_NUMBER() OVER (PARTITION BY c.[Account_Key
                            ORDER BY c.[Account_Key, c.[AccountNumber]) AS [KeyCount],
         ROW_NUMBER() OVER (PARTITION BY c.[AccountNumber]
                            ORDER BY c.[AccountNumber], c.[Account_Key) AS [NumberCount]
  FROM cte c
)
SELECT @Found = 1
FROM   counts
WHERE  [KeyCount] > 1
OR     [NumberCount] > 1;

IF (@Found = 1)
BEGIN
   ROLLBACK;
   RAISERROR(N'AccountNumber cannot be associated with more than 1 Account_Key', 16, 1);
   RETURN;
END;
Источник

Если я вас правильно понимаю, вы хотите:

  1. Любой данный AccountNumber может быть связан только с одним AccountKey
  2. Любой данный AccountKey может быть связан с несколькими AccountNumbers

Если это правильно, вы можете достичь этого с помощью CHECK CONSTRAINT это вызывает UDF.

РЕДАКТИРОВАТЬ:

Псевдо-логика для CHECK CONSTRAINT может быть:

IF EXISTS anotherRow 
WHERE theOtherAccountNumber = thisAccountNumber 
AND theOtherAccountKey <> thisAccountKey
THEN False (do not allow this row to be inserted)
ELSE True (allow the insertion)

Я бы поместил эту логику в UDF, которая возвращает true или false, чтобы сделать ограничение CHECK проще.

Источник

Создайте дополнительную таблицу AccountKeyNumbers (имя, по вашему выбору, конечно) со столбцами Account_Key и Account_Number.

Сделать Account_Number первичным ключом.

Обратите внимание, что вы не можете добавить Account_Number дважды и, следовательно, не можете связать его с двумя разными Account_Keys в этой таблице.

Теперь вы добавляете дополнительное уникальное ограничение для Account_Number плюс Account_Key. В этой таблице вы положили все номера счетов и соответствующие им ключи.

Наконец, вы определяете внешний ключ в своей таблице Accounts, в столбцах Account_Key плюс Account_Number, ссылаясь на уникальное ограничение в таблице AccountKeyNumbers.

Теперь вы убедились, что в учетные записи могут быть вставлены только действительные комбинации ключ / номер, и что никакие два ключа учетной записи не могут иметь одинаковый номер. Нам потребовалось дополнительное уникальное ограничение, которое не способствует целостности данных таблицы AccountKeyNumbers, просто чтобы позволить создать внешний ключ, который должен указывать на первичное или уникальное ограничение.

Источник
Другие вопросы по тегам