|
An hour ago I needed to use the Data Generation Plan to add
generated data to my database. After setting up the generation plan
I executed it. I got the following error:
"violation of primary key constraint 'PK_ '.Cannot insert
duplicate key in object 'Pk_...'"
Apparently Visual Studio tries to insert the ID's and starts
counting with 1. This will work fine on an empty database, but not
in this case. I searched a while to find a solution, but couldn't
find any. This is what I did to solve the problem (it is a little
bit of hacking, but it works):
At first I opened the .dgen file (there is a directory named
"Data Generation Plans" in your database solution) with notepad++.
The id's look something like this:
<Element Type="ISql100SimpleColumn"
Name="[dbo].[Authentication].[ID]">
<Property Name="IsNullable" Value="False"
/>
<Property Name="IsIdentity" Value="True"
/>
<Relationship Name="TypeSpecifier">
<Entry>
<Element Type="ISql90TypeSpecifier">
<Relationship Name="Type">
<Entry>
<References ExternalSource="BuiltIns" Name="[int]"
/>
</Entry>
</Relationship>
</Element>
</Entry>
</Relationship>
<Annotation
Type="ColumnConfigAnnotation">
<Property Name="Selected"
Value="True" />
<Property Name="Seed"
Value="5" />
</Annotation>
</Element>
My first try was to set the IsIdentity property to false. Visual
studio still handles the field as a readonly field. So this isn't a
solution.
The second try was to remove the IsIdentity property. After
saving Visual Studio asked me to update the plan. After the update
Visual Studio asked me to update the plan with the latest database
properties. Here you will have to decline. If you do this, your
changes will be undone.
After this you will be able to set the minimal value for the
id-column. Fill in the highest available id from you database +1
and you're almost good to go!
SQL Server will not accept identity insert by default. You must
enable this before you run the plan. Another way to do this is to
temporarily alter the tables you want to use and disable the
Identity Seed. If you run the Generation Plan now, it will run
ok.
Of course be very cautious. You are disabling 'safety controls'
and you will be able to mess up your database.
|