Creating a Column and setting a default value

Question

Creating a Column and setting a default value

Thomas Campbell

SSC Veteran

Points: 275
More actions
August 5, 2004 at 10:40 am

#85944

I've searched through the forum and wasn't able to find anything. I am using DMO for an installation of a database (along with some other items.) I am creating the database, the tables, the views, triggers, storedprocs, etc...
So far, it's coming along nicely, however, I haven't been able to figure out how the set the default value for a column. For instance, I use a field called CreateDate and I set the default value to getDate().
If I can't do it in DMO directly, I guess I'll just execute a script to set those items, but I'd rather not do that if possible as it will be more complicated.
thanks in advance.
Tom

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

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

AKM SSCommitted Points: 1538 More actions · Answer 1

You have to create a Default object, then bind it to an appropriate Column object using the Default object's BindToColumn method. I quote from BOL:

To create a SQL Server default

Create a Default object.
Set the Name property.
Set the Text property to establish the default value generated for an INSERT statement. The value of the Text property must match the constraints of the constant_expression parameter of the CREATE DEFAULT statement. For more information about how to set the Text property, see CREATE DEFAULT.
Add the Default object to the Defaults collection of a connected Database object.

After the SQL Server default has been created, use the BindToColumn and BindToDatatype methods of the Default object to bind the SQL Server default to SQL Server columns and user-defined data types.

Note that when it talks about Default.Text being a constant_expression, that is a bit misleading; in fact "Any constant, built-in function, or mathematical expression can be used."

So it looks like you just create a Default object, call it something, set its Textto 'GETDATE()', add it to the Defaults collection of your db, then do MyDefault.BindToColumn("mytable","mycolumn").

jfmccabe SSCrazy Points: 2547 More actions · Answer 2

That's a *general* default. To set a column level default look at DRIDefault. Again quoting from bol

To set a DEFAULT constraint on a new SQL Server column

Create a Table object.
Create a Column object.
Get the DRIDefault object from the new Column object.
Set the Text property of the DRIDefault object to the desired default for the column.
Add the Column object to the Columns collection of the new Table object.
Add the Table object to the Tables collection of a connected Database object.

To set a DEFAULT constraint on an existing SQL Server column

Get a Table object from the Tables collection of a connected Database object.
Use the BeginAlter method of the Table object to mark the beginning of changes to the SQL Server table.
Get the desired Column object from the Columns collection of the selected Table object.
Get the DRIDefault object from the new Column object.
Set the Text property of the DRIDefault object to the desired default for the column.
Use the DoAlter method of the Table object to submit changes to the SQL Server.

Simple really

Thomas Campbell SSC Veteran Points: 275 More actions · Answer 3

Ok, I had read that stuff too. Conceptually, it all makes sense. However, I have not been able to find a definition for the DRIDefault property of a column and my understanding is that it is read only.

Also, the Default property is expects a string, not a Default Object. I am doing this in VB.NET

Here's my code for the table and column.

dim oDatabase as New SQLDMO.Database

oDatabase.name = "MyDatabase"

OServer.Databases.add(oDatabase)

Dim oTable as New SQLDMO.Table

oTable.Name = "tbl_MyTable"

oTable.Owner = "dbo"

Dim colDate as new SQLDMO.Column

colDate.Name = "CreateDate"

colDate.Datatype = "datetime"

colDate.AllowNulls = False

'I tried this... didn't work

dim t and new SQLDMO.Default

t.Name = "MyDate"

t.Text = "GETDATE()"

colDate.Default = t.text

'However, this did not work.

'the error messages states

'Default'user_name()' not found

oTable.Columns.Add(colDate)

oDatabase.Tables.Add(oTable)

Thomas Campbell SSC Veteran Points: 275 More actions · Answer 4

Whoa.... I figured it out... and it produced a behaviour in SQL I've never seen before. I'm posting this in case anyone else would like to be able to do this.

First, you can't add a Default by using the colDate.Default = <something>. It doesn't work that way.

What you do is create a Default in the Database.

Dim t as New SQLDMO.Default

t.Name = "TheDate"

t.Text = "GETDATE()"

oDatabase.Defaults.Add(t)

Then, after the table is created to apply the default to the table and column as such:

oDatabase.Tables.Add(otable)

t.BindToColumn("tbl_MyTable","CreateDate")

The odd behavior is when you go look at the Design view of the table in EM. When you look at the column's properties, next to Default value it will say "TheDate". It does work properly though. I've never seen that before.

Anyways, thanks for the help. Even though, nobody had a direct answer, it lead me to re-read the documentation and figure out how to make the darn thing work.