Ensure only one of the several records for a given item has a column with true value 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?
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);