Sql-server – Unique-like constraint in a complex scenario

sql serversql-server-2016

I have a table in SQL Server 2016, as shown below. Records are inserted into the table each quarter, for all partners.

Constraint Needed: Should allow only one BatchID for a partnerID for a year-quarter, for a Distribution_Type. However it is allowed to have multiple records with same BatchID for the partner for a given year-quarter, for a distribution type.

How to create such a constraint in the table? If it can be done without trigger that would be great.

enter image description here

Table

CREATE TABLE [dbo].[Distribution]
(
    [Distribtution_Result_ID] [int] IDENTITY(1,1) NOT NULL,
    [PartnerID] [int] NOT NULL,
    [Distribution_Year] [int] NOT NULL,
    [Distribution_Quarter] [int] NOT NULL,
    [Distribution_Amount] [decimal](16, 4) NULL,
    [Distribution_Type] [varchar](100) NULL,
    [Batch_ID] [int] NOT NULL
)

Best Answer

To explain ypercubeᵀᴹ's comment:

Yes, this can be solved if you add a second table and an FK from this to the second table

If the requirement is one BatchId per PartnerId,Year,Quarter,Distribution_Type then you will need a table such as:

CREATE TABLE dbo.Partner_Batch --Or whatever
(
    PartnerID             INT           NOT NULL,
    Distribution_Year     INT           NOT NULL,
    Distribution_Quarter  INT           NOT NULL,
    Distribution_Type     VARCHAR(100)  NOT NULL,
    Batch_ID              INT           NOT NULL,
    CONSTRAINT PK_Partner_Batch PRIMARY KEY (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type)
)

Of course you'd need the relevant FK references for PartnerID and Batch_ID as well as something to ensure Quarter is only valid values (1,2,3,4).

You would then modify your existing table with the foreign key constraint to ensure the rule is enforced:

CREATE TABLE [dbo].[Distribution]
(
    Distribtution_Result_ID   INT IDENTITY(1,1) NOT NULL,
    PartnerID                 INT               NOT NULL,
    Distribution_Year         INT               NOT NULL,
    Distribution_Quarter      INT               NOT NULL,
    Distribution_Amount       DECIMAL(16,4)     NULL,
    Distribution_Type         VARCHAR(100)      NOT NULL, --Note: This column cannot be NULL
    Batch_ID                  INT               NOT NULL,
    CONSTRAINT FK_Distribution_Partner_Batch FOREIGN KEY (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type) REFERENCES dbo.Partner_Batch (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type) --I would use a more descriptive name for the relation, but I don't have enough information about your system
)

From afar, there's a lot more work to be done, but at minimum I'd say you'd need to:

  1. Have a reference table for Distribution_Type to only allow valid values.
  2. Trim down the data type for Distribution_Type or substitute a shortname/code.
  3. Define an actual key for Distribution other than the row identifier.
Related Question