title: SQL Check Constraint on Table Columns
body: <p>I have the following check constraint to add to an existing table. </p>
  
  <pre><code> ALTER TABLE  [dbo].[PROCESS_UPLOADDATA] 
  ADD CONSTRAINT [PROCESS_RequireKeyFileOnMODE_Constraint] 
  CHECK (
  (RTRIM(LTRIM(LOWER(UPLOAD_MODE))) ='sftp' 
  AND DATALENGTH(KEYFILE_PATH) &gt; 2) 
  )
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>The ALTER TABLE statement conflicted with the CHECK constraint "PROCESS_RequireKeyFileOnMODE_Constraint". The conflict occurred in database "FILECONTROL", table "dbo.PROCESS_UPLOADDATA".</strong></p>
  
  <p>When I execute however, I get the following error message. Would appreciate any help with this please. SQL SERVER database ! Thanks in advance. </p>
  
  <pre><code>//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) &gt; 2) 
                         THEN 1 
                         WHEN   (RTRIM(LTRIM(LOWER(UPLOAD_MODE))) != 'sftp' )
                          AND (DATALENGTH(KEYFILE_PATH) &lt; 2) 
                         THEN 1
                         WHEN   (RTRIM(LTRIM(LOWER(UPLOAD_MODE))) != 'sftp' )
                          AND ( KEYFILE_PATH IS NULL) 
                         THEN 1
                         ELSE   0
  
                         END =0
      )
  </code></pre>
  
  <p>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. </p>