locked
add a new column to a table with conditions RRS feed

  • Question

  • hi friends
    i want to add a column to a table with conditions below :
    1- it has a default value=0
    2- this column can get null value
    3- no constraint should be created


    is it possible ?

    thanks

    Tuesday, May 28, 2013 3:22 PM

Answers

  • Hi,

    Try like this,

    ALTER TABLE TestTable
    ADD Col INT NULL DEFAULT(0)
    GO

    3 rule not possible,i guess

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Tuesday, May 28, 2013 3:26 PM
  • When you add a column with a DEFAULT constraint, then this Default is only assigned to new rows where the column's value is not explicitly specified. It does not change any existing row. The new column will have NULL for all existing rows.

    If you want all existing rows to have a 0 in your newly added column, then you need an UPDATE statement to do this (UPDATE TestTable SET Col = 0 WHERE Col IS NULL). Depending on the size of your table, this may take a while.


    Gert-Jan

    Tuesday, May 28, 2013 10:15 PM

All replies

  • Hi,

    Try like this,

    ALTER TABLE TestTable
    ADD Col INT NULL DEFAULT(0)
    GO

    3 rule not possible,i guess

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Tuesday, May 28, 2013 3:26 PM
  • A default value is a constraint.

    Tuesday, May 28, 2013 3:31 PM
  • Hi,

    You can use default constraint to satisfy first 2 conditions, but to satify 3rd condition instead of trigger can be used.

    Create Table Test(id INT,id_value VARCHAR(100))
    GO
    INSERT INTO  Test
    SELECT 1,'A'
    UNION ALL
    SELECT 2,'B'
    UNION ALL
    SELECT 3,'C'
    GO
    ALTER TABLE Test ADD new_column INT
    GO
    CREATE TRIGGER trg_Test_instdoff on Test
    INSTEAD OF INSERT	
    AS
    BEGIN
    INSERT INTO TEST(id,id_value,new_column)
    SELECT id,id_value,ISNULL(new_column,0) FROM INSERTED	
    END
    GO
    INSERT INTO  Test(id,id_value)
    SELECT 4,'D'
    UNION ALL
    SELECT 5,'E'
    UNION ALL
    SELECT 6,'F'
    GO
    SELECT * FROM Test
    

    Tuesday, May 28, 2013 3:48 PM
  • Hi,

    Try like this,

    ALTER TABLE TestTable
    ADD Col INT NULL DEFAULT(0)
    GO

    3 rule not possible,i guess

    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    thanks

    i use your code . a column is added but without default value . it is null . i want that it equal by zero

    Tuesday, May 28, 2013 3:52 PM
  • Hi,

    your Rule 2 :

    2- this column can get null value

    So if you want o instead of NULL,while inserting use ISNULL(col,0)


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Tuesday, May 28, 2013 3:58 PM
  • Do an ALTER TABLE statement to add the column. Unlike ANSI/ISO standard SQL, a DEFAULT is treated as a constraint in T-SQL. Constraints have to be added with further ALTER TABLE statements. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, May 28, 2013 5:52 PM
  • When you add a column with a DEFAULT constraint, then this Default is only assigned to new rows where the column's value is not explicitly specified. It does not change any existing row. The new column will have NULL for all existing rows.

    If you want all existing rows to have a 0 in your newly added column, then you need an UPDATE statement to do this (UPDATE TestTable SET Col = 0 WHERE Col IS NULL). Depending on the size of your table, this may take a while.


    Gert-Jan

    Tuesday, May 28, 2013 10:15 PM