locked
Local CE 3.5 database - index's, primary keys RRS feed

  • Question

  • I am using the Sync Framework to sync several remote servers up to some local tables using the SqlCeClientSyncProvider. Everything is working fine, however I am noticing some strange design behavior with my local tables.

    Basically, the index's on my remote tables (SQL Server 2005) are being pulled down and those columns are becoming the primary keys' on the tables in my local database. For example:

    Here is a sample table on my remote server:

    IdentityColumn (PK, not null)
    Fname (varchar(25), not null) - Non Unique, non-clustered index
    Lname
    (varchar(25), not null) - Non Unique, non-clustered index
    Address (varchar(25), not null) - Non Unique, non-clustered index
    City (varchar(25), not null)
    State (varchar(2), not null)

    Now, If I run my sync to create my local CE database, the local table will be created like this:

    Fname (PK,varchar(25), not null)
    Lname
    (PK,varchar(25), not null)
    Address (PK, varchar(25), not null)
    IdentityColumn (not null)
    City (varchar(25), not null)
    State (varchar(2), not null)

    This is happening consistently among all the 12 or so tables I'm syncing. The server indexes are becoming the keys locally. Any idea why?
    • Moved by Hengzhe Li Friday, April 22, 2011 7:44 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, December 1, 2008 4:20 PM

All replies

  • I think I've found a better way to do this. Instead of relying on my very first sync to create the local schema, I am now creating the local tables ahead of time with my pre-defined schema and using the TableCreationOption.UseExistingTableOrFail instead of TableCreationOption.DropExistingOrCreateNewTable.
    Monday, December 1, 2008 9:38 PM
  • Hi Marcus,

     

    using predefined schema on local db is a way to custermize the schema on the SqlCe db and can surely workaround issues for schema creation.

     

    However, the table schema you described here should be created as the PK as the same on the Sqlserver. I did a quick try and confirmed this. below is the table I used. I checked the local SqlCE db and can see the PK is on col1.

     

    create database testdb

    go

    use testdb

    go

    create table test ( col1 int identity (1,1) primary key, col2 int, col3 int )

    go

    create index idx_1 on test (col2)

    go

    create index idx_2 on test (col3)

    go

     

    can you double check your indexes on the sql server to ensure the index is not clustered unique index?

     

    thanks

    Yunwen

    Sunday, December 7, 2008 6:51 PM
    Moderator