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.

Answers


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.


Need Your Help

Python loop won't exit on specific user input

python loops

Sorry if this is a common question, but every solution I've looked up so far doesn't seem to work.

Cannot download images from apple website?

html ios iphone image

Generally one can download the images from any website by right clicking and selecting save image as.. but its not possible in apple website https://www.apple.com/iphone/compare/ , also they are us...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.