Column Constraint in SQL

Question

Possible Duplicate:
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.

score 0 · Answer 1 · answered Jun 03 '11 at 14:28

0

You could use a CHECK constraint.

answered Jun 03 '11 at 14:28

Oded

489,969
99
883
1,009

1

From your link, "Most database management systems restrict check constraints to a single row" - how would you propose that a CHECK constraint be able to determine whether it's column value being 1 is valid without consulting other rows? In e.g. SQL Server, there are kludges using UDFs, but they're not 100% there, usually. – Damien_The_Unbeliever Jun 03 '11 at 14:33
@Damien_The_Unbeliever - I suggest you read further: "Such constraints are not truly table check constraints but rather row check constraints.". And they exist in SQL Server properly, not as UDF kludges: http://msdn.microsoft.com/en-us/library/ms188258.aspx – Oded Jun 03 '11 at 14:36
but the necessary constraint in this case needs to assert the truth or otherwise about the state of the table as a whole - that exactly one row has ColumnX = 1. That is not possible with a CHECK constraint (at least in SQL Server). And the solutions to this problem are well known, as linked to dups by myself and Joe - you use an indexed view or a filtered index. – Damien_The_Unbeliever Jun 03 '11 at 14:44

score 0 · Answer 2 · answered Jun 03 '11 at 14:44

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

Column Constraint in SQL

2 Answers2