#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0

    Loading Foreign Keys


    So I am new to MySQL. I have been reading a lot and I understand the concept of foreign keys and primary keys, but the thing I don't get is how you actually load the foreign keys into the table. I am working with a database with 4 Million rows so I can't exactly insert manually the values. I think I have thought of a way to do it, but it seems like a very inefficient way of doing it (not to mention there could be some errors)

    So here is what I am working with. I have a very large database with several columns. I want to normalize the table. So for instance I want to create another table with address. So I take the distinct address and create a new table that has the primary key as site_id which auto increments. So now I want to use the site_id as a constraint for the original table.

    This is the way I was thinking of loading the data.. I could now use a join with the matching address' and select the address from my first table and the site_id from the address table. I could then put this into an output file, then load this file back into my originally table (only the foreign key column). After this I could drop the address columns from the original table.

    So I thought this seemed very backwards and inefficient and surely MySQL (SQL in general) has a more logical, efficient process of doing this . Any pointers?? First post, and any response is much appreciated. Thanks!
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,932
    Rep Power
    4033
    You don't really "load" foreign keys. You just describe what is a key and what it references when you create your tables. As your tables get filled with data then the foreign key constraints will be enforced helping to keep things intact.

    What you're describing is re-factoring your database, which is unrelated to foreign keys. The basic process your described (select the data, add it to the new table, copy the generated ID to the old table, drop the old column) is basically what you have to do. Depending on exactly what you need to do to accomplish the above you may be able to do it with just a few sql statements, or you may need to involve some scripting/manual work.

    For example, if you just wanted to copy the addresses out to another table 1-to-1 then something like this would work:
    Code:
    --create new table
    create table addresses (addressId int not null primary key auto_increment, address varchar(255), tmpRefId int);
    
    --copy address data over, also copy the PK of the old table for a reference
    insert into addresses  (address, tmpRefId)
    select 
       address, rowId
    from oldTable
    ;
    
    -- Add a column to the old table to hold the new ID
    alter oldTable add addressId int not null foreign key references addresses (addressId) ;
    
    -- Update the old table with the new ID by joining the new table using the PK reference copied earlier
    update oldTable
    inner join addresses ON oldTable.rowId=addresses.tmpRefId
    set
       oldTable.addressId=addresses.addressId
    ;
    
    -- Drop the tmp PK reference field
    alter table addresses drop column tmpRefId
    
    -- Drop old address field.
    alter table oldTable drop column address
    I'm more familiar with SQL Server so the syntax of my above statements may be slightly off. Consult the manual for details on the proper syntax for the above statements.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    2
    Rep Power
    0
    Thanks for your response, if you have a second to answer this follow of question I would appreciate it.

    Would this not work for a one-to-many relationship? I am certain it is a one-to-many relationship, there are many people who work at the same address

    Thanks!

    For example, if you just wanted to copy the addresses out to another table 1-to-1 then something like this would work:
    Code:
    --create new table
    create table addresses (addressId int not null primary key auto_increment, address varchar(255), tmpRefId int);
    
    --copy address data over, also copy the PK of the old table for a reference
    insert into addresses  (address, tmpRefId)
    select 
       address, rowId
    from oldTable
    ;
    
    -- Add a column to the old table to hold the new ID
    alter oldTable add addressId int not null foreign key references addresses (addressId) ;
    
    -- Update the old table with the new ID by joining the new table using the PK reference copied earlier
    update oldTable
    inner join addresses ON oldTable.rowId=addresses.tmpRefId
    set
       oldTable.addressId=addresses.addressId
    ;
    
    -- Drop the tmp PK reference field
    alter table addresses drop column tmpRefId
    
    -- Drop old address field.
    alter table oldTable drop column address
    I'm more familiar with SQL Server so the syntax of my above statements may be slightly off. Consult the manual for details on the proper syntax for the above statements.[/QUOTE]

IMN logo majestic logo threadwatch logo seochat tools logo