Foreign Key constraint check

[eluser]Jagar[/eluser]
Is there anyway to detect when there is a foreign key problem?

For example deleting a row from a table when there is a foreign key at another table.

I don't know if I explained it right, but let me know I can make it clear.

Thanks

[eluser]tomcode[/eluser]
Not in MySQL, it is up to You. You can work with naming conventions for the foreign keys, example :

1. all primary indexes get the name 'id'.
2. all foreign keys use 'table_name' + '_id'

Another indice, but not determining is that for foreign keys exist probably an index.

[eluser]Jagar[/eluser]
Let me make an example:

Table
[Students]
student_name varchar
FK_program_id int

table [Program]
Program_id int
Program_name varchar

Now...

[Program]
Program_id Program_Name
1 Software Engineering
2 Networking
3 Web Design

[Student]
Student_name FK_program_id
John 1
James 3
Adam 2

Now right now if I go and delete the program_id 2, CI will throw an error and saying database error, and saying that you cannot delete that id because it's a foreign key of another table.

How can I detect such error, so I can tell the user instead of CI database error.

Thanks

[eluser]tomcode[/eluser]
I got Your question wrong, I thought You're manipulate the tables, now I see that You're talking about data.

Can You post Your query ?

[eluser]Jagar[/eluser]
I use the Active Record Class query, by simply doing:

Code:

$this->db->where("program_id",$id);
$this->db->delete("program");

and that's where it throws the error about foreign violation.

Thanks

[eluser]tomcode[/eluser]
I just looked it up, in fact You can have foreign key structures by using InnoDB tables.

I did not know that, guess if You want to change the data on one of the so linked tables You need to change the other, too.

Apparently it's a parent / child relation and You have to change the parent first.

Sorry to be not more helpful.

[eluser]Jagar[/eluser]
Only InnoDb engine supports foreign keys, and that's why I changed it from MyIsam to innodb.

Everything is working fine, just want to know how to detect and display the error with foreign key on delete, rather than display the CI error like follow:

Code:

A Database Error Occurred

Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails

Thanks for trying appreciate!

[eluser]Jagar[/eluser]
I guess there is no solution to this, so this is what I'm going to do now.

Before deleting a row from a table that is a foreign key of another table, I will check all the tables, and see if the id exist on them, and return true or false.

If there's a better way to do this and efficient way, please let me know.

Thanks

[eluser]ch5i[/eluser]
bump.... anyone?

[eluser]danmontgomery[/eluser]
http://dev.mysql.com/doc/refman/5.1/en/i...aints.html

CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;