Fun with Identity fields in Sparx EA

 

After a bit of inventive adjective use the other day I found out that the way you create new database tables in Sparx Enterprise Architect database models has an impact on whether or not you can specify an Identity column for a SQL Server database table.  For those of you asking why is this important? – well your standard SQL server self-incrementing Primary Key field is usually defined as one of these.

Before getting further into it lets first talk about two ways that you can create database table objects (Elements) in Sparx EA.  Both of the following approaches assume that you have already have a <<Database>> model and are familiar with database modelling in Sparx EA.

  1. Via the Project Browser – within the project browser navigate to your desired <<Database>> model, then right-click on the Tables sub-folder and choose New Element.
  2. Via the Database Builder – within the project browser navigate to your desired <<Database>> model, right-click on it and choose to open it within the Database Builder tool.  Within the database builder choose to create a New Table in the Tables folder.

Now we’ve got an idea of the two difference approaches lets dive in with examples.  However today lets do things in reverse – lets start with the Database Builder example so that I can show you what we want to occur in order to define Identity fields, and then we’ll do it the other way and show what happens.

Database Builder Approach

  1. First create a <<DataModel>> SQLServer 2012 Model Structure from the Wizard dialog (for this example it has the “Database builder” label).
  2. Navigate down to the <<Database>> Database A folder and choose to open it in the Database Builder.
  3. Create a test table with two fields (say int and char) and nominate the int field to be a Primary Key.  If you then click on the PK field you should have something similar to the following:

You see that you’ll have all of the expected property/attributes that you can set for an Identity field under the AutoNum option – whether or not it automatically increments, size of the increment, and starting value.  All good – that’s exactly what we expect and want.

Project Browser Approach

  1. Now create a <<DataModel>> SQLServer 2012 Model Structure from the Wizard dialog (for this example it has the “Project Browser build” label).
  2. Navigate down to the <<Database>> Database A folder and create a table in the <<Database>> Database A – Tables folder, by right-clicking on Tables and selecting New Element.
  3. Bring up the Attribute editor for the new table and create two sample columns – one integer that will be our sample primary key, and a simple char field.  Make the integer column a Primary key using either the PK checkbox, or the Constraints/indexes window.  Now when you select the Primary key field you’ll see that you don’t get any AutoNum options for the field.

Not the best situation if you really want to set up identity fields and the like.  So the obvious takeaway from this is to create your database entities inside of the Database Builder tool if you don’t want inadvertent restrictions.  But what if you have already? or you’ve inherited a model that has this situation?

Making the best of a bad situation

So you want to apply identity/auto-num behaviour to table fields but discover that you can’t get the options displayed as shown above?  Not to worry there is a workaround that can get you back on the right path.

  1. Open the non-behaving <<Database>> model in Database Builder.
  2. Navigate to and select the table that you want to work on.  Right-click on the table and select to Copy as New.
  3. Because the new copy was created using the Database Builder interface the it allows you to set the AutoNum properties for your desired Identity field.
  4. Unfortunately you now have two objects – so you need to delete the original and rename the copy back to its original name.

Be warned this approach may break any diagrams, associations, etc. that were using the old object – so it would be prudent to double-check any uses of it.

That’s about it – until de next time – Bork, bork, bork. Brent.

 

Brent is a data warehouse developer who uses Azure, SQL and AWS a fair bit.  Read his blog on how Sparx EA handles database structures, or some of this other blogs.

Did you know we can teach you the BEAM* method to collect business requirements for data warehouse design in an agile, proactive way?

0 Comments
Submit a Comment

Your email address will not be published. Required fields are marked *