Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Unanswered: Foreign key blues

    Hi,

    I'm upgrading an access database to mssql 2005. The problem I'm having is that the previous owner of this database used an alphanumeric column to relate two of the tables together. The tables already have data in them and using the alpanumeric key isn't a good option. I still need to keep the alphanumeric data as it used from central office as an identifier of each case. Is there a way to add a numeric number to each row coinciding with each of the different alphanumeric keys and keep integrity intact? Thanks for your help, I'm not finding any answers by just searching..

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure what the problem is. Are you getting an error from the upgrade process? Performance problems? I have defined foreign keys on natural keys before, and there was no problem.

  3. #3
    Join Date
    Sep 2010
    Posts
    22
    I'm recreating the database, but don't want to use alpha-numeric keys, so i'm looking for a way to sort of replace all the alpha-numerics but also keep a copy of them in another column, no errors, I'm still designing the tables, but need a way to relate them with integers, Thanks..

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    create a cross-reference table first, first column is an int IDENTITY(1,1) second column is your alpha field

    then just insert into this c-r table selecting distinct alpha field

    finally add your new columns to the appropriate tables and then update the new int column by joining your alphas against the c-r table

  5. #5
    Join Date
    Sep 2010
    Posts
    22
    Quote Originally Posted by Gagnon View Post
    create a cross-reference table first, first column is an int IDENTITY(1,1) second column is your alpha field

    then just insert into this c-r table selecting distinct alpha field

    finally add your new columns to the appropriate tables and then update the new int column by joining your alphas against the c-r table
    Thanks Gagnon, I've never done this before, but I'm back into the project now, so I'm hoping this will do the trick!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why the cross-reference table?
    He can just add an identity column to the existing table.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StanTheMan2 View Post
    Is there a way to add a numeric number to each row coinciding with each of the different alphanumeric keys and keep integrity intact?
    Quote Originally Posted by StanTheMan2 View Post
    I'm recreating the database, but don't want to use alpha-numeric keys
    Unless there is a driver that you haven't mentioned I would say there is not a good ROI in pursuing this. For a new system then yes; retrofitting surrogate keys to a functioning existing system, no.

    However I agree with blindman; there is no need to create any extra tables to accomplish this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What is the prejudice against alphanumeric primary keys?

    Yes, index sizes are much larger, resulting in some sort of theoretical performance hit.

    But if the major tables in your system are designed around a natural alphanumeric sort order, why not use a primary key that is appropriately clustered--an alphanumeric primary key?


    I'm not saying that anyone is doing anything wrong here, I would just like to know the reason that people, out-of-hand, dismiss alphanumeric keys without providing justification (which is why I used the word "prejudice").


    Please explain.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Sep 2010
    Posts
    22
    Quote Originally Posted by PracticalProgram View Post
    What is the prejudice against alphanumeric primary keys?

    Yes, index sizes are much larger, resulting in some sort of theoretical performance hit.

    But if the major tables in your system are designed around a natural alphanumeric sort order, why not use a primary key that is appropriately clustered--an alphanumeric primary key?


    I'm not saying that anyone is doing anything wrong here, I would just like to know the reason that people, out-of-hand, dismiss alphanumeric keys without providing justification (which is why I used the word "prejudice").


    Please explain.
    My particular reason is that I'm using vb.net with controls that bind directly to the tables and fields, an alpha numeric key hasn't been an option, it just doesn't work with bound objects (unless someone knows how to) if I were using queries then maybe it would work, thanks for all the replies everyone, I'm hoping to find a solution as I've been given another database to upgrade and I here it has troubles with relationships

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    That's cool.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Sep 2010
    Posts
    22
    Quote Originally Posted by Gagnon View Post
    create a cross-reference table first, first column is an int IDENTITY(1,1) second column is your alpha field

    then just insert into this c-r table selecting distinct alpha field

    finally add your new columns to the appropriate tables and then update the new int column by joining your alphas against the c-r table
    The more I read this, the more I think it might work for me, I'm sure I'll spend some time on google figuring out the join, but hopefully, in sql server 05, there will be some kind of derived table that i can bind to, any idea's on if this is a possibility? a regular table in the db that is a product of a join? (it's been a long while since I learned some of this stuff) Thanks!

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE dbo.foo (
       NaturalKey		NVARCHAR(5)
       UNIQUE (NaturalKey)
       )
    
    INSERT INTO dbo.foo VALUES ('A101')
    INSERT INTO dbo.foo VALUES ('B102')
    INSERT INTO dbo.foo VALUES ('C103')
    INSERT INTO dbo.foo VALUES ('M113')
    INSERT INTO dbo.foo VALUES ('Z126')
    
    SELECT * FROM dbo.foo
    
    ALTER TABLE dbo.foo
       ADD id		INT			IDENTITY
    
    SELECT * FROM dbo.foo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Sep 2010
    Posts
    22
    after getting back into my project a little more, i noticed it wasn't vb.net having trouble with natural keys, it was this block of code i'm using to upload files into the database, it's made to run with integer keys and i haven't found a way to make it work with the naturals, anyways, there's less than 500 rows in the main table (those keys are ok) and most of the db has good keys, so I'm going to try and fix by manually inputting integers into new rows/columns to match up some of the foreign tables, that will take time, but it will be right, thanks for the help and support! I hope to have some good results to report back on, it's an app that runs on notebooks/sqlexpress, you can upload pictures,pdf's,doc,xls,data,etc. then come back to the office, sync up with the server, the users of the other notebooks sync up when they come in and everyone has the same data.
    Last edited by StanTheMan2; 11-22-10 at 19:16.

Posting Permissions

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