migrating from mysql

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

migrating from mysql

Frederick Grim-2
Howdy all,
        So I am wondering if anyone has run into this problem.  I am trying  
to move from mysql to sqlite and the unique constraint from primary  
keys is not allowing me to insert my sql that I dumped from the mysql  
database.  I have a bunch of join tables so I really can't set the id  
field to null and let sqlite renumber the keys.  I guess I could dump  
each table into a separate .csv file or something and use the import  
pragma but I am hoping there is just so pragma I can give it for it  
not to enforce key constraints through the import.  Can I do this?

        Fred

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: migrating from mysql

Mark Richards-3
Frederick Grim wrote:
> Howdy all,
>     So I am wondering if anyone has run into this problem.  I am trying
> to move from mysql to sqlite and the unique constraint from primary keys
> is not allowing me to insert my sql that I dumped from the mysql
> database.  I have a bunch of join tables so I really can't set the id
> field to null and let sqlite renumber the keys.  I guess I could dump
> each table into a separate .csv file or something and use the import
> pragma but I am hoping there is just so pragma I can give it for it not
> to enforce key constraints through the import.  Can I do this?
I have run into this, or it into me, but not cross-server and not with
the complexity of joined tables.

I think the principles are the same.

My guess is that you have existing records in the target table with keys
that are identical to those you are trying to import (you said "insert
my sql that I dumped from the mysql database").

Since you rely on these keys to join other tables, I can imagine that
there's a possibility that anything you do will mangle the associations
that exist.

Someone here might have a quick and easy way and I'd love to read it.

If you can easily re-build the joins, one option would be to use an
INSERT INTO [table]( {with a field list that excludes your key field})
VALUES(..)  or UPDATE [table] SET {with a field list that excludes your
key field} as applicable.  A little more work than just importing.

Another option is to build a process that reads the data from the source
and populates it according to the existing constraints.  You might also
consider nulling all your master table source record key fields and
import these (new keys will be assigned), and then use a process to
re-build the joins with the other tables.

If it were my project, I'd see a few hours of programming in the near term.

Knowing me, I'd also back up everything.

/m


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------