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!
+1
A:
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.
Bob Jarvis
2010-08-11 19:06:18
Good one, thanks!
Andrey
2010-08-11 19:16:17
A:
Create a column check:
CHECK (column_name IN (2, 4, 8, 16, 32, ..., 2147483648))
GSerg
2010-08-11 19:07:05
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.
GSerg
2010-08-11 19:24:58
+2
A:
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.
Randy
2010-08-11 19:11:36
I already figured that out a couple of minutes ago :) I'll upvote you anyway as it's a good idea
Andrey
2010-08-11 19:15:18
+3
A:
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)))
kbrimington
2010-08-11 19:12:33
+1
A:
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))
Martin Smith
2010-08-11 19:20:30