2

I have a table called Computer with columns:

id
NIC_name
is_primary

My primary key is (id, NIC_name) and is_primary is a bit field for a boolean value.

I would like to ensure that for all computers with same id there is always zero or one records whose is_primary is set to true. How do I accomplish this?

  • group by and count() might do the trick. – mustaccio Jul 27 '15 at 22:49
3

You can use a filtered index to enforce uniqueness on id and is_primary when is_primary is 1.

An example here:

create table Computer(
    id char(20) not null
  , NIC_name varchar(100) not null
  , is_primary bit not null
  , constraint pk_computer primary key (
    id asc,
    NIC_name asc
    )
);

create unique index ix_one_primary on Computer(id, is_primary) where (is_primary=1);

-- these three are fine
insert Computer(id, NIC_name, is_primary)
values('computer1','foo',0);

insert Computer(id, NIC_name, is_primary)
values('computer1','bar',1);

insert Computer(id, NIC_name, is_primary)
values('computer1','foobar',0);

-- this one is not allowed
insert Computer(id, NIC_name, is_primary)
values('computer1','oops',1);
  • Thank you, you did answer my question. Taking your idea I did this using SQL Serve Management Studio. I'll post what I did in another answer. – user3885927 Jul 28 '15 at 21:02
1

The answer from @Mister Magoo is great. I used SQL Server Management Studio to do the same task. I followed the instructions here from MSDN. Pasting below as well for convenience: To create a filtered index

  1. In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.
  2. Click the plus sign to expand the Tables folder.
  3. Click the plus sign to expand the table on which you want to create a filtered index.
  4. Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….
  5. In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.
  6. Under Index key columns, click Add….
  7. In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index. I selectedid and is_primary.
  8. Click OK.
  9. On the Filter page, under Filter Expression, enter SQL expression that you’ll use to create the filtered index. I used: is_primary=1
  10. Click OK.
  • 2
    You know you could just run - through SSMS since you prefer it - the statement provided by @MisterMagoo: create unique index ix_one_primary on Computer(id, is_primary) where (is_primary=1); – ypercubeᵀᴹ Jul 28 '15 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.