Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Scripting Lookup Lists with Sequence ID foreign keys.

    I have three tables which will contain related data: AppRole, AppRight, and AppRoleRight.

    AppRoleRight references Sequence IDs in AppRole and AppRight.

    I need to script out the data that is in these tables so that they can be populated during installation.

    What is the best method for accomplishing this? Should my script include the Sequence ID values to maintain relational integrity and then start new sequences with a higher seed value? Or should I script without the ID values and then try to reconstruct relationships based on new values? Is there a better method that I am missing?

    Thanks for help and advice.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Feb 2004
    Posts
    6
    I don't think there is going to be a "best method" either way would work.

    If you know the ids in advance and the tables were empty I would preload the tables and then build the sequences. Although I know nothing of your enivronment I think this would be the simplest approach.

    You didn't specify how large the tables are but you could leave off all the constraints and indexes until after you load the tables if that's feasible. This could give you some performance improvement depending on how much data you are loading.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Tables are small, under 100 records each.

    So when I define the sequences, I will need to set a starting value greater than the maximum existing ID value, right? What about putting a check in the function the sets the ID so that it continues incrementing until it finds the first ID not already used? Drawbacks? Performance hits? How would this handle cached sequence values?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2004
    Posts
    145
    Am I looking at your issue in too simple fashion?

    I am not sure what the issue is.

    How is the table being loaded during the installation?
    Is there a trigger on the table that updates the record?

    If there is a trigger simply disable along with disabling of foreign key relationship, if possible during the installation. Load the data then enable the trigger and foreign key relationship.

    If I am taking too simplistic approach please provide more information.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But when I re-enable the trigger, won't the Sequence generate IDs that were already loaded during the install? I mean, if my Sequence starts with 1 and increments by 1, but I pre-load values 1-4 into the table during the install, won't I get errors the first time somebody trys to add a new record?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Oct 2004
    Posts
    145
    I was assuming you were alredy using the sequence and it lined up with the current data. Hence simply disable the trigger will ensure data and sequence remains in synch.

    If the sequence is new, you can always created it with start value = max id + 1.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for advice, MonolithicX and JimYoo!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I have three tables which will contain related data: AppRole, AppRight, and AppRoleRight.
    Probably too late to bring this up, but what the hell kind of table names are APPROLE, APPRIGHT or APPROLERIGHT? Using a known language can provide a level of self-documentation when naming objects. (Apologies if those names are meaningful in Arapaho or Inuit or something and I'm just missing it.)
    Last edited by WilliamR; 12-10-04 at 20:37.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    APPROLE: Application Roles
    APPRIGHT: Application Rights
    APPROLERIGHTS: Implements many-to-many relationship between APPROLE and APPRIGHT.

    My original table names were:
    THETABLEWHATHOLDSALLTHETYPESOFLOGINS
    THETABLEWHATHOLDSALLTHESTUFFANYLOGINKINDO
    and
    ANNUDERTABLEWHATHOLDSALLTHESTUFFDOABLEBYEACHLOGIN
    ...but after writing a few lines of code the Word Wrap feature on my text editor overheated and won't work anymore. So sad...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •