How to set a columns to UNIQUE (MSSQL)?

[pocketom]

I have modified the build-in transformation macros to let them create together a DDL model and a C# class model from a platform independent model according to my environmental constraints (C# MSSQL, Fluent/NHibernate). I use it to generate a persistence model that needs no further mofications to code and DB schema after generation. So far that works fine, but now I started fine-tuning and limitations show up.

I want to set a column's unique flag from within the script (from an attribute marked with a <unique> stereotype in PIM model), but obviosly that's not posible (stated also in documentation, but no reason why). Does anybody know why this isn't possible or if there is a good reason for not doing it?
I'm looking for a workaround to overcome this. There is a function macro EXEC_ADD_IN available in the intermediary language, but as said I'm not sure if that's a good idea to hack it in that way, also I couldn't find in the documentation if the SDK would allow me to set the unique flag to a column.

I need it also for setting NOT NULL via macro...

Any help is highly appreciated  8-)

[pocketom]

Nobody? I guess this should be very simple, but somehow I still don't get it  

[FritzTheCat]

Please have a look at this if you are still interested in MDA Transformas for Unique and NotNull: unique is set as tag on the attribute


----------------------------------------------------
this is a response to a support question I asked months ago:
Thank you for your enquiry.

In addition to setting the "unique" property to the column you also need
to create a stereotyped operation to fully define the unique constraint
in your transformation.

The following example is a modified version of the "Attribute" template
for the DDL transformation that sets the "unique" property and creates
the required Operation.

We hope this helps.  If you have any further questions, please feel free
to ask.


=== START TEMPLATE ===

$isUnique = %attTag:"Unique"%
$datatype=%CONVERT_TYPE(genOptDefaultDatabase,attType)%

Column
{
  %TRANSFORM_CURRENT("type", "stereotype", "collection", "constant",
"containment", "ordered", "static", "volatile")%
  type=%qt%$datatype%qt%
%if $isUnique == "TRUE"%
  unique
%endIf%
}

%if $isUnique == "TRUE"%
Operation
{
  name=%qt%UQ_%className%_%attName%%qt%
  stereotype=%qt%unique%qt%
  
  Parameter
  {
    name=%qt%%attName%%qt%
    type=%qt%$datatype%qt%
  }
}
%endIf%

=== END TEMPLATE ===



----------------------------------------------------


Also use the search button, find by message or header and go for posts dating back to more than a year. use mda or  unique or transformation, not null as words to look for.

Sparx is right now changeing the nullable foreign key behaviour of the DDL tool, this might be of interest to you. In the physical model, after transformation took place, the multiplicity of the association between table classes let DDL tool decide, what fk is nullable and what is not.

let me know if you need more to get around your issue
Peter

[pocketom]

Hi! Thanx a lot, that's exactly what I was looking for!

Cheers
Thomas

[bstoddart]

This did work for me except in one situation.  "notNull" does not work in the ForeignKey context (see below) with in the connector template.    Everything works as expected, but nulls are allowed.  I want to used multiplicity to govern the requiredness of a created column.  Is there a work around?

ForeignKey
{
  %TRANSFORM_REFERENCE("FK1",connectorGUID)%
  Source
  {
     %TRANSFORM_REFERENCE("Table",connectorSourceElemGUID)%  
    name="foo"
    multiplicity=%qt%$srcMult%qt%
    Column
    {
       type="int"
       name="field1"
       notNull
    }
  }
  Target
  {
    %TRANSFORM_REFERENCE("Table",connectorDestElemGUID)%
    multiplicity=%qt%$dstMult%qt%
    Column
    {
      name=%qt%%CONVERT_NAME(connectorDestElemName, "Pascal Case","Camel Case")%Id%qt%
      type=%qt%%CONVERT_TYPE(genOptDefaultDatabase,"guid")%%qt%
        notNull
    }
  }
}

[pocketom]

Did you manage to set the NOTNULL constraint? I think it would be a good idea to determine this by multiplicity.
Another way would be setting NOTNULL when the composition connector was used (but it seems to appear as "Aggregation", not sure if there is an option to distinguish compositions).