Post reply

Identity column reseed cause problem with other database

  • November 9, 2012 at 3:19 pm

    #393578

    We have a data warehouse team that uses the identity column (surrogate key) as primary keys for many cubes tables for the database I will call it Database A.

    We also have another database B that uses tables imported from the data warehouse.

    We give the column name something like DatabaseA_CourseSection_SK

    Recently the data warehouse told us they reseed the courseSection table in database A.

    So the primary key value changed.

    But database B still use the older values across several tables.

    My question is:

    We initailly thought the PK value (surrogate key) should be always consistent in data warehouse, and will be reliable, never should be changed, I don't know why they reseed it?

    Is it a common thing to reseed a table in data warehouse or any othe reasons?

    What is the best approach in database B to use to prevent things like this happen and mess up the data integrity?

    P.s We didn't use natural key is because we have to use several combined columns. So we chose to use identity column. Please do not start the discussion about using natural key for this case if possible.

    thanks,

    Thanks

  • November 9, 2012 at 3:48 pm

    #1557835

    sqlfriends (11/9/2012)

    We have a data warehouse team that uses the identity column (surrogate key) as primary keys for many cubes tables for the database I will call it Database A.

    We also have another database B that uses tables imported from the data warehouse.

    We give the column name something like DatabaseA_CourseSection_SK

    Recently the data warehouse told us they reseed the courseSection table in database A.

    So the primary key value changed.

    But database B still use the older values across several tables.

    My question is:

    We initailly thought the PK value (surrogate key) should be always consistent in data warehouse, and will be reliable, never should be changed, I don't know why they reseed it?

    Is it a common thing to reseed a table in data warehouse or any othe reasons?

    What is the best approach in database B to use to prevent things like this happen and mess up the data integrity?

    P.s We didn't use natural key is because we have to use several combined columns. So we chose to use identity column. Please do not start the discussion about using natural key for this case if possible.

    thanks,

    Thanks

    I'm all in favor of using IDENTITY columns as PK's so no argument there from me.

    That notwithstanding, is there a "natural key" on each table that you could use to identify the rows between the two databases? If not, this is going to be a real bugger to fix (if it can be fixed).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 9, 2012 at 3:52 pm

    #1557837

    Thanks Jeff.

    We have fixed the issue by using the natural keys of their tables to join database B tables, and in database B we create its own Surrogate key to relate tables in the databases.

    But my question is :

    We initailly thought the PK value (surrogate key) should be always consistent in data warehouse, and will be reliable, never should be changed, I don't know why they reseed it?

    Is it a common thing to reseed a table in data warehouse or dbs or any othe reasons?

  • November 11, 2012 at 9:13 am

    #1557962

    sqlfriends (11/9/2012)

    Thanks Jeff.

    We have fixed the issue by using the natural keys of their tables to join database B tables, and in database B we create its own Surrogate key to relate tables in the databases.

    But my question is :

    We initailly thought the PK value (surrogate key) should be always consistent in data warehouse, and will be reliable, never should be changed, I don't know why they reseed it?

    Is it a common thing to reseed a table in data warehouse or dbs or any othe reasons?

    There are those that think that an IDENTITY column must always be contiguous and not have any gaps whatsoever. That's a huge mistake on their part especially if they do a RESEED without understanding the impact everywhere else. Surrogate Keys should never be changed unless you intend to restate the key in every table that uses the key. It's a whole lot of work just to get rid of gaps and almost always totally unnecessary. The PK, surrogate or not, should almost never change. The only time that I'd consider such a change is if two companies merged. Even then, I'd try to find a way around that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 12, 2012 at 2:01 am

    #1558054

    sqlfriends (11/9/2012)

    Is it a common thing to reseed a table in data warehouse or dbs or any othe reasons?

    this is/should not be a common practice to reseed the values now , all the child tables or other related table have lost their references and fixing that will be a problem

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • November 16, 2012 at 1:59 pm

    #1559941

    I dont know this to be common place. In fact I know not to expect it. With that being said it may be possible (just to prevent this scenario again in the future if the company will keep doing this) to have a table in the original source database where you can post a lookup table with columns PK_value and the corresponding SK_value. Have the PK_value column actually be a FK to the table where the data was reseeded and allow cascading updates. You can use this table for the foundation in your warehouse ETL from the operational database. I hope this makes sense. but you really shouldn't have to do this.

    Mark

    ----------------------------------------------------

  • November 16, 2012 at 4:58 pm

    #1559960

    No, normally you never change an identity/key value in a warehouse once it's been assigned. I can't imagine what would require that to be done.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • November 16, 2012 at 4:59 pm

    #1559962

    No, normally you never change an identity/key value in a warehouse once it's been assigned. I can't imagine anything that would require that to be done, so it's very odd that they would feel the need to do that.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • November 17, 2012 at 12:10 pm

    #1560035

    Thanks, that is what I think too.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply