CODEWITHSUNDEEP
sqlsql-serverconstraints
Andrey
Andrey

Reputation: 21295

Setting column constraint on a table (SQL Server)

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!

Upvotes: 2

Views: 479

Answers (5)

Martin Smith
Martin Smith

Reputation: 453910

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))

Upvotes: 6

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

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.

Upvotes: 1

kbrimington
kbrimington

Reputation: 25692

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)))

Upvotes: 4

Randy
Randy

Reputation: 16673

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.

Upvotes: 2

GSerg
GSerg

Reputation: 78210

Create a column check:

CHECK (column_name IN (2, 4, 8, 16, 32, ..., 2147483648))

Upvotes: 0

Related Questions