Post reply

Avoid Blank Values in a column

  • March 11, 2016 at 10:42 am

    #308936

    Hi All,

    I need to avoid/deny blank values/empty string ('') entered into few columns in a table. This column already does not allow NULL values and there is a default constraint set.

    I would like to apply same check constraint that applies to 5 columns in the same table. Can I use just one check constraint?

    What is the best way to accomplish this.

    Many thanks!

  • March 11, 2016 at 11:01 am

    #1864641

    You'll need to add a CHECK Constraint. The code will look like:

    ALTER TABLE <yourtable>

    ADD CONSTRAINT ck_constraintname

    CHECK (column <> '')

    "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

  • March 11, 2016 at 11:02 am

    #1864642

    In the database layer one solution is a trigger that checks the value and rolls back the transaction.

    You could validate date in the application layer (and probably should, honestly), but that won't stop someone from doing a manual something.

    Probably the right/best solution is a CHECK CONSTRAINT. See BOL.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 11, 2016 at 1:40 pm

    #1864683

    Thanks Alan.

  • March 11, 2016 at 1:40 pm

    #1864684

    Thanks Kevin.

  • March 11, 2016 at 5:17 pm

    #1864703

    I would like to apply same check constraint to 5 columns in the same table. Can I use just one check constraint? How do I write a script for this?

  • March 11, 2016 at 7:42 pm

    #1864706

    You need one check constraint for each column.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 11, 2016 at 9:07 pm

    #1864711

    IF OBJECT_ID('tempdb..#yourtable') IS NOT NULL DROP TABLE #yourtable;

    CREATE TABLE #yourtable

    (

    c1 varchar(10) NOT NULL,

    c2 varchar(10) NOT NULL,

    c3 varchar(10) NOT NULL,

    c4 varchar(10) NOT NULL,

    c5 varchar(10) NOT NULL,

    CONSTRAINT ck_xxx CHECK (c1 <> '' AND c2 <> '' AND c3 <> '' AND c4 <> '' AND c5 <> '')

    );

    -- This will succeed

    INSERT #yourtable VALUES ('a','b','c','d','e');

    -- this will fail

    INSERT #yourtable VALUES ('a','b','c','','e');

    "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

  • March 11, 2016 at 9:12 pm

    #1864712

    Alan, I stand corrected. I've never seen it done this way. I learned something new, so thank you.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 12, 2016 at 4:15 am

    #1864721

    Thanks a lot Alan! Will the update also fails if an empty string is passed?

  • March 12, 2016 at 4:31 am

    #1864723

    SQL!$@w$0ME (3/12/2016)

    Thanks a lot Alan! Will the update also fails if an empty string is passed?

    suggest you try it 🙂

    UPDATE #yourtable

    SET c4 = ''

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • March 12, 2016 at 6:55 am

    #1864730

    J Livingston SQL (3/12/2016)

    SQL!$@w$0ME (3/12/2016)

    Thanks a lot Alan! Will the update also fails if an empty string is passed?

    suggest you try it 🙂

    UPDATE #yourtable

    SET c4 = ''

    OP, I'd suggest you always try what you write. It's called testing.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 21, 2016 at 9:01 am

    #1866494

    Ed Wagner (3/11/2016)

    Alan, I stand corrected. I've never seen it done this way. I learned something new, so thank you.

    Sorry for the late reply Ed (I was away for 8 days). Thanks! I learn plenty from you all the time sir so it's nice to pay you back 😛

    "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

  • March 23, 2016 at 10:48 am

    #1867170

    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?

  • March 23, 2016 at 10:59 am

    #1867173

    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?

    Yes, that works. However, when you script the check constraint back in SSMS (Script Table as CREATE To), the constraint looks like this:

    ALTER TABLE [dbo].[test] WITH CHECK ADD CHECK ((NOT (''=[c5] OR ''=[c4] OR ''=[c3] OR ''=[c2] OR ''=[c1])))

    (I didn't name the test constraint)

    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]<>''))

    Either way, the logic is the same.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply