Re: One Record Where True, Many Where False

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 12:20:55 -0400

You can enforce this using an indexed view... something like this:

CREATE VIEW PrimaryClientLocations
AS
    SELECT PrimaryContactLocation, ContactID, COUNT_BIG(*) AS PrimaryCount
    FROM tblClientLocations
    WHERE PrimaryContactLocation = 1
    GROUP BY PrimaryContactLocation, ContactID
    HAVING COUNT_BIG(*) = 1
GO

CREATE CLUSTERED INDEX IX_ClientLocation ON
PrimaryClientLocations(ContactID)
GO

"Jeremy Wallace" <altacct@yahoo.com> wrote in message
news:788714cf.0407280814.64bb28a3@posting.google.com...
> I have a table where I want to mark one record per contact as primary,
> but allow multiple records per client to be not primary.
>
> tblClientLocations has a bunch of fields including ContactID (Int) and
> PCL (bit) [Primary Contact Location], as well as a bunch of address
> fields.
>
> I can't use a unique contstraint on the two fields, because that would
> allow only one true and one false per contact.
>
> How should I go about this?
>
> Jeremy
> ========
> Jeremy Wallace
> Fund for the City of New York
> http://www.fcny.org
> FirstInitialLastName @ fcny.org