Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: Help Altering Database Table from NULL to NOT NULL

    I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You alter columns to NOT NULL, not tables. Can you explain why you're trying to do this? What are you going to put in all the columns that have NULL? Why does the table have NULL in the first place. You should post what the table looks like with a couple sample rows.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Mar 2004
    Posts
    14
    My apologies I meant a column. Currently a remote table that I'm adding a new column to where the people there have no idea how to use SQL Server. I've made a new SQL script to add a new column and have to set the column to NULL since its a new column being added to an existing table. After the apropriate data is in the new column I need to set the column to NOT NULL for future entries......

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Could you add the column allowed to have NULL, then update the table where the column get's some value, then alter the table setting the column to NOT NULL?

  5. #5
    Join Date
    Mar 2004
    Posts
    14
    Thats pretty much what I'm trying to do but via scripts. Circumstances keep me from doing this manually so I'm trying to get it setup via a script......

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    so at which of the three steps are you stuck?

  7. #7
    Join Date
    Jan 2004
    Posts
    164
    Are you just trying to change the column null to not null if something is meet?

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    if exists (select 1 from your_table where new_column is null)
    raiserror ('You can't make column NON-NULLABLE if there is at least 1 NULL in it!, 15, 1)
    else
    alter table your_table alter column new_column <data_type> not null
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •