Column Constraint in SQL [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Dup开发者_C百科licate:

SQL Server bit column constraint, 1 row = 1, all others 0

Hi all,

Say I have a table called TableA containing a BIT field. Is there any way I can enforce only one row in a table to be ever set at 1?

Edit: No triggers, please!

Thank you very much.

---

You could use a CHECK constraint.

---

CREATE FUNCTION [dbo].[CheckTestTableFlag]()
RETURNS int
as
BEGIN
    DECLARE @retval int
    SELECT @retval = COUNT(*) FROM Test WHERE Flag = 1;
    RETURN @retval;
END;
GO

ALTER TABLE [dbo].[Test]  WITH CHECK ADD CONSTRAINT [chkFlag] CHECK  ([dbo].[CheckTestTableFlag]()<= 1 )

GO