SQL Server / TSQL Updating a table with a unique constraint

Question

Hi,

If I have two tables, say:

Clients
(
  ClientID int primary key,
  ClientName varchar(50) not null
)

and

Addresses
(
  AddressID int primary key,
  AddressLine1 varchar(50),
  etc..
  ClientID int not null,
  IsPrimaryAddress bit not null
)

with a unique constraint across (ClientID, IsPrimaryAddress), how can up date those tables from a collection in code (not a single sql statement) without violating the constraint?

Thanks...

Answer 1

It's probably a mistake to have a unique constraint across (ClientID, IsPrimaryAddress), unless you want to ensure that a client can have no more than two addresses (one primary, one other), as any time you try to add further non-primary addresses you will be violating the integrity constraint.

Answer 2

As others have mentioned already, the problem here seems to stem from having an overly restrictive unique constraint. Presumably this causes you difficulty when you have two addresses for a client and want to swap over which is the primary. In this case, you first update the primary address to be non-primary and immediately fail because two non-primary addresses for the same client violates the constraint.

The problem, therefore, would be fixed by only enforcing the unique constraint on ClientID when the address is primary. Such conditional unique constraints have been discussed on stack overflow before.

If you were using Oracle, then you can be clever and do something like this:

CREATE UNIQUE INDEX unique_primary_addr ON Addresses (
  DECODE (IsPrimaryAddress, 1, ClientId, NULL));

But I'm assuming that you are using sql-server or something else, so you'll be forced to do something like this:

CREATE FUNCTION PrimaryAddressCount(@Id INT) RETURNS INT AS
BEGIN
  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM Addresses WHERE ClientId = @Id AND IsPrimaryAddress = 1;
  RETURN @ret;
END;
GO

ALTER TABLE Addresses
  ADD CONSTRAINT SinglePrimaryConstraint
CHECK (IsPrimaryAddress != 1 OR dbo.PrimaryAddressCount(ClientId) = 1);

Either way, the resulting constraint will allow as many non-primary addresses as you like for each client, but will enforce a single primary address. This should enable you to update addresses easily, as long as you always write the new primary address last.