SQL Server – Check Constraints on Decimals in SQL Server 2012

check-constraintssql server

I have the following check constraint on an NVARCHAR(255) column:

([weekReviewWk10]='A' OR [weekReviewWk10]>=(0) AND [weekReviewWk10]<=(25))

However, when someone enters, say, 23.5, SQL Server rejects the input. How can I fix this? I know it is because it is testing things as ints, but how do I get it to test the entry as a decimal?

The column has to be NVARCHAR since, as you can see, I also need my users to be able to enter 'A'.

Best Answer

You could use TRY_CAST to cast the column to a decimal format before comparing.

weekReviewWk10='A' OR
TRY_CAST(weekReviewWk10 AS decimal(3, 1)) BETWEEN 0.0 AND 25.0

If the cast fails, the result of TRY_CAST will be NULL and the comparison is false, but it won't crash. TRY_CAST requires SQL Server 2012 or newer.

It's worth mentioning that you're potentially opening up a pit of bad things when you allow text values in a numeric column, but judging from the question, you already know that. :)

Related Solutions

Sql-server – Altering nullability of column in SQL Server

The relevant documentation is here

ALTER COLUMN Specifies that the named column is to be changed or altered.

The modified column cannot be any one of the following:

Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

In practice it seems that SQL Server does allow some additional cases beyond that mentioned in the documentation however.

As you show in your question ALTER TABLE test ALTER COLUMN field1 int null does in fact work so the restrictions on changes to columns used in indexes appear to be the same as those for user created statistics.

Moreover the caveat mentioned about Primary Keys seems to be untrue also. The following works fine.

CREATE TABLE test2(pk varchar(10) primary key);

ALTER TABLE test2 ALTER COLUMN pk varchar(100) NOT NULL

Sql-server – Add Check Constraint On Varchar Column to enforce no duplicates

Ordinarily, you'd just create a unique constraint on that column. But you can't put a unique constraint on a varchar(max) column. That could be close to 2GB per index entry.

In SQL Server, the maximum size of an index key is 900 bytes. I believe SQL Server enforces unique constraints by using a unique index, so I'd expect that limitation to apply to your problem, too.

In any case, I think this is an XY problem.

Best Answer

Related Solutions

Sql-server – Altering nullability of column in SQL Server

Sql-server – Add Check Constraint On Varchar Column to enforce no duplicates

Related Question