How to add FOREIGN KEY constraint when creating a table?

[eluser]kadishmal[/eluser]
I am trying to create a table which has a foreign key reference to another table. The table is being created by CodeIgnier. However, in

Code:

$this->dbforge->add_key()

I cannot see how would I indicate that this key should be a foreign key which references another key. How would I do that?

[eluser]Bhashkar Yadav[/eluser]

Code:

$this->dbforge->add_key('KEY_ID', TRUE);

[eluser]kadishmal[/eluser]
[quote author="Bhashkar" date="1328943041"]

Code:

$this->dbforge->add_key('KEY_ID', TRUE);

[/quote]

That's the private key, not foreign key.

[eluser]InsiteFX[/eluser]
CodeIgniter User Guide - Custom Function Calls

I think it can also be done using a query I'll see if I can get an example for you.

Update:

Examples:

Code:

// Create a database table.
$this->db->query('
CREATE TABLE IF NOT EXISTS `role` (
  `id`          int(11)      UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        varchar(255)          NOT NULL UNIQUE,
  `description` text                           DEFAULT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;'
);

// These will work also if your table is already created!

// Example: But not needed here
// $this->db->protect_identifiers('role');

// First we add our column that will be the Foreign Key.
$this->db->query('ALTER TABLE `role` ADD COLUMN `parent` int(11) UNSIGNED DEFAULT NULL;');

// Next we add an index. Why? because mysql says so and FKs rely on indexes
$this->db->query('ALTER TABLE `role` ADD INDEX `parent` (`parent`);');

// Now we add our constraint.
$this->db->query('ALTER TABLE `role` ADD CONSTRAINT `parent` FOREIGN KEY(`parent`) REFERENCES `role`(`id`) ON DELETE SET NULL ON UPDATE SET NULL;');

// Add foreign key
$this->db->query('ALTER TABLE `role` ADD FOREIGN KEY(`id`) REFERENCES settings(`id`) ON DELETE CASCADE ON UPDATE CASCADE;');

All have been tested and working here!

You could also make this into a method and pass the parameters into it...

[eluser]kadishmal[/eluser]
Thank you, InsiteFX! That's a great example!

I understand that anything allowed by the DBMS can be executed through an SQL query directly in CI. What I was looking for is the CI implementation of Foreign keys using functions just like add_key().

The idea I was looking if FK was implemented is that I wanted to commit FK implementation together with an updated CUBRID Database Driver. If you happen to know why it hasn't been implemented so far, please let me know. I just keep thinking that maybe there is a reason why CI doesn't have FK support.

[eluser]mabasic[/eluser]
Hi all,

I was inspired by the posts here and frustrated by codeigniter that it does not have abillity to add foreign keys using DBforge so I forked the repo and added it as a new feature. It only requires little code modifications to system folder.

You can find the code here: https://github.com/DamexHr/CodeIgniter/t...reign_key/

Usage is simple

Code:

$this->dbforge->add_foreign_key(array('field' => 'language_id',
               'foreign_table' => 'languages',
               'foreign_field' => 'id'));

[eluser]Aken[/eluser]
Your code will only work for the mysqli DB driver. It'll effectively break DB forge on any other driver, because you've changed the parameters in the call to _create_table(), but not the actual parameters in each driver.

If you have suggestions for foreign keys in the forge, you should contribute to this pull request's discussion to encourage activity on the subject, and work towards a solution that supports as many DBs as possible.

[eluser]mabasic[/eluser]
[quote author="Aken" date="1366442675"]Your code will only work for the mysqli DB driver. It'll effectively break DB forge on any other driver, because you've changed the parameters in the call to _create_table(), but not the actual parameters in each driver.

If you have suggestions for foreign keys in the forge, you should contribute to this pull request's discussion to encourage activity on the subject, and work towards a solution that supports as many DBs as possible.[/quote]

Yes, I have forgotton to mention that this only works with mysqli DB driver, as it is the one that I am using.
I have seen that discussion and tried the code, but with no success. If you want to have foreign keys support on different DB drivers, then You should implement that, I have no desire to work on something that I don't need.

The reason why i didn't do a pull request is because I have branched the stable branch not the develop branch as stated in the codeigniter guidelines.

This solution requires only a few changes to two files in the system folder and it is very simple to use.

I hope that it will help someone who stumbles upon this post as me.