I'm still trying to understand how DO generate keys. I deployed my application, entered data and everything went fine. Then I generated a script of my schema as well as data
I ran this script in another computer, the database was created and the data inserted without any problem.
Then I changed my configuration in my application to point to the new database. As soon as I tried to entered data using the application I got the exception 'Violation of PRIMARY KEY constraint' 'Cannot insert duplicate key in object 'dbo.tableName'. The duplicate key value is (1).'
I thought that when DO tried to rebuild the domain, it would see that the tables were already there and then it would get the Current Identity for each table and insert a number of rows into the tables, where this number is equal to whatever is in KeyGeneratorCacheSize, then store the new IDs in a cache variable (Key Generator) for each table and then delete those rows.
Apparently this is not what happens in my case. After I recreate the database, it tries to insert a row with ID 1.
Can we give me some help on how to make DO work with an existing database? I mean, a database that was NOT created by building the domain, but has all the tables that would be created if I had built it using the application.
asked Jun 30 '15 at 07:21
Hello Jamal Lucas.
Short answer: Your script probably doesn't take into account state of sequences or generator tables (it depends on your RDBMS).
Long answer to understand how it works.
If we talking about built-in Key generators we can divide them in two groups: - generators which are based on sequences; - generators which are based on special tables with auto-increment column. DO will prefer first type of generators, if storage supports sequences, otherwise, DO will use generators form the second group.
Both types of generators get Keys by portions. Portion size is defined by KeyGeneratorConfiguration.CacheSize parameter. KeyGeneratorConfiguration.Seed defines how many keys DO should skip to get first Key. For example, default values of CacheSize is 128 and Seed is 0. That means first Key value will be 1 if we set Seed to 10 then first key will be 11.
When you don't define configuration for build-in generator then it will be initialized with default seed and cache size values. I suppose you didn't define special values of Seed and Cache size. When you built first domain without any data then generator returned you values which start from 1. Every time when you need next key DO checks cache and if it doesn't contain any keys DO gets another 128 keys from storage sequence (or special table) and increases current value of sequence or auto-increment column.
DO doesn't store current value of any generator. It used keys portions which provide sequence or special table. DO gets keys just by one query. So as I said in short answer, you might have moved schema and data but forgot about current value of sequences or current value and increase step value of auto-increment column in generator table.
I recommend you to define Seek and Cache size of generators (or generator) with values which prevent you from key conflicts.
P.S. If you have another misunderstandings or any questions connected with my answer, please, ask in comments and I'll respond you.
answered Jun 30 '15 at 13:12
The other exception I get is 'Entity with key 'TableName, (1)' already exists'
answered Jun 30 '15 at 07:29
it makes sense now. I didn't created the script for the sequences, also, I generated the database from a SQL Server 2012 and run the script on a SQL Server 2010, which doesn't support sequences, so even if I had got the scripts for the sequences it wouldn't work.
answered Jul 02 '15 at 19:30