I have a column that should contain one of values of 2 power n: 2,4,8,16,32 etc. I want to enforce that on table schema level - is there a way to specify such a column constraint?
Thanks!
2
-
Can you give a reason just why you are doing this ?JonH– JonH2010-08-11 19:01:26 +00:00Commented Aug 11, 2010 at 19:01
-
Ok, it just occurred to me that I should rather store n than 2 power n :) Case solved!Andrey– Andrey2010-08-11 19:11:54 +00:00Commented Aug 11, 2010 at 19:11
Add a comment
|
5 Answers
Shamelessly stealing from this answer you could use bitwise operations to do this pretty efficiently.
ALTER TABLE tablename ADD CONSTRAINT
ckname CHECK (colName > 0 AND (colName & (colName - 1) =0))
Comments
In SQL Server:
ALTER TABLE [dbo].[PowerOfTwo]
WITH CHECK ADD CONSTRAINT [CK_PowerOfTwo]
CHECK ((log([Value])/log(2)=round(log([Value])/log(2), 0, 1)))
Comments
how about defining the column to be N. then all uses of that column would be 2^n by definition instead of constraint.
otherwise - you could put trigger logic in place to validate each value as it is entered or updated.
1 Comment
Andrey
I already figured that out a couple of minutes ago :) I'll upvote you anyway as it's a good idea
Assume your column name is N. Try something like
CHECK(LOG(N)/LOG(2) = TRUNC(LOG(N)/LOG(2)))
The intent is to verify that the binary logarithm of the value N is an integer, which would mean that N was a power of 2. Not sure if SQL Server supports the LOG and TRUNC functions - substitute in the correct names as needed.
Edit: as I re-read this I realized that rounding might cause a problem (I forgot the Second Commandment of Floating Point, which is: Thou Shalt Never Compare Floating Point Values For Equality!). OK, how about
CHECK(ABS(LOG(N)/LOG(2) - TRUNC(LOG(N)/LOG(2))) < 0.00001)
or substitute whatever error tolerance you'd like for the 0.00001.
Share and enjoy.
answered Aug 11, 2010 at 19:06
Bob Jarvis - Слава Україні
50.2k10 gold badges81 silver badges119 bronze badges
Comments
Create a column check:
CHECK (column_name IN (2, 4, 8, 16, 32, ..., 2147483648))
2 Comments
GSerg
Reasons for downvoting anyone?
GSerg
Yes I am serious. How is this answer conceptually different from the accepted answer? Yes, I thought it'd be better to use a simple list of values, since there are few, rather than call
log 4 times, but other than that (irrelevant) difference, there's nothing else. Or are you implying your column is actually of type double, not int ? In this case the accepted answer is wrong, too, because there's a finite floating-point precision because of which the comparison will likely be less true than you'd expect.