On delete, set id to 1

I am working on a basic blog application with Codeigniter 3.1.8 and Bootstrap 4.

NOTE: My database does use InnoDB.

There is a posts and a categories table. At the moment, the application inserts a category id into the posts table, whenever a post is created (you can not add a post before choosing a category for it), but I have set no foreign key relationship between the two tables (for ease of development) and no cascading.



Now I want to set a special cascading relationship between the two tables: whenever a category is deleted, all posts in that category should have 1 in the cat_id column.


I have created an installation process for the application: after creating a database and providing its credentials to the 

Code:

application/config/database.php

 file, you can run the 

Code:

Install

 controller which will create all the necessary tables:

Code:

class Install extends CI_Controller {
   public function __construct()
   {
       parent::__construct();
   }

   public function index(){
       // Create all the database tables if there are none
       // by redirecting to the Migrations controller
       $tables = $this->db->list_tables();
       if (count($tables) == 0) {
           redirect('migrate');
       } else {
           redirect('/');
       }
   }
}

The migrations I use to create the posts table are:

Code:

public function up(){
 $this->dbforge->add_field(array(
 'id'=>array(
   'type'=>'INT',
   'constraint' => 11,
   'unsigned' => TRUE,
   'auto_increment' => TRUE
 ),

 'author_id'=>array(
   'type'=>'INT',
   'constraint' => 11,
   'unsigned' => TRUE,
 ),

 'cat_id'=>array(
   'type'=>'INT',
   'constraint' => 11,
   'unsigned' => TRUE,
 ),

 'title'=>array(
   'type'=>'VARCHAR',
   'constraint' => 255,
 ),

 'slug'=>array(
   'type'=>'VARCHAR',
   'constraint' => 128,
   'unique' => TRUE,
 ),

 'description'=>array(
   'type'=>'VARCHAR',
   'constraint' => 255,
 ),

 'content'=>array(
   'type'=>'TEXT',
 ),

 'post_image'=>array(
   'type'=>'VARCHAR',
   'constraint' => 255,
 ),

'created_at'=>array(
   'type'=>'TIMESTAMP',
 ),

'updated_at'=>array(
   'type'=>'TIMESTAMP',
 ),

));

$this->dbforge->add_key('id', TRUE);
$this->dbforge->create_table('posts');
$this->db->query('ALTER TABLE `posts` ADD FOREIGN KEY(`cat_id`) REFERENCES 'categories'(`id`) ON DELETE SET cat_id to 1;');

}

What shall I replace the last line with (it has illustrative purposes):

Code:

$this->db->query('ALTER TABLE `posts` ADD FOREIGN KEY(`cat_id`) REFERENCES 'categories'(`id`) ON DELETE SET cat_id to 1;');

in order to get the desired result?

The last part should be:

Code:

ON DELETE CASCADE

I still would add the deleted_at field name for soft deletes, CI 4
has this field already

CI 4:
created_at
updated_at
deleted_at

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )