Avoid Blank Values in a column

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  March 23, 2016 at 11:26 am

  #1867180

  Stephanie Giovannini (3/23/2016)

  ---

  David McKinney (3/23/2016)

  ---

  A slight variation (although I've not got SQL installed to test it.)

  CONSTRAINT ck_xxx CHECK ('' not in (c1,c2,c3,c4,c5))

  Would you kindly check and let me know if that works?

  Very clever, I forgot about that trick.

  For the constraint CHECK (c1 <> '' AND c2 <> '' AND c3 <> '' AND c4 <> '' AND c5 <> ''), the script generated is:

  ALTER TABLE [dbo].[test] WITH CHECK ADD CHECK (([c1]<>'' AND [c2]<>'' AND [c3]<>'' AND [c4]<>'' AND [c5]<>''))

  Yeah - that's an annoying feature of SSMS which is why I always check the DDL that I write into Source Control; I can retrieve it as I wrote it.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001