DDL named default constraints

[klewin]

we see how to set initial column values in a data model.

How can you create named default constraints?

We are attempting to define for SQL 2000 ddl generation.

Thanks for the assistance

[jaimeglz]

Hi klewing,

I'm currently using Oracle and don't have ready access to SQL Server, so I have not tested whether the following will work for your purposes.

To create a CHECK constraint, follow the instructions in EA Help for "check constraint" (search for "check constraint" and read the documentation on "Indexes, Triggers Check Constraints").

Here's and example.

I added a "GENDER_CONSTRAINT" operation for the t_user table. I chose "check" as the op. stereotype. In the notes section of the operation, I typed "gender = 'F' OR gender = 'M'", then clicked on the Columns tab, and selected the "gender" column. I then saved the operation.

After generating the DDL code for t_user, the ALTER TABLE... ADD CONSTRAINT GENDER_CONSTRAINT... is included in the DDL code.

An alternative to CHECK constraints would be quite more elaborate and involved, but very much worth the effort if you want to automate a large ammount of operations: Create a new OpTable stereotype (Settings -> UML -> Stereotype), say "rule", and create a single new datatype for SQL Server 7 language (Settings -> Code Datatypes) so you can have a SQL Server 7 language included in your custom code generation templates. You can have a lot of fun creating your custom code generation templates (Settings -> Code Generation Templates). For large and complex databases this will save you a lot of time, but it does take a good deal of effort and practice to unleash the power of this EA feature. You can follow EA documentation on the customization and use of Code Generation templates and control macros, and don't forget that to generate this custom code you proceed by "Generate Code" (Ctrl + G) and not by "Generate DDL".

Hope this helps (and I hope I don't get you into a problem by suggesting to try customized Code Generation Templates).

Jaime

[klewin]

Thanks for the reply.

Does this apply to default values for a column?
For example, we are creating a datetime column with a default value of getdate().

We would like a default constraint created named DF_tablename_columnname.

[jaimeglz]

I don't think CHECK can handle a getdate() default correctly; but custom templates definitely can. At the Operation Body level you could specify something like the following:

$param = %list="Parameter" @separator=""%

IF EXISTS (SELECT ..... FROM SYSOBJECTS WHERE OBJECTNAME = %opName%) sp_drop....

sp_createdefault(%opName%,%className%,$param.....)

(where opName will be substituded for the name you gave to your operation, className will be substituted for the name of the table, and $param will return the column or columns you specified for the operation).

My Transact-SQL is gathering dust, so please excuse whatever is wrong in the previous code; the idea, however, is that you can generate whatever suits your needs. However, it takes a little time to get used to the IE custom template structure and language, so please first take a look at existing templates, like the C++ templates.

Hope this helps, but consider that it could be difficult to get custom templates to work for you.

Jaime