sql server - SQL Check Constraint on Table Columns -

i have following check constraint add existing table.

 alter table  [dbo].[process_uploaddata]  add constraint [process_requirekeyfileonmode_constraint]  check ( (rtrim(ltrim(lower(upload_mode))) ='sftp'  , datalength(keyfile_path) > 2)  ) 

the upload_mode column column not allow nulls , has values such ftp or sftp. keyfile_path column nvarchar(400) column null default setting. however, in case value in upload_mode set 'sftp', want make sure keyfile path provided.

the alter table statement conflicted check constraint "process_requirekeyfileonmode_constraint". conflict occurred in database "filecontrol", table "dbo.process_uploaddata".

when execute however, following error message. appreciate please. sql server database ! in advance.

//updated sql check         alter table  [dbo].[process_uploaddata]      add constraint [process_requirekeyfileonmode_constraint]      check (     case                       when (rtrim(ltrim(lower(upload_mode))) ='sftp' )                         , (datalength(keyfile_path) > 2)                         1                         when   (rtrim(ltrim(lower(upload_mode))) != 'sftp' )                         , (datalength(keyfile_path) < 2)                         1                        when   (rtrim(ltrim(lower(upload_mode))) != 'sftp' )                         , ( keyfile_path null)                         1                        else   0                         end =0     ) 

after considering andomar's suggestion/explaination, able put above , wanted sure if logic correct need here please. again in advance.

this check requires entries sftp:

rtrim(ltrim(lower(upload_mode))) ='sftp' , datalength(keyfile_path) > 2 

you mean:

rtrim(ltrim(lower(upload_mode))) = 'sftp' , datalength(keyfile_path) > 2 or rtrim(ltrim(lower(isnull(upload_mode,''))) <> 'sftp' 

that allow sftp keyfile_path, , other upload mode (including null) either or without keyfile_path.