SQL Server / TSQL Updating a table with a unique constraint

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...

Answers


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.


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.


Need Your Help

Find and Replace using function to manipulate matches first

php regex sed replace

I need to search for certain lines in a file that contain numbers, then do some manipulation on those numbers before replacing them in the file. For example, say I've got a line like this:

Generate many combinations of numbers based on

c# .net math numbers combinations

I was just wondering if there's a way to generate many different combinations of numbers and letters, based off of just one number/letter? For example: