• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
On delete, set id to 1

#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.


[Image: uGsIX.png]

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?
Reply

#2
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 )
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.