Constraint on a column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Constraint on a column

One of our developers is asking me to create a table with two columns: ID (Int), and IsActive (Bit). He wants IsActive to be null for all rows in the table and only one row is set (means IsActive = 1 for that row). He is asking for a constraint on the table that it cannot set IsActive to 1 if there is another raw set. He wants to be sure that at a given time only one row is set. I am thinking of a Trigger. Is there any other solution?
CanadaDBA
How about just storing "active" ID somewhere instead of having table with all ids marked inactive except one? These IDs come from other table identity column, right?
Actually, his design is like this:
ID WaiverCode IsActive
01 01 Null
02 02 Null
03 08 1
04 10 Null CanadaDBA
If you don’t expect much more rows in that table and infrequent changes, you can apply trigger. For that purpose I usually use appParameters table. Row for this example would be something like: appParameters.ParameterName = ‘WaiverCode’, appParameters.ParameterValue = ’08’ appParamateres.ParameterType = ‘char(2)’,
appParameters.Description = … or appParameters.ParameterName = ‘WaiverID’, appParameters.ParameterValue = 3, appParameters.ParameterType = ‘tinyint’
appParameters? I couldn’t find it in BOL. What’s that? CanadaDBA
This is table we created to store settings like one you mentioned. Equivalent to ini file. To be clear, this is our creation, not system table/view.
So, you have a hard coded table in your application. Actually, I was thinking that ask the developers to put an UPDATE with Null before every UPDATE for "set" in the table. For example: UPDATE tblTest SET IsActive = Null
UPDATE tblTest SET IsActive = 1 WHERE WaiverCode = ’04’
CanadaDBA
Assuming you will never have more then a few rows in the table, I think you can do it that way without performance impact. The only problem is what if developpers don’t follow your recommendation. We allow developpers to access db only using sps. In our case it would be simply matter of implementing this code in tblTestUpdate stored procedure.
]]>