Toad World® Forums

Generating SQL for default constraints


#1

Database: MS sql 2014
Toad DM version 5.5
I set default values for generating default constraint name df
I Am trying to generate sql code for an entity where I have a column ColumnIndicator where I set default value to ‘T’ using a default rule
When I generate a sql code I don’t see any sql for default constraint. All it is doing is adding a default value besides the column
[ColumnIndicator] bit DEFAULT ‘T’ NULL
I tried another way by specifying default value constraint name at column level and this generates constraint name but I need to do this everywhere wherever I have default values.
[ColumnIndicator] bit CONSTRAINT [df_ColumnIndicator] DEFAULT ‘T’ NULL
I am trying to automate this by configuring at one place. Is there an option available to do
Also would like to know if we can display default values of a column in physical model


#2

Hello,

yes you are right. If you create an object of type Default and use it in the Attribute properties via combo box “Default Rule” to specify the default value to a column then TDM does not generate CONSTRAINT and you have to set its name manually in the edit “Default values constraint name”.

There is an option to specify default name of the “Default values constraint name” (go to the menu Setting | Default Values… | Entity | Attribute and set a new default value for the property “Defautl Constraint name”, apply and restart TDM). If you create a new attribute there will be a name of default constraint by default. But note it will work only for newly created attributes.

If you want to specify the “Default values constraint name” in the existed attributes you can use the following scrip. Go to the menu Expert model, select Scripting windows, move your model from the Available object (on the left) to selected object in the right, insert the code and run the script (feel free to modify it). It set the name to the columns where any default values is specified.

function main()
{
var i, j;
var Entity, Attr;

for (i=0; i<Model.Entities.Count; i++)
{
Entity = Model.Entities.GetObject(i);
for (j=0; j<Entity.Attributes.Count; j++)
{
Attr = Entity.Attributes.GetObject(j);
if (Attr.DefaultValueAsText != “”)
{
if (Attr.DefaultConstraintName == “”)
{
Attr.DefaultConstraintName = “def_”+Entity.Name+"_"+Attr.Name;
Log.Information(“Default Constraint Name in attribute “+Attr.Name+” (Entity “+Entity.Name+”) was changed.”);
}
}
}
}
}

TDM does not allow to display default values on the workspace but you can display in Attribute grid in the Entity properties in TDM 6.2 and higher at least.

Regards,

Dave