[macaroni]

I'm trying to understand database in general and I'm reading an introductory book on databases and they mention on relation between one table to another i.e. one to many, one to one, many to many.

They show one table where one field exist in another table. i.e. the sales_agent_id in the agent table exist in the client table, where a client is assigned an agent.

My question is, in databases i.e. oracle, mysql, when an update is made to the first table (agent table) does it automatically change the field as well in the second table(client table). i.e suppose I change the sales agent id in the agent table does it change the agent id assigned to the client.

thanks..

[mrburri]

With my experience with PHP and MySQL the answer is no. Because the table's in MySQL cannot auto update the ID value in a second table if the ID value from table one gets updated.

But I would suspect with more commercial Dbases their must be some in build way to keep data accurate across tables.

To update two tables you would have to write two SQL statements to update the their respective tables.

Something like this:

function store_details ($id) {

$sql_account = "INSERT INTO account VALUES ( 'id' )" or die(mysql_error());
$sql_memberdetail = "INSERT INTO memberdetail VALUES ( 'id' )" or die(mysql_error());

$result_account = mysql_query($sql_account);
$result_memberdetail = mysql_query($sql_memberdetail);


if(!$result_account || !$result_memberdetail) {
return false;
}
else {
return true;
}
}

Their is also a PHP function called LAST_INSERT_ID() that will insert the last ID inserted into another table.

This example is in PHP but the SQL is still the same for JSP or god forbid ASP.

Hope this helps.

[DaGuy]

It is my understanding (someone jump in if I'm off) that the integrity constraints (the ones used to keep tables consistent) are properties of the tables themselves. This means that, if you try to insert/update without manually updating the linked tables, you will get an error.

What you seem to be after, is something called "triggers" these are essentially procedures that are started by the occurance of certain well-defined events i.e., updating one table can fire the trigger that updates a different table.

Many databases support triggers i.e., Oracle, Postgres and I'm not sure if the MySQL does.

[itai195]

The sales_agent_id is what you would call a primary key... In general, you don't want to write code that modifies primary keys, for the obvious reason that you state. Other tables can depend on the integrity/state of those keys.

Like the above poster said, some databases do support triggers which can implement such functionality (eg propagating changes to other tables).

[Diggory Laycock]

this book is good:

http://www.amazon.co.uk/exec/obidos/...069972-1274257

[Arkham_c]

Generally you don't want to force updates of the other tables. Unless you are editing the foreign keys (keys shared between tables), this is not desirable. Example:

Code:
CREATE TABLE Users (
                    user_id VARCHAR(10) primary key not null,
                    user_name VARCHAR(255),
                    phone VARCHAR(10),
                    email VARCHAR(255)
                   );

CREATE TABLE Friends (
                      user_id VARCHAR(10) not null,
                      friend_id VARCHAR(10) not null
                     );

In this case, we have a foreign-key many-many relationship each userid can have more than one friend, and each friend can have more than one userid. Adding or deleting a row in the second table should not necessarily require a change in the first. This is a programming problem, not a database problem.