StrataFrame Forum

Unique Index Problem

http://forum.strataframe.net/Topic19056.aspx

By Aaron Young - 9/4/2008

Hi,

I have a ROWGUID field and index in every table in a SQL Server 2005 database. The indexes are unique and non-clustered and all indexes and fields are called the same - ROWGUID. This database is pre-StrataFrame so I am trying to setup the model in DDT to match it.

After I set the Unique property on the ROWGUID indexes (see attached screenshot), when I try and deploy a new database, it will only create the index on the first table and for all other tables the DDT reports the error:-

"There is already an object named 'ROWGUID' in the database. Could not create constraint."

When the Unique property is set, it looks like the DDT wants the indexes to be called different names. When the Unique property is reset it will create the indexes albeit as non-unique.

SQL Server doesn't need a Unique index to have a unique name so I was wondering if I have missed something?

Aaron

By Greg McGuffey - 9/4/2008

Aaron,



Actually, SQL Server does require that each index have a unique name within a db.schema. When you use SQL Server Management Studio, it creates a unique name for each index automatically. I'd suggest that you prefix the ROWGUID with the table name:



Table1_ROWGUID

Table2_ROWGUID



Hope that helps.
By Aaron Young - 9/4/2008

Hi Greg,

The production database I am trying to duplicate in DDT has been in use for years and is currently installed with many customers in SQL Server 2005. SQL Server 2005 doesn't require a unique name for a unique index in our model as the two attached screenshots hopefully demonstrate.

Basically, I am trying to build an application on top of our existing production database and I only noticed the problem when a database schema tool reported that the DDT generated indexes were not set as unique. I changed this and immediately hit the problem.

I appreciate that I could change the name of every field but our customers have this database currently installed in around several thousand SQL 2005 instances and to upgrade them would be painful when all I really want to do is to have the DDT generate the same database that we are already using.

Aaron

By Greg McGuffey - 9/4/2008

I stand corrected. It looks like an index has to be unique by table, but not for the entire DB. Interestingly, I tested this and it works if I write the SQL directly, but doesn't like it when using SSMS. For this reason, I've always just created unique index names. So, I think this is one that the SF team is going to have to answer. Sorry Ermm
By Aaron Young - 9/4/2008

I think the DDT is trying to create it as a unique constraint rather than a unique index - at least that is what the error message and behaviour suggest.
By Dustin Taylor - 9/9/2008

We'll have to discuss this before I can give an answer either way on this one. We generally consider non-unique index names to be bad practice (there are situations where it can get you into trouble), hence the prevention within the DDT.

Regardless, we'll discuss it and we may allow you to do this in the future, but it's not likely to change for 1.6.6.

By Aaron Young - 9/9/2008

Hi Dustin,

As SQL Server allows it then it would be great if the DDT allowed it too. I have come up with a workaround in the meantime by using a database schema comparison tool so it isn't a big problem.

Thanks,

Aaron

By Aaron Young - 4/6/2009

Hi Guys,



Just thought I would bump this one as it came back to haunt me today. Just to summarise the above I have a unique index called ROWGUID in each table and when I try and make this a unique index in the DDT, it always creates the indexes as non-unique. I only want to create them as indexes and not constraints.



I know there was some discussion above on whether a unique index should also have a unique name, but to quote Microsoft:-



"Index names must be unique within a table, but do not have to be unique within a database."



I am not sure this is actually the problem - it could be nothing more than the Unique index flag is not working. To date, I have had to run another database schema tool after the DDT package has run to correct the problem and today I forgot Sad Hence the bump.



Thanks,



Aaron
By Trent L. Taylor - 4/8/2009

I guess I need more information then because I cannot get SQL Server to allow this.  The DDT actually let's you create the index names.  But SMO is preventing them from being distributed this way.  I will need a sample package and more details to work this this issue.  I am more than willing to add anything that falls within the support of SQL Server 2005 or greater if supported by these versions.  But at the moment, I am not understanding what you are trying to do apparently.
By Aaron Young - 4/8/2009

I will send you a SQL script shortly that will create a full database to show the problem.



Thanks
By Trent L. Taylor - 4/8/2009

Got it. Smile