Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Default column values in CREATE TABLE statement Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:30 AM
Points: 9, Visits: 24
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
Post #1560473
Posted Thursday, April 10, 2014 9:29 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:14 AM
Points: 196, Visits: 719
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.
Post #1560504
Posted Thursday, April 10, 2014 10:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 10:09 AM
Points: 141, Visits: 313
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.
Post #1560520
Posted Thursday, April 10, 2014 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:30 AM
Points: 9, Visits: 24
Thanks for confirming!
Post #1560532
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse