Post reply

Default column values in CREATE TABLE statement

  • April 10, 2014 at 8:38 am

    #290237

    DB2 guy here, about to begin a conversion project...

    I wonder if someone could answer a question about column defaults when creating a table. In DB2, to create a default constraint, the value of the default does not have to be specified in the create:

    CREATE TABLE MYTABLE(

    ...

    COMMENT CHAR(200) NOT NULL WITH DEFAULT);

    In this case, DB2 will insert blanks (by default) when a row is inserted without a comment.

    Does MSSQL have similar functionality, or must the default value of a CHAR, VARCHAR, INT, etc, always be included in the CREATE statement?

    Thanks a million,

    Moody

  • April 10, 2014 at 9:29 am

    #1704952

    djmoodyjames (4/10/2014)

    DB2 guy here, about to begin a conversion project...

    I wonder if someone could answer a question about column defaults when creating a table. In DB2, to create a default constraint, the value of the default does not have to be specified in the create:

    CREATE TABLE MYTABLE(

    ...

    COMMENT CHAR(200) NOT NULL WITH DEFAULT);

    In this case, DB2 will insert blanks (by default) when a row is inserted without a comment.

    Does MSSQL have similar functionality, or must the default value of a CHAR, VARCHAR, INT, etc, always be included in the CREATE statement?

    In MSSQL you would need to add the default value you wish to use either in the CREATE or with an ALTER TABLE.

    If you do not a NOT NULL column will throw an error.

  • April 10, 2014 at 10:01 am

    #1704964

    use this code to create the default constraint.

    ALTER TABLE tbl_nm

    ADD CONSTRAINT DFLT_Tblnm_Columnnm DEFAULT default_value for [column name]

    Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

  • April 10, 2014 at 10:37 am

    #1704975

    Thanks for confirming!

Viewing 4 posts - 1 through 3 (of 3 total)

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