Results 1 to 2 of 2

Thread: SQL Server constraint on multiple columns

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2001
    Posts
    1,374

    SQL Server constraint on multiple columns

    I would like to enforce a constraint on an SQL Server table as follows:

    Table definition

    ComputerID autoinc
    ComputerName varchar(255)
    Username varchar(255)
    IsDefault bit

    ComputerName may have multiple records with the same value but only one of them should have IsDefault set to 1. The constraint should not allow the same ComputerName to have multiple records with IsDefault = 1.

    How can this be achieved?

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,477

    Re: SQL Server constraint on multiple columns

    You can create unique indexes with a filter, which is most commonly used to allow multiple NULLs in a column but require unique values otherwise. The filter is a WHERE clause, the same as you would use in a query. You can't add a filter via the GUI in SSMS so you have to do it using a SQL script. You can create a unique index using the GUI and then generate a script from it that you can edit to add the filter.
    Code:
    CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1]
    (
    	[ComputerName] ASC
    )
    WHERE ([IsDefault]=(1))
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width