2

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!

Andrey
  • 20,487
  • 26
  • 108
  • 176

5 Answers5

5

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))
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
4

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
  • 25,142
  • 5
  • 62
  • 74
2

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
  • 16,480
  • 1
  • 37
  • 55
  • I already figured that out a couple of minutes ago :) I'll upvote you anyway as it's a good idea – Andrey Aug 11 '10 at 19:15
1

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 - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
0

Create a column check:

CHECK (column_name IN (2, 4, 8, 16, 32, ..., 2147483648))
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Reasons for downvoting anyone? – GSerg Aug 11 '10 at 19:12
  • 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 Aug 11 '10 at 19:24