none
Change the Column from NULL to NOT NULL

    Question

  • Hi all,

     

    One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.

     

    I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.

     

    Is there any way to achieve this...

     

    Thanks...

    Friday, February 29, 2008 4:56 AM

Answers

  • If it's not possible to update NULL values that the column already contains, your only option is to add a constraint to the table to prevent new NULL values from being added to that column.

     

    For example, suppose you have the following:

     

    Code Snippet

    create table #test (Col1 nvarchar(10) not null, Col2 nvarchar(10) null)
    insert into #test values ('A', 'B')
    insert into #test values ('C', null)
    insert into #test values ('D', 'E')

     

    Then you decide that Col2 should no longer accept NULL values, but the existing one cannot be updated.

     

    You create the constraint with the following:

     

    Code Snippet

    alter table #test with nocheck add constraint Col2NotNull check (Col2 is not null)

     

    Then the command

    Code Snippet

    insert into #test values ('F', 'G')

     

    works, but the command

    Code Snippet

    insert into #test values ('H', null)

     

    will fail because the insert statement does not match the check constraint.

     

    The key thing is that the constraint is created with the with nocheck option.  This means that the existing data is not verified against the constraint, but any new data will be.

     

    Iain

    Friday, February 29, 2008 5:12 AM

All replies

  • If it's not possible to update NULL values that the column already contains, your only option is to add a constraint to the table to prevent new NULL values from being added to that column.

     

    For example, suppose you have the following:

     

    Code Snippet

    create table #test (Col1 nvarchar(10) not null, Col2 nvarchar(10) null)
    insert into #test values ('A', 'B')
    insert into #test values ('C', null)
    insert into #test values ('D', 'E')

     

    Then you decide that Col2 should no longer accept NULL values, but the existing one cannot be updated.

     

    You create the constraint with the following:

     

    Code Snippet

    alter table #test with nocheck add constraint Col2NotNull check (Col2 is not null)

     

    Then the command

    Code Snippet

    insert into #test values ('F', 'G')

     

    works, but the command

    Code Snippet

    insert into #test values ('H', null)

     

    will fail because the insert statement does not match the check constraint.

     

    The key thing is that the constraint is created with the with nocheck option.  This means that the existing data is not verified against the constraint, but any new data will be.

     

    Iain

    Friday, February 29, 2008 5:12 AM
  •  

    update null values in the table.

    (e.g. update table set columnIDontWantNullAnymore = somevalue where columnIDontWantNullAnymore is null)

    Change the column from null to not null.

    Friday, February 29, 2008 5:21 AM