Post reply

Foreign Key Heirarchy

  • August 8, 2017 at 11:57 am

    #1954523

    funbi - Tuesday, August 8, 2017 11:56 AM

    It's easier than laboriously deleting data in the correct order anyway.

    Unless you have a ready-made script which generates the code for you.

  • August 8, 2017 at 12:04 pm

    #1954526

    Phil Parkin - Tuesday, August 8, 2017 11:57 AM

    funbi - Tuesday, August 8, 2017 11:56 AM

    It's easier than laboriously deleting data in the correct order anyway.

    Unless you have a ready-made script which generates the code for you.

    But if you don't you only have to run two queries before and after 🙂

  • August 8, 2017 at 12:38 pm

    #1954530

    funbi - Tuesday, August 8, 2017 11:56 AM

    Jeff Moden - Tuesday, August 8, 2017 11:42 AM

    funbi - Tuesday, August 8, 2017 7:12 AM

    You could also disable all your constraints and then re-enable them afterwards

    -- disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    -- do whatever you need to do...

    -- enable all constraints
    exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    What does that buy you?  If you make a mistake, which the constraints will help prevent, you may end up with a system where the constraints cannot be re-enabled.

    I've done this before when taking a data dump from one environment to another, normally for testing purposes. If the OP has rights to (and needs to) delete all the data I'm sure they can run a compare to ensure the keys are synched up and correct them if not. It's easier than laboriously deleting data in the correct order anyway.

    Listen to what you're saying.  If you want to delete ALL the data then don't disable the keys... drop them, truncate the tables, and then rebuild the keys.  You also say "to ensure the keys are synced up and correct them if not".  That's a whole lot more tedious and laborious than just doing it right the first time even if you only have to do it once.

    --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)

  • August 8, 2017 at 12:45 pm

    #1954531

    funbi - Tuesday, August 8, 2017 12:04 PM

    Phil Parkin - Tuesday, August 8, 2017 11:57 AM

    funbi - Tuesday, August 8, 2017 11:56 AM

    It's easier than laboriously deleting data in the correct order anyway.

    Unless you have a ready-made script which generates the code for you.

    But if you don't you only have to run two queries before and after 🙂

    Heh... yeah... let's bank on nothing going wrong.  Get ready to implement "Plan B". 😉

    --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)

  • August 8, 2017 at 1:02 pm

    #1954532

    Jeff Moden - Tuesday, August 8, 2017 12:38 PM

    funbi - Tuesday, August 8, 2017 11:56 AM

    Jeff Moden - Tuesday, August 8, 2017 11:42 AM

    funbi - Tuesday, August 8, 2017 7:12 AM

    You could also disable all your constraints and then re-enable them afterwards

    -- disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    -- do whatever you need to do...

    -- enable all constraints
    exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    What does that buy you?  If you make a mistake, which the constraints will help prevent, you may end up with a system where the constraints cannot be re-enabled.

    I've done this before when taking a data dump from one environment to another, normally for testing purposes. If the OP has rights to (and needs to) delete all the data I'm sure they can run a compare to ensure the keys are synched up and correct them if not. It's easier than laboriously deleting data in the correct order anyway.

    Listen to what you're saying.  If you want to delete ALL the data then don't disable the keys... drop them, truncate the tables, and then rebuild the keys.  You also say "to ensure the keys are synced up and correct them if not".  That's a whole lot more tedious and laborious than just doing it right the first time even if you only have to do it once.

    As I was saying, this method has helped me in the past when moving data between environments. The DB keys were the same. In the hypothetical situation where they were not then it wouldn't have been too difficult to make the relevant changes. We are obviously not going to agree on this point.

  • August 8, 2017 at 2:18 pm

    #1954539

    funbi - Tuesday, August 8, 2017 1:02 PM

    Jeff Moden - Tuesday, August 8, 2017 12:38 PM

    funbi - Tuesday, August 8, 2017 11:56 AM

    Jeff Moden - Tuesday, August 8, 2017 11:42 AM

    funbi - Tuesday, August 8, 2017 7:12 AM

    You could also disable all your constraints and then re-enable them afterwards

    -- disable all constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    -- do whatever you need to do...

    -- enable all constraints
    exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

    What does that buy you?  If you make a mistake, which the constraints will help prevent, you may end up with a system where the constraints cannot be re-enabled.

    I've done this before when taking a data dump from one environment to another, normally for testing purposes. If the OP has rights to (and needs to) delete all the data I'm sure they can run a compare to ensure the keys are synched up and correct them if not. It's easier than laboriously deleting data in the correct order anyway.

    Listen to what you're saying.  If you want to delete ALL the data then don't disable the keys... drop them, truncate the tables, and then rebuild the keys.  You also say "to ensure the keys are synced up and correct them if not".  That's a whole lot more tedious and laborious than just doing it right the first time even if you only have to do it once.

    As I was saying, this method has helped me in the past when moving data between environments. The DB keys were the same. In the hypothetical situation where they were not then it wouldn't have been too difficult to make the relevant changes. We are obviously not going to agree on this point.

    Correct!. 😉

    --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)

Viewing 6 posts - 16 through 21 (of 21 total)

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