Sql-server – How to add constraint on a varchar column to have at least one non numeric character

constraintdatabase-designddlsql servertable

I am trying to implement some constraints on tables so that there is no tampering of the data from what is expected.

One of the constraints is on a varchar column. It should at least contain one non-numeric character.

How can I implement this constraint in SQL Server, and if I implement it, What will be the effect on the performance if any?

Best Answer

A check constraint with a LIKE expression could be used to ensure at least one non-numeric character exists:

CHECK (YourColumn LIKE '%[^0-9]%')

The 0-9 range there might not be exactly right for you, depending on the collation of the varchar column. For example, using the common Latin1_General_CI_AS collation, the following additional characters would be considered numeric by the 0-9 test:

²
³
¹
¼
½
¾

If that is a concern, expand the range explicitly:

CHECK (YourColumn LIKE '%[^0123456789]%')

I would expect negligible performance impact in either case.

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

The following should do the trick:

CREATE TABLE MyTable (col1 FLOAT NULL, col2 NVARCHAR(30) NULL, col3 DATETIME NULL);
GO

ALTER TABLE MyTable
ADD CONSTRAINT CheckOnlyOneColumnIsNull
CHECK 
(
    ( CASE WHEN col1 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col2 IS NULL THEN 0 ELSE 1 END
    + CASE WHEN col3 IS NULL THEN 0 ELSE 1 END
    ) = 1
)
GO

Sql-server – How to add a constraint after I’ve truncated a table

Use WITH NOCHECK instead of WITH CHECK to add a constraint that conflicts with existing data. https://stackoverflow.com/questions/529941/whats-the-difference-between-with-check-add-constraint-followed-by-check-constr

Perhaps you are getting the error because you "2. Trucate a table." and not all of them? Maybe you are removing the Authors or the Books and are leaving the records in AuthorBooks that references these other tables.

Best Answer

Related Solutions

Sql-server – Check constraint only one of three columns is non-null

Sql-server – How to add a constraint after I’ve truncated a table

Related Question