CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldata-warehouse
Hankman3000
Hankman3000

Reputation: 115

Enforcing 1:1 and 1:Many cardinality in denormalized warehouse table with composite Primary Key

I have a table called "Accounts" with a composite primary key consisting of 2 columns: Account_key and Account_Start_date both with the datatype int and another non key column named Accountnumber(bigint).

Account_key should have one or many Accountnumber(bigint) and not the other way around meaning 1 or many Accountnumber can only have 1 Account_key. If you try to insert same Account_key and same Account_Start_date then the primary key constraint is stopping this of course because they are together primary key.

However if you insert existing Account_key with different non existing Account_Start_date then you could insert a random Accountnumber as you wish without any constraint complaining about it, and suddenly you have rows with many to many relations between Account_key and Accountnumber and we dont want that.

I have tried with a lot of constrains without any luck. I just don't know what I am doing wrong here so please go ahead and help me on this, thanks! (Note: I dont think changing the composite primary key is an option because then we will loose the slowly changing dimension date functionality)

There is another table (case) where 1 'Account_Key' can only be related to 1 'AccountNumber' meaning 1..1 relation, all other things is the same except that there should be 1..1 relation between them. Unique index havent work for me at least, just consider if I wanted to change Accounts table or put a trigger or even a Index so it will be 1..1 relation between 'Account_Key' and 'AccountNumber', ?

Upvotes: 1

Views: 144

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48874

If this were an OLTP table the solution would be to properly normalize the data into two tables, but this is a DW table so it makes sense to have it all in one table.

In this case, you should add a FOR / AFTER Trigger ON INSERT, UPDATE that does a query against the inserted pseudo-table. The query can be a simple COUNT(DISTINCT Account_Key), joining back to the main table (to filter on just the AccountNumber values being added/updated), doing a GROUP BY on AccountNumber and then HAVING COUNT(DISTINCT Account_Key) > 1. Wrap that query in an IF EXISTS and if a row is returned, then execute a ROLLBACK to cancel the DML operation, a RAISERROR to send the error message about why the operation is being cancelled, and then 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;

For the "other" table where the relationship between Account_Key and AccountNumber is 1:1, you might could try doing something like:

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;

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

If I understand you correctly, you want:

  1. Any given AccountNumber can only be related to one AccountKey
  2. Any given AccountKey can be related to multiple AccountNumbers

If this is correct, you can achieve this with a CHECK CONSTRAINT that calls a UDF.

EDIT:

Psuedo-logic for the CHECK CONSTRAINT could be:

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

I would put this logic in a UDF that returns true or false to make the CHECK constraint simpler.

Upvotes: 0

Henk Kok
Henk Kok

Reputation: 383

Create an extra table AccountKeyNumbers (name is your choice of course) with columns Account_Key and Account_Number.

Make Account_Number the primary key.

Note that you can't add an Account_Number twice and therefore, can't link it to two different Account_Keys in this table.

Now, you add an extra unique constraint on Account_Number plus Account_Key. In this table you put all the account numbers and their corresponding keys.

Finally, you define a foreign key on your Accounts table, on the columns Account_Key plus Account_Number, referencing the unique constraint in the AccountKeyNumbers table.

You've now ensured that only valid key/number combinations can be inserted into Accounts and no two AccountKeys can have the same number. We needed the extra unique constraint, which doesn't contribute to the data integrity of the AccountKeyNumbers table, just to enable creating a foreign key, which must point to a primary or unique constraint.

Upvotes: 0

Related Questions