Veiller à ce qu'une et une seule valeur par défaut soit définie pour un ensemble

Question

J'ai une table client qui a une relation un à plusieurs avec une table adresse. Je veux contraindre la base de données pour qu'un client avec auront toujours une (et une seule) adresse par défaut.

Je peux facilement ajouter une contrainte pour m'assurer qu'il n'y a jamais qu'une seule adresse par défaut pour chaque client. J'ai cependant du mal à savoir comment appliquer une contrainte qui garantisse qu'une adresse soit toujours marquée comme adresse par défaut.

En résumé :

• Un client n'est pas tenu d'avoir une adresse.
• Si un client a des adresses, il doit y avoir une adresse par défaut.
• Il ne peut y avoir qu'une seule adresse par défaut par client.

Voici un exemple du problème et quelques tests "unitaires". J'utilise une table de liens pour relier les clients et les adresses.

CREATE TABLE Customer
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

CREATE TABLE [Address]
(
    Id INT PRIMARY KEY,
    Address VARCHAR(500) NOT NULL
)

CREATE TABLE CustAddress
(
    CustomerId INT,
    AddressId INT,
    [Default] BIT NOT NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customer(Id),
    FOREIGN KEY (AddressId) REFERENCES [Address](Id)
)

INSERT INTO Customer VALUES (1, 'Mr Greedy')

INSERT INTO [Address] VALUES (1, 'Roly-Poly House, Fatland')
INSERT INTO [Address] VALUES (2, 'Giant Cottage, A Cave')

-- Should succeed
INSERT INTO CustAddress VALUES (1, 1, 1)
INSERT INTO CustAddress VALUES (1, 2, 0)

DELETE FROM CustAddress

-- Should fail as no default address set
INSERT INTO CustAddress VALUES (1, 1, 0)

DELETE FROM CustAddress

-- Should fail as we end up with no defualt address set
INSERT INTO CustAddress VALUES (1, 1, 1)
INSERT INTO CustAddress VALUES (1, 2, 0)
UPDATE CustAddress SET [Default] = 0 WHERE CustomerId = 1 AND AddressId = 1

DELETE FROM CustAddress

-- Should fail as we end up with no defualt address set
INSERT INTO CustAddress VALUES (1, 1, 1)
INSERT INTO CustAddress VALUES (1, 2, 0)
DELETE FROM CustAddress WHERE CustomerId = 1 AND AddressId = 1

Answer 1

Pourquoi ne pas modifier le schéma en

CREATE TABLE Customer
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

CREATE TABLE [Address]
(
    Id INT PRIMARY KEY,
    Address VARCHAR(500) NOT NULL
)

CREATE TABLE CustDefaultAddress
(
    CustomerId INT PRIMARY KEY, /*Ensures no more than one default*/
    AddressId INT,
    FOREIGN KEY (CustomerId) REFERENCES Customer(Id),
    FOREIGN KEY (AddressId) REFERENCES [Address](Id)
)

CREATE TABLE CustSecondaryAddress
(
    CustomerId INT REFERENCES CustDefaultAddress(CustomerId), 
                   /*No secondary address can be added unless default one exists*/
    AddressId INT REFERENCES [Address](Id),
    PRIMARY KEY(CustomerId, AddressId)
)

S'il existe une exigence supplémentaire selon laquelle une adresse ne doit pas être présente à la fois comme adresse principale et comme adresse secondaire, vous pouvez l'appliquer à l'aide d'une table d'aide et d'une vue indexée.

CREATE TABLE dbo.TwoRows
  (
     X INT PRIMARY KEY
  );

INSERT INTO dbo.TwoRows
VALUES      (1),
            (2)

GO

CREATE VIEW V
WITH SCHEMABINDING
AS
  SELECT D.AddressId,
         D.CustomerId
  FROM   dbo.CustDefaultAddress D
         JOIN dbo.CustSecondaryAddress S
           ON D.AddressId = S.AddressId
              AND D.CustomerId = S.CustomerId
         CROSS JOIN dbo.TwoRows

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON V(AddressId, CustomerId)

Answer 2

Au cas où les exigences ne m'auraient pas échappé, je pense qu'il est possible d'appliquer les mêmes conditions à l'aide d'un au lieu de déclencher .

Ce n'est pas aussi élégant qu'une solution de conception de tableau, et cela nécessitera un déclencheur un peu plus compliqué que ce que je préfère pour un déclencheur, mais cela passera tous vos tests actuels.

Voici ce qu'il fait en réalité :

• en cas d'insertion ou de mise à jour, il validera l'ensemble des données (anciennes et nouvelles paires) pour voir si chaque client a une et une seule (remarquez la somme des bits par défaut) valeur par défaut. S'il y a 0 ou plus de 1 défaut, il y aura une erreur.
• en cas de suppression, il ne validera que les adresses restantes par client pour qu'elles aient la même règle (uniquement par défaut, si une adresse existe).
• à la fin, s'il n'y a pas eu d'erreur, il fera la même opération que celle qu'il était censé faire ;

Le déclencheur qui fonctionnera avec votre table et vos données ressemblerait à ceci :

CREATE TRIGGER dbo.CustAddress1DefaultAddress
    ON  dbo.CustAddress
    Instead of INSERT, DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    declare @cnt int, @operation char(1);
    IF exists (select * from inserted)
    and not exists (select * from deleted) --only insert, no delete/update
        select @operation = 'I';
    else if exists (select * from inserted)
        and exists (select * from deleted) --update
        Select @operation = 'U';
    else
        Select @operation = 'D';
    print 'operation = ' + @operation;

    begin try
    if @operation in ('I', 'U')
    begin
        ;with defaultsPerCustAdd(SumDefault, CustomerId)
        as (
            select sum (x.[Default]), x.CustomerId
            from (
                select i.CustomerId, cast(i.[Default] as tinyint) as [Default]
                from inserted as i
                union all
                select ca.CustomerId, cast(ca.[Default] as tinyint) as [Default]
                from dbo.CustAddress as ca
                join inserted i on i.CustomerId = ca.CustomerId
                and i.AddressId != ca.AddressId
            ) as x
            group by x.CustomerId
        )
        select *
        from defaultsPerCustAdd as d
        where d.SumDefault = 0
        OR d.SumDefault > 1;
        set @cnt = @@ROWCOUNT;
    end
    else -- Delete
    begin
        ;with defaultsPerCustAdd(SumDefault, CustomerId)
        as (
            select sum (x.[Default]), x.CustomerId
            from (
                select ca.CustomerId, cast(ca.[Default] as tinyint) as [Default]
                from dbo.CustAddress as ca
                join deleted d on d.CustomerId = ca.CustomerId
                and d.AddressId != ca.AddressId
            ) as x
            group by x.CustomerId
        )
        select *
        from defaultsPerCustAdd as d
        where d.SumDefault = 0
        OR d.SumDefault > 1;
        set @cnt = @@ROWCOUNT;
    end;

    if @cnt > 0
        raiserror('error when validating one default address per customer', 16, 1)

    if @operation = 'I'
        insert dbo.CustAddress(CustomerId, AddressId, [Default])
        select i.CustomerId, i.AddressId, i.[Default]
        from inserted as i
    else if @operation = 'U'
        update ca
        set [default] = i.[default]
        from dbo.CustAddress as ca
        join inserted as i on i.AddressId = ca.AddressId and i.CustomerId = ca.CustomerId
    else if @operation = 'D'
        delete ca
        from dbo.CustAddress as ca
        join deleted as d on d.AddressId = ca.AddressId and d.CustomerId = ca.CustomerId

    end try
    begin catch
        print 'error when validating one default address per customer';
    end catch;
END
GO

Answer 3

Dash a suggéré qu'une contrainte de contrôle

quelque chose comme (select count(*) From table where customerid = @customerid et default = 1) = 1

pourrait être utilisé, c'est pourquoi j'ai créé cette réponse.

CREATE FUNCTION NumberOfCustomerDefaultAddresses
(
    @CustomerId INT
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM CustAddress
        WHERE CustomerId = @CustomerId
        AND [Default] = 1
    )
END
GO

ALTER TABLE CustAddress ADD CONSTRAINT CHK_DefaultAddress CHECK (dbo.NumberOfCustomerDefaultAddresses(CustomerId) = 1)

Cela permet d'éviter les insertions qui auraient pour conséquence de ne pas définir d'adresse par défaut. Mais il échoue pour détecter les mises à jour qui modifient l'indicateur par défaut et les suppressions qui suppriment l'enregistrement par défaut.