StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Import SQL DB - PK comes over as Unique constraint

Import SQL DB - PK comes over as Unique constraint

Charles R Hankey — Mon, 06 Aug 2007 14:41:47 GMT

I imported a VFP database to DDT, made the necessary corrections and created a SQL database.

All went well. Then realized I didn't want clustered UID pks. Made the change in DDT but DDT could not uncluster the indexes ( error said they had to be dropped and recreated and DDT didn't seem to want to do that )

Okay, so I used T-SQL to drop all constraints, keys etc on all the tables in the sql dbc. Then I recreated all the pks as non-clustered.

Now I wanted to reimport into DDT. Decided to do it into a different project. i think that is where it started to go wrong. All the structure came over and the indexes came over but they are just unique constraints - not primary keys and will not accept a default value of NEWID()

They show up as primary in the field list

but the index

If I try to add NEWID() as a default value I get

DataLayerSavingException
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_indexes_indexfields". The conflict occurred in database "StrataFrame", table "dbo.DBEIndexes", column 'idx_pk'.
The statement has been terminated.
SqlException
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_indexes_indexfields". The conflict occurred in database "StrataFrame", table "dbo.DBEIndexes", column 'idx_pk'.
The statement has been terminated.

I haven't had this problem with my other databases

I tried completely deleting all the DDT projects connected with this database and then importing to a new project in DDT but get the same result.

To test this :

I created a new database called Testit. One table Names ( ckey UID, cname char(10) )

Created PK on ckey

Imported into a new DDT project and got the same result. pk_ckey is Unique Constraint, not PK

Am I doing something wrong or is this a bug? Don't think I had this problem in 1.6

RE: Import SQL DB - PK comes over as Unique constraint

Charles R Hankey — Mon, 06 Aug 2007 14:41:47 GMT

Thanks!

FWIW here is a little more on this to throw in the notes :

After importing tables with UID pks and having them show up in the index node in DDT as a unique constraint but not a pk ( though the pk in the field list is still correctly identifiied ) I did as you suggested - deleted the index in the index node, opened the table in DDT and saved it. The PK then showed correctly on the node ( you asked about this earlier in this thread )

I manually corrected all the tables in the DDT this way, dropped the database in SQL Server and deployed from DDT.

All seems well except there is a constraint that would not be there if this had been created in SQL.

I have no idea if this is ever going to make a difference on the sql end and if this is just an anomaly created by dropping the constraint from the index node in DDT and having the Pk automatically replace it. But it is a little disturbing that there is nothing in DDT ( or at least I don't see it ) to indicate this constraint is still going to be deployed after being deleted. ( I realize a pk is itself a constraint, but the DF_agent_ckey seems gratuitous and I don't know if it may get me in trouble in some other way so I am just going to write a script in sql to drop all the dk_ constraints ) Just wanted you to know that deleting it in the DDT node doesn't seem to keep it from being deployed.

I'm attaching the package just so you can see what is under the hood.

Realize you have other stuff on your plate and I can work around this for now but

How would I roll back to DDT 1.6 for now as I don't think the importing from SQL table problem exists there?

TIA

Charles

RE: Import SQL DB - PK comes over as Unique constraint

Trent L. Taylor — Mon, 06 Aug 2007 10:37:07 GMT

Charles,

I have not reproduced this one issue just yet. I beleive you, I just have not got it in a reproducable format yet since I need to go manually create a SQL table first....I always use the DDT :). So it is on my list of things to reproduce before the next release, but I just have not had the chance to reproduce this yet.

RE: Import SQL DB - PK comes over as Unique constraint

Charles R Hankey — Mon, 06 Aug 2007 09:29:01 GMT

Have you had any luck reproducing this?

UID pks are definitely not importing correctly from SQL 2005 databases into DDT

RE: Import SQL DB - PK comes over as Unique constraint

Trent L. Taylor — Thu, 02 Aug 2007 09:41:56 GMT

Charles,

Thanks for all of your kind words. We will look into this. Thanks for the info.

RE: Import SQL DB - PK comes over as Unique constraint

Charles R Hankey — Wed, 01 Aug 2007 11:10:00 GMT

This seems to be a UID issue.

If I create a table with an integer identity primary key it imports as expected. Create exactly the same thing with a UID as pk and the key become a unique constraint instead of a pk

I'm pretty sure this is a bug .:)

RE: Import SQL DB - PK comes over as Unique constraint

Charles R Hankey — Tue, 31 Jul 2007 14:16:08 GMT

In the DDT project for the sample I outlined above if I delete the Index under the index node the ckey field remains checked as primary but on save a true pk index is in the node as it should be and all is well.

I think that in 1.6 - my first exposure to DDT when I was testing everything - importing the test database I outlined above would have created a pk index without problems.

I'll be curious to know if anyone who still has 1.6 can confirm that or what results you get in running the test against 1.61

( understand - these nit pickings are small ones - the DDT is one of the most incredible tools i've experienced in 25 years of playing with databases. I'm currently talking it up in the VFP community as a must-have even if you aren't doing .NET. :) )

TIA

RE: Import SQL DB - PK comes over as Unique constraint

Trent L. Taylor — Tue, 31 Jul 2007 10:49:28 GMT

I will try to set this up and test it later. One question though, if you manually delete the index then modify the structures for the table then click Save, it should re-create the index for you. Does the index get created properly after the save?

RE: Import SQL DB - PK comes over as Unique constraint

Charles R Hankey — Mon, 30 Jul 2007 10:26:57 GMT

I think I just complicated the problem with the way I asked the question :)

The problem is not going from DDT to deployment, but in importing an existing SQL DB

Please try this : Create SQL database Testit. Create 1 table Names ( ckey UID, cname CHAR(10))

Put PK on ckey ( seems to not matter if clustered or non-clustered )

do this directly in SQL Server, not through DDT

Now create a new project in DDT and import Testit. Look at index node and notice it is a Unique Constraint. Try to put a NEWID() default value on ckey

What results do you get?

RE: Import SQL DB - PK comes over as Unique constraint

Trent L. Taylor — Mon, 30 Jul 2007 09:19:16 GMT

I may have missed something, but I am not having a problem. I checked the Primary Key option on the ckey field and it created the index for me.

If you then look at the index, it has created the clustered index properly.

Please let me know if I am not understanding your problem. Thanks. :)