Post reply

Create Check Constraint

  • September 3, 2012 at 12:17 pm

    #263804

    In the following table, there should only be one instance where isPrimaryFacility = True for each instance of a unique ProjectNumber, however there can be multiple instances where isPrimaryFacility = False for each unique ProjectNumber. So how would I go about creating a check constraint to ensure that there is only one Primary Facility, but allow multiple secondary facilities?

    CREATE TABLE [dbo].[ProjectFacilities](

    [Id] [int] IDENTITY(1000,1) NOT NULL,

    [ProjectNumber] [varchar](8) NOT NULL,

    [FacilityId] [int] NOT NULL,

    [EntryUserId] [varchar](25) NULL,

    [EntryDateTime] [datetime] NULL,

    [ModifiedUserId] [varchar](25) NULL,

    [ModifiedDateTime] [datetime] NULL,

    [IsPrimaryFacility] [bit] NOT NULL,

    CONSTRAINT [PK_ProjectFacilities] PRIMARY KEY CLUSTERED

    (

    [Id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[ProjectFacilities] ADD CONSTRAINT [DF_ProjectFacilities_IsPrimaryFacility] DEFAULT ((1)) FOR [IsPrimaryFacility]

    GO

    INSERT INTO [ProjectFacilities] (ProjectNumber, FacilityId, IsPrimaryFacility) VALUES '0001', '1001', '1') - Valid (No other record exists with isPrimaryFacility = True)

    INSERT INTO [ProjectFacilities] (ProjectNumber, FacilityId, IsPrimaryFacility) VALUES '0001', '1002', '0') - Valid (isPrimaryFacility = False)

    INSERT INTO [ProjectFacilities] (ProjectNumber, FacilityId, IsPrimaryFacility) VALUES '0001', '1003', '0') - Valid (isPrimaryFacility = False)

    INSERT INTO [ProjectFacilities] (ProjectNumber, FacilityId, IsPrimaryFacility) VALUES '0001', '1004', '0') - Valid (isPrimaryFacility = False)

    INSERT INTO [ProjectFacilities] (ProjectNumber, FacilityId, IsPrimaryFacility) VALUES '0001', '1004', '1') - Invalid (A record already exists with isPrimaryFacility = True)

  • September 3, 2012 at 12:36 pm

    #1533049

    Use a filtered index

    CREATE UNIQUE INDEX IX ON [dbo].[ProjectFacilities] (ProjectNumber) WHERE [IsPrimaryFacility]=1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply