Column Default (SQL Server)

[2005_guy]

As the SQL that creates a default does not specify a constraint name (create table test columna int not null default 0), the default is allocated a system assigned name at run time.  This causes problems later on when this default has to be manipulated, as a custom script is needed for each environment, due to the different system assigned default names.
Is there any way in EA to assign a column default as a named constraint (create table test columna NULL constraint DF_test_columna DEFAULT (0))?

thanks,
this is really messing up our release strategy

[«Midnight»]

Not a bad idea at all!

But I suspect you're correct here, that EA does not assign a name when you check the Unique box.

As a workaround, see if you can create a single-column unique constraint as a constraint. (I.e. do not check the Unique box, but add the constraint after saving the column the first time.)

Please let us know if this works.

Meanwhile, please submit this as a feature request to Sparx. When you do so, quote the title of this thread, and paste in a link to your post. That will give Sparx a pointer should they wish to participate in this discussion. Also, please let us know if they have any suggestions or other response.

You can get to the correct page by clicking the Feature Request link under Support at the bottom of any forum page.

David

[2005_guy]

Hi David,

The unique constraint is created as I would expect, (named), unfortunately this does not help my case.  We tend to use the default clause heavily to numb the pain of not nullable columns. There does not appear to be column attributes that can be set in the required way (aside of PK/Not null/unique/identity).  As EA is SQL Server 2000 compliant, rather than 2005, this may explain the absence.

I'll follow up with Sparx, they may have another angle.

many thanks
2K5

[«Midnight»]

2K5,

Yes, EA pretty much conforms to SQL 2000, versus 2005.

However, perhaps things could improve for you. If you upgraded from EA 6.5 since you started your project, then you won't see some metadata regarding SQL 2005. That's because EA 6.5 did not have the necessary reference data, so it never got into your project.

If so, create a nwe project with EA 7.0 - it can be anything, just an empty project will do. Extract the reference data relating to databases. [Check the list in the Extract dialog, I don't remember the headings, but they are obvious.] Now import this reference data into your EA 6.5 project. Suddenly you'll see SQL 2005 as a supported database, with such things as the SQL field type.

HTH, David

PS: Meanwhile, please report the original issue to Sparx as I mentioned earlier. I too would welcome improvement in this area.

[Martin Terreni]

This IS a bug!!

[2005_guy]

Thanks Martin,

Will you handle the admin of entering this as a priority fix?

regards,
2K5

[Martin Terreni]

done

[2005_guy]

You're a man of few words!  How do I track this to discover the fix rollout?

regards,
2K5

[Martin Terreni]

issue "Registered Support Request - Not automaticaly upgragin to SQL2005"
No Idea how to track ti through site.
but if I get some answer i'll post it.

[2005_guy]

Ok, my issue is with assigning column defaults a name (via a constraint), rather than the SQL Server compliance level, are we on the same page?

[Martin Terreni]

Nope, sorry. I didn't drill in to that issue , I was just upset that I'm not getting autmaticaly SQL2005 option

[2005_guy]

Ok, can you log a feature request on this or is there another protocol to follow?  I don't really give a monkeys about the SQL Server compliance level.  The absence of this functionality will be the end of your product on many sites, as it causes great difficulty with change management.  So please assign it +1000 over the earlier issue you raised.

TIA
2K5

[Martin Terreni]

Sorry, I dont think I fully understand the problem ( at least I didn't go deep into it).
I think some one who does should open it, so if sparxians aks questions it will be possible to give them answers.

[2005_guy]

Problem is EA (6.5 & 7) will produce the following SQL when a default of 0 is assigned to a column =>

create table test columna int NOT NULL default (0)

What is required (EA cannot do this) is the ability to name the default by adding a default constraint so that it can be consistanly referred to in SQL DML scripts.  By not supplying a name SQL Server allocates a system generated name at run time. This means a different SQL script has to be written for each environment that requires change.   =>

create table test columna int NOT NULL constraint DF_test_columna DEFAULT (0)