StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Unique Index Problem

Unique Index Problem

Aaron Young — Wed, 08 Apr 2009 13:25:34 GMT

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

RE: Unique Index Problem

Trent L. Taylor — Wed, 08 Apr 2009 13:25:34 GMT

Got it. :)

RE: Unique Index Problem

Aaron Young — Wed, 08 Apr 2009 10:25:27 GMT

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

Thanks

RE: Unique Index Problem

Trent L. Taylor — Wed, 08 Apr 2009 09:14:06 GMT

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.

RE: Unique Index Problem

Aaron Young — Mon, 06 Apr 2009 16:47:49 GMT

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 :( Hence the bump.

Thanks,

Aaron

RE: Unique Index Problem

Aaron Young — Tue, 09 Sep 2008 18:03:25 GMT

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

RE: Unique Index Problem

Dustin Taylor — Tue, 09 Sep 2008 09:43:24 GMT

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.

RE: Unique Index Problem

Aaron Young — Thu, 04 Sep 2008 18:39:08 GMT

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.

RE: Unique Index Problem

Greg McGuffey — Thu, 04 Sep 2008 18:05:49 GMT

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:

RE: Unique Index Problem

Aaron Young — Thu, 04 Sep 2008 15:20:03 GMT

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

RE: Unique Index Problem

Greg McGuffey — Thu, 04 Sep 2008 13:57:54 GMT

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.