Foreign Key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
qadeer_ahmad
Forum Newbie
Posts: 11
Joined: Thu Aug 14, 2008 3:17 am

Foreign Key

Post by qadeer_ahmad »

I want to handle foreign key using php script.
Can i handle this run time using php script?

Thanks
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Foreign Key

Post by alex.barylski »

Sure instead of relying on RDBMS constraints you implement those constraints in your business logic, which I personally prefer anyways. You might do that in a DAL as well, or wherever suits your needs I suppose.

The answer, yes you can do it in PHP it's just more work.

Cheers,
Alex
qadeer_ahmad
Forum Newbie
Posts: 11
Joined: Thu Aug 14, 2008 3:17 am

Re: Foreign Key

Post by qadeer_ahmad »

Would you please give me any example how to handle these?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Foreign Key

Post by alex.barylski »

I can't really, without giving a specific example.

They are simple constraints. Lets say you have two tables:

Code: Select all

users:
pkid, fname, lname, address
 
numbers:
pkid, user, type, value
users has a one to many relationship with numbers, that is, every user can have zero or more numbers associated with to it, via numbers.user.

If you have a user with 5 numbers, when you delete the user, all numbers associated with it should also be deleted.

You might have two models:

- Users
- Numbers

Users model would have a deleteUser() method. Inside this method you would have to call a deleteNumber() method iteratively to remove each associated number:

Code: Select all

function deleteUser($id)
{
  $model = new Numbers();
  $numbers = $this->selectAllNumbers($id);
  foreach($numbers as $number){
    $model->deleteNumber($number['id']);
  }
}
Not tested but should demonstrate the point of what I sam saying

Cheers,
Alex
qadeer_ahmad
Forum Newbie
Posts: 11
Joined: Thu Aug 14, 2008 3:17 am

Re: Foreign Key

Post by qadeer_ahmad »

Thanks for help but i need when i delete a key record then all record on other table when are bind a foreign key automatically deleted so that every thing handle under the database another issue if i set foreign key locally using mysql youg or any other system it is hard to apply on phpmyadmin so i am asking if i got any script to deploying foreign key using php script or some thing else.

Thanks again for taking interest and helping me.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Foreign Key

Post by josh »

qadeer_ahmad wrote:Thanks for help but i need when i delete a key record then all record on other table when are bind a foreign key automatically deleted so that every thing handle under the database another issue if i set foreign key locally using mysql youg or any other system it is hard to apply on phpmyadmin so i am asking if i got any script to deploying foreign key using php script or some thing else.

Thanks again for taking interest and helping me.
On delete = cascade. Set up innodb foreign key.

You could implement it in the business logic, but the foreign key makes it so if your script crashes (a failed query) the transaction would be rolled back/not committed.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Foreign Key

Post by Benjamin »

:arrow: Moved to Databases
Post Reply