How to Ensure Only 1 Bit Valued As 1 in Table
-
I am trying to use a bit datatype field for an indicator. This indicator can only be true (1) for one record in my table. Any thoughts on how I can validate this? Constraints?
I know that an alternative would be to make this a char(1) and place a unique index on the column, but I'd like to know if there are any other ideas.
MANY THANKS!!!
-
I would use a trigger, which sets all any original '1's to '0'. That way, you only have to worry about setting the new one to 1, and the old one will be turned off for you.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
INSERT, UPDATE trigger can be used to check the existing values and enforce the rule. If you are updating/Inserting multiple rows use correlated query or cursor. Logic will some thing like
IF EXISTS (SELECT * FROM Table WHERE BitCol = 1) AND ((SELECT BitCol FROM INSERTED) = 1)
ROLLBACK TRAN
Making a CHAR(1) will allow multiple values. Still you have to use trigger to enforce the rule.
Regards,
gova -
Here is a completely different solution:
CREATE TABLE dbo.INDICATORTABLE(
othertablepk INT NOT NULL PRIMARY KEY NONCLUSTERED
, uniqifier BIT NOT NULL DEFAULT 1
, CONSTRAINT INDICATORTABLE_fk_OTHERTABLE FOREIGN KEY (othertablepk) REFERENCES dbo.OTHERTABLE (othertablepk)
, CONSTRAINT INDICATORTABLE_uq UNIQUE (uniqifier)
, CONSTRAINT INDICATORTABLE_ck CHECK (uniqifier = CAST(1 AS BIT))
)
-
Lets see if I understand your method Chris.
dbo.INDICATORTABLE contains a single record with the uniqifier field set to 1.
All you do is set the value of othertablepk to the primary key value of the table for which you are trying to set the single indicator?
-
Yes, that was the idea. The uniqifier column could of course just as well be set to 0 always, or be a tinyint that is always set to 42 (you get the picture
). The only thing important is that it makes sure that the table always contains just a single row, which as you say should point to the row in othertable which we want to indicate. -
I am not sure whether this is the best way or not. It was just an alternative that popped up in my head when I read the question. The nice part about it is that it avoids a lot of redundant NULLs or 0's in othertable. And it is a completely declarative constraint so we don't need any triggers and/or cursors etc.
-
Chiris I don't get the solution.
This would not allow more than one 0 in the uniqifier field. There can be one 0 and one 1 and rest will be nulls. Or is that intended in the answer.
Regards,
gova -
No, actually it would allow one row with the value 1 in the uniqifier column, and nothing else. No more rows, and nothing else than a 1 in uniqifier.
The original question asked for "[an] indicator [that] can only be true (1) for one record [sic] in my table". So what is asked for here is some way to point out the fact that one row (not record) in the table is 'special' in some way. Using a bit column on every row with the value 1 being allowed on only one row, the rest having zeroes or null, is wasteful as I see it. And worse yet is that it requires a non-declarative constraint (by implementing a trigger), since SQL Server does not allow multi-row constraints.
My solution instead uses a declarative way to make sure that one row, and only one, is 'indicated as special'.
-
Good Idea. Cool.
Regards,
gova -
Wow!!! I have a bit to chew on (sorry for the pun) with these great responses.
The usage of this is for a website that will be using the information to dynamically generate option buttons for each record in the table, determine what their position/order will be and assign one button as a default.
I do like the simple elegance of Chris Hedgate's declarative method and also considering the use the Insert/Update Trigger approach of changing all values to zero when either a new row is valued as 1 or when a current row's value is updated to 1.
I do need to consider the possibility of deleting a row that is valued as 1 as I still require one and only one row to have a value of 1 for the bit field.
Would anyone care to comment on how to handle the deletion scenario (Still need a row to have the bit field valued as 1) in both Chris' method and the Trigger method?
-
Well, you could put a foreign key on Chris' table, so that the system won't let you delete that row out of the original table until you've deleted it.
If you decide to go for a trigger (which I suggested assuming that your database design must've been given to you, not up to you to change), I would simply rollback the delete if it's the flagged record.
Otherwise, you could use the trigger to set the flagged record to be the one that meets some other criteria... like the smallest id field or something.
RobF
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Like Rob says, if you always require one row to be indicated, then you would need to have some method of selecting which new row should be indicated when the old one is deleted. Just update the foreign key in my indicator-table to reflect this and then delete the old row from the other table.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply