Hi Ryan,
Thanks for the transaction suggestion. We will do that.
I provided a simplistic example for constraints, but I think I have an answer
now.
Thanks,
Vikas
On Nov 21, 2016, at 12:24 PM, R Smith <rsm...@rsweb.co.za> wrote:
>
>
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
