CODEWITHSUNDEEP
sqlsql-server
Kobojunkie
Kobojunkie

Reputation: 6545

SQL Check Constraint on Table Columns

I have the following check constraint to add to an existing table. 

 ALTER TABLE  [dbo].[PROCESS_UPLOADDATA] 
ADD CONSTRAINT [PROCESS_RequireKeyFileOnMODE_Constraint] 
CHECK (
(RTRIM(LTRIM(LOWER(UPLOAD_MODE))) ='sftp' 
AND DATALENGTH(KEYFILE_PATH) > 2) 
)

The UPLOAD_MODE column is a column that does not allow NULLS and has values such as ftp or sftp. The KEYFILE_PATH column is an NVARCHAR(400) column with IS NULL default setting. However, in the case that the value in the UPLOAD_MODE is set to 'sftp', what I want is to make sure that a keyfile path is also provided.

The ALTER TABLE statement conflicted with the CHECK constraint "PROCESS_RequireKeyFileOnMODE_Constraint". The conflict occurred in database "FILECONTROL", table "dbo.PROCESS_UPLOADDATA".

When I execute however, I get the following error message. Would appreciate any help with this please. SQL SERVER database ! Thanks in advance. 

//UPDATED SQL CHECK 


     ALTER TABLE  [dbo].[PROCESS_UPLOADDATA] 
    ADD CONSTRAINT [PROCESS_RequireKeyFileOnMODE_Constraint] 
    CHECK (
    CASE 
                     WHEN (RTRIM(LTRIM(LOWER(UPLOAD_MODE))) ='sftp' )
                        AND (DATALENGTH(KEYFILE_PATH) > 2) 
                       THEN 1 
                       WHEN   (RTRIM(LTRIM(LOWER(UPLOAD_MODE))) != 'sftp' )
                        AND (DATALENGTH(KEYFILE_PATH) < 2) 
                       THEN 1
                       WHEN   (RTRIM(LTRIM(LOWER(UPLOAD_MODE))) != 'sftp' )
                        AND ( KEYFILE_PATH IS NULL) 
                       THEN 1
                       ELSE   0

                       END =0
    )

After considering Andomar's suggestion/explaination, I was able to put together the above and wanted to be sure if the logic is correct for what I need here please. Again Thanks in advance.

Upvotes: 0

Views: 274

Answers (1)

Andomar
Andomar

Reputation: 238086

This check requires that all entries are sftp:

RTRIM(LTRIM(LOWER(UPLOAD_MODE))) ='sftp' AND DATALENGTH(KEYFILE_PATH) > 2

You probably mean:

RTRIM(LTRIM(LOWER(UPLOAD_MODE))) = 'sftp' AND DATALENGTH(KEYFILE_PATH) > 2
OR
RTRIM(LTRIM(LOWER(ISNULL(UPLOAD_MODE,''))) <> 'sftp'

That would allow sftp with a keyfile_path, and any other upload mode (including null) either with or without a keyfile_path.

Upvotes: 3

Related Questions