SQL Server Performance

Add column with NOT NULL

Discussion in 'General Developer Questions' started by Bredsox, Sep 19, 2006.

  1. Bredsox New Member

    Hi,
    Is there a way around to add column in existing table with NOT NULL? I am having a hard time adding column which references to other column as foreign key. I can link this as FK with no check option if I can add the column. Any suggestion? Thanks


  2. Bredsox New Member

    Another option is when I added the column as NULL, I can't reference that column to the other table even with NOCHECK option. Any suggestion would be great except suggestion for dropping and recreating it which is not an option. Thanks

  3. Bredsox New Member

    quote:Originally posted by Bredsox

    Another option is when I added the column as NULL, I can't reference that column to the other table even with NOCHECK option. Any suggestion would be great except suggestion for dropping and recreating it which is not an option. Thanks




    Never mind, I got it.
  4. FrankKalis Moderator

    Well, never tried this myself, but I see no reason why it shouldn't work.

    You can add a column NOT NULL when you also create a DEFAULT for that column. Now since you can use a UDF for a DEFAULT in which you can reference columns in other tables, you might be able to reference the foreign keys.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. sudiptakr New Member

    Another way is add the column as null. Then update the values. After updating all rows in the column alter the column defination as not null
  6. Bredsox New Member

    quote:Originally posted by FrankKalis

    Well, never tried this myself, but I see no reason why it shouldn't work.

    You can add a column NOT NULL when you also create a DEFAULT for that column. Now since you can use a UDF for a DEFAULT in which you can reference columns in other tables, you might be able to reference the foreign keys.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de


    I added a column as NULL and referenced it as FK constraint for other table WITH NOCHECK option while creating constraint and it worked. That's acceptable for business.
    Frank,
    I am not sure how to use UDF for a DEFAULT though. Can you please explain little more on this. Thanks
  7. FrankKalis Moderator

    Ignore the German wording, the SQL code is quite self-explaining:<br /<a target="_blank" href=http://www.insidesql.de/beitraege/dev_basics/eine_tadellose_sequenz.html>http://www.insidesql.de/beitraege/dev_basics/eine_tadellose_sequenz.html</a><br /><br />You can also use UDF's in CHECK constraints, too btw. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. Bredsox New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Ignore the German wording, the SQL code is quite self-explaining:<br /<a target="_blank" href=http://www.insidesql.de/beitraege/dev_basics/eine_tadellose_sequenz.html>http://www.insidesql.de/beitraege/dev_basics/eine_tadellose_sequenz.html</a><br /><br />You can also use UDF's in CHECK constraints, too btw. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />This is totally new to me. Great to know that. Thanks Frank for the link.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page