Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I've created a platform about three years ago when I had less knowledge about how to create efficient databases. I have now re-written the platform into Laravel but I come across a problem.
In the old database I made use of VARCHAR as a primary key in my table Customer. In other tables this primary key was used as foreign key. In my new migration I have created an AI as a primary key, and another column to store the username.
Schema::create('customers', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('username');
$table->timestamps();
});
I need to import the data from the other table (where the varchar was used as a foreignkey) into the Laravel database. However I need to replace this old foreign key with the new primary key. What is an easy way of doing this? It's a large table with atleast 3 million+ rows .
I made a small script to open the old table in a CSV file. Inside a loop I go trough every line and search for the Customer (where username = old foreign key) by the old foreign key and then replaces it with the new primary key of the Customer table. But this is very time consuming.
I would use an extra column in the customers table where I store the old primary key and the same for the other tables where you use it as foreing key. Then I can run something like
INSERT INTO table2
SELECT * FROM table1
And then do an update of the foreign key Something like
UPDATE table set foreign key = (select id from table2, table where table.old_id = table2.old_id
And lastly drop the old_id columns
I think the approach you're doing works, but instead of doing the import from a CSV I recommend you to do it changing db connections.
I suppose you have already the rows in your database with proper primary key and the old varchar field as a field for momentary reference.
In a database migration or an artisan command you could
Model::chunk(100, function($model) {
$model->reference_key = Customer::where('old_varchar', $model->reference_key)->first()->id;
});
That process is expensive also but better than importing from CSV, I think it should be a database only approach which will be even better than this.
Haven't thought about these ideas before. I am definitly going to try them out tomorrow and see how it works. The approach I am currently using is working but very time consuming, like 24+ hours.
Please or to participate in this conversation.