How to add foreign key in Codeigniter?

by muriel.schmidt , in category: PHP Frameworks , 2 years ago

How to add foreign key in Codeigniter?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

Member

by domenico , 2 years ago

@muriel.schmidt 

In Codeigniter, you can add a foreign key constraint to a database table using the $this->db->query() method in your model. Here is an example of how you can do it:

1
$this->db->query("ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id)");


In this example, the orders table is being modified to add a foreign key constraint on the customer_id column, which references the id column in the customers table.


You can also use the $this->db->query() method to drop a foreign key constraint, like this:

1
$this->db->query("ALTER TABLE orders DROP FOREIGN KEY customer_id_foreign");


Keep in mind that you should always be careful when modifying your database schema, as it can have unintended consequences if not done properly. It's always a good idea to make a backup of your database before making any changes.

Member

by dana , a year ago

@muriel.schmidt 

To add a foreign key in CodeIgniter, you need to follow these steps:

  1. Open the migration file for the table where you want to add the foreign key. Migration files can be found in the application/database/migrations/ directory.
  2. Use the add_foreign_key() method provided by CodeIgniter's Schema builder class ($this->dbforge).
  3. Pass the necessary parameters to the add_foreign_key() method, including the name of the foreign key, the name of the table where the foreign key will be added, the name of the column in the current table that will be the foreign key, the name of the table that the foreign key references, the name of the column in the referenced table, and any additional options such as ON DELETE or ON UPDATE actions.


Here's an example of adding a foreign key in a migration file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
class Add_foreign_key_to_table extends CI_Migration
{
    public function up()
    {
        $this->dbforge->add_foreign_key(
            'fk_name', // Name of the foreign key constraint
            'table_name', // Name of the current table
            'column_name', // Name of the column in current table that will be the foreign key
            'referenced_table_name', // Name of the referenced table
            'referenced_column_name', // Name of the column in the referenced table
            'RESTRICT', // Additional options, if any
            'CASCADE'
        );
    }

    public function down()
    {
        $this->dbforge->drop_foreign_key('table_name', 'fk_name');
    }
}


Once you have added the foreign key in the migration file, run the migration using CodeIgniter's migration command to apply the changes to the database.


Note: Make sure that you have enabled the database migration feature in your CodeIgniter configuration and have set up the necessary database credentials.