Creating SQL Server 2008 Constraint

Question

I have a table with two integer columns and a bit column. How can I put a constraint on the table so that it will not allow duplicates with the combination of two integers with a true. For example:

1 2 True
1 2 False
1 2 True ------> This should not be allowed

Answer 1

create unique index idxName on (Field1, Field2) where Condition = 'True';

Answer 2

ALTER TABLE Table_1 ADD CONSTRAINT col1_col2_COL3
   UNIQUE (col1, col2, col3);

Answer 3

This might be heavier than you like but I believe it gives what you want. Create a view on the rows where the bit column is TRUE then materialize it with a unique index. The below example works on SQL Server 2008.

--Set statements required for creating materialized views.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON 
GO

--Set statements required when creating index for materialized views.
SET ANSI_PADDING ON 
GO
SET ANSI_WARNINGS ON 
GO
SET ARITHABORT ON   --only required in 80 compatibility mode.
GO
SET CONCAT_NULL_YIELDS_NULL ON 
GO
SET NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE dbo.Test (a int not null, b int not null, c bit not null);
GO

CREATE VIEW dbo.vTest (a,b) WITH SCHEMABINDING AS 
SELECT a,b FROM dbo.Test WHERE c = 'TRUE' ;
GO

CREATE UNIQUE CLUSTERED INDEX [AK_vTest] ON dbo.vTest ( a,b );
GO

INSERT dbo.Test (a,b,c) VALUES (1,2,'TRUE');  --succeeds.
INSERT dbo.Test (a,b,c) VALUES (1,2,'FALSE'); --succeeds.
INSERT dbo.Test (a,b,c) VALUES (1,2,'FALSE'); --succeeds.
INSERT dbo.Test (a,b,c) VALUES (1,2,'TRUE');  --fails "Msg 2601, Level 14"
GO

SELECT * FROM dbo.Test
SELECT * FROM dbo.vTest

DROP VIEW  dbo.vTest
DROP TABLE dbo.Test

Answer 4

for doing this in SSMS, expand the table name and then press write click of your mouse on it. click on new index in exposed menu. in New Index window, and in general tab you can add your arbitrary columns. in filter tab, add your filter expression.