On 2016/11/21 9:57 PM, Vikas Aditya wrote:
Hi everyone,
I have a DB migration question. I have a table called "employees" and it has a UNIQUE constraint on
"employee_email". With some new features being requested, I need to relax the constraint and have a new
constraint on "employee_email" + "employee_number". Since SQLite ALTER table command can't modify
an existing constraint, I have thought about creating a new table with the new constraint and then copying everything
from old table to new table and afterwards dumping the old table. It does work but my question is what is the fastest
and most memory efficient way to copy data from old table to new table?
Firstly, are you sure about this constraint relaxing? Basically by
altering the constraint you are saying (in standard English) that you
believe there CAN be an entry where two people with different
Employee-numbers could have the same e-mail address, or, put another
way, you believe that there could be two different people with different
e-mail addresses which could have the same employee number... Which
doesn't sound like any company I know of - usually any employee within a
company must have both a unique e-mail address and unique employee
number. (It might be different but valid for you, I'm just suggesting to
double-check the premise for that decision).
Currently we are using:
sql = "INSERT INTO {} SELECT * FROM {}".format(totable, fromtable)
cur.execute(sql)
So, a single line statement can copy from old to new. But will this work fine
even if I have 100s of thousands of records in old table? Or will this load
pretty much entire data into memory and then copy to new?
Looking for other optimal ways to copy data from one table to another if this
is not optimal.
Your example would work great - SQLite is very good at doing this and it
would use memory correctly.
It is of course unlikely a relaxed constraint would fail when copying
from a table with a more strict set, but it is possible, and it is
possible something else might go wrong, so just to be sure, start a
transaction before renaming the old table, making the new table and
copying the data (you can do all of that in one set of instructions),
then commit or rollback based on the result from the cur.execute().
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
