Change foreign key without losing data in column

Question

I've checked google, but I haven't found a way to preserve data while changing a foreign key on a table.

I have two tables, User Events and User Sports

Table User Events
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| date         | date             | YES  |     | NULL    |                |
| description  | text             | YES  |     | NULL    |                |
| user_id      | int(10) unsigned | YES  | MUL | NULL    |                |
| sport_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| created_at   | timestamp        | YES  |     | NULL    |                |
| updated_at   | timestamp        | YES  |     | NULL    |                |
| deleted_at   | timestamp        | YES  | MUL | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

Table User Sports
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | varchar(191)     | NO   |     | NULL    |                |
| default_sport_id | int(10) unsigned | YES  | UNI | NULL    |                |
| user_id          | int(11)          | YES  |     | NULL    |                |
| created_at       | timestamp        | YES  |     | NULL    |                |
| updated_at       | timestamp        | YES  |     | NULL    |                |
| deleted_at       | timestamp        | YES  |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+

Before creating User Sports table, in table User Events I had foreign sport_id from Sports table.

Now, when I want to change that column to be foreign key from Sports to User Sports table I'm getting this message on php artisan migrate:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

The migration works if I drop the column sport_id from User Events but I don't want data loss.

Here is my migration:

public function up()
{
   Schema::table('user_events', function (Blueprint $table) {
      $table->dropForeign('event_sport_id');
   });

   Schema::table('user_events', function (Blueprint $table) {
      $table->foreign('sport_id')
            ->references('default_sport_id')
            ->on('user_sports')
            ->onDelete('set null');
   });
}

Can someone point me in the right direction? Thanks

You can disable foregin check and run this migration – sanu Nov 23 '18 at 17:21 — sanu, Nov 23 '18 at 17:21
Foreign keys are there as *safety net*. – Kyslik Nov 23 '18 at 20:02 — Kyslik, Nov 23 '18 at 20:02

score 0 · Answer 1 · answered Nov 23 '18 at 19:59

0

You must pass an array not a string to the method dropForeign: $table->dropForeign(['event_sport_id']);

answered Nov 23 '18 at 19:59

Walter Cejas

1,924
1
12
22

Change foreign key without losing data in column

1 Answers1