CODEWITHSUNDEEP
sqlsql-servert-sqlconstraints
e2fx
e2fx

Reputation: 29

SQL Constraints OR Condition

How can I have an SQL constraint with an OR condition:

USE [dbname]
GO

ALTER TABLE [dbo].[tablename]  WITH NOCHECK ADD  CONSTRAINT [CK_table] CHECK  (([field1] like ('[0-9a-zA-Z][0-9a-zA-Z]' OR '[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]') AND [field1] IS NOT NULL))
GO

ALTER TABLE [dbo].[tablename] CHECK CONSTRAINT [CK_table]
GO


field1 = varchar[10]

Goal is to check for size 2 or size 3 with allowed characters

Problem is 'OR' throws error of unknown

Upvotes: 2

Views: 158

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

The NULL check should be handled with a simple NOT NULL constraint. If you do include it, it should be phrased correctly and not just tacked onto the second condition:

ALTER TABLE [dbo].[tablename] WITH NOCHECK
    ADD CONSTRAINT [CK_table]
        CHECK  ( ([field1] like '[0-9a-zA-Z][0-9a-zA-Z]' OR
                  [field1] like '[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'
                 ) AND
                 field1 is not null
               );

The LIKE guarantees that the value is not NULL; an additional check is not necessary.

If you have chains of these, you can also write this as:

ALTER TABLE [dbo].[tablename] WITH NOCHECK
    ADD CONSTRAINT [CK_table]
        CHECK  ([field1] not like ('%[^0-9a-zA-Z][0-9a-zA-Z]%' AND
                LEN(field1) BETWEEN 2 AND 3 AND
                field1 is not null
               );

The first condition says that everything is alphanumeric (by using double negatives). The second specifies how long the column is. For two lengths, the two methods are about the same. If you allowed fields with 2-6 characters, this would be simpler.

Upvotes: 0

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Try this :

USE [dbname]
GO

ALTER TABLE [dbo].[tablename]  WITH NOCHECK ADD  CONSTRAINT [CK_table] CHECK  ([field1] like '[0-9a-zA-Z][0-9a-zA-Z]' OR [field1] like '[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]') AND [field1] IS NOT NULL
GO

ALTER TABLE [dbo].[tablename] CHECK CONSTRAINT [CK_table]
GO

You have problem here

([field1] like ('[0-9a-zA-Z][0-9a-zA-Z]' OR '[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]')

Means you wrote something like this (field1 like 'x' or 'y') which is not correct syntax. You need to write like (field1 like 'x' or field1 like 'y')

Upvotes: 2

Related Questions