Can one record update several other columns in the same table using relationships

[sniperman]

Just a quick heads up.

I've had an MS Access database for a while now for project management and have started to reorganize the tables to make it a true object relational database.

I currently use a Project ID unique key with no duplicates.

I also use the combo box and table/query to choose records for the main table to avoid duplication.

The question is, because the information is pulled from several other tables to populate one row and those records all have a unique Project ID key associated to them, I would like to be able to enter the Project ID (which is also a table/query selection) and have a query locate all the associated links in the rest of the columns in that table, rather than have to manually enter them in again and again.

Is there a way for this to happen?

[james438]

You will need to use FOREIGN KEYS. This is a more complex aspect of MySQL.

Here is a child table:

CREATE TABLE notes (

id INT AUTO_INCREMENT,
addressee_id INT,
day DATE,
note TEXT,

PRIMARY KEY(id),
FOREIGN KEY(addressee_id)
REFERENCES people(id)
ON DELETE CASCADE
) ENGINE=INNODB;

In this table there is a FOREIGN KEY, which means that this table is a child table. The REFERENCES says what the parent table and column is that the FOREIGN KEY relates to. ON DELETE CASCADE is the action to take when the REFERENCE KEY is altered in some way. In this case if people.id is deleted then the corresponding row notes.addressee_id is deleted as well. The FOREIGN KEY is declared in the child table. REFERENCES is the parent column that the FOREIGN KEY is matched against. For example, if the parent table deletes a row that is referenced by this child table then the child table notes will delete the row that matches the one deleted by the parent row. INNODB must be selected for each table that is involved with the FOREIGN KEY.

Here is the Parent Table:


CREATE TABLE people (
id INT AUTO_INCREMENT,
name VARCHAR(128),
default_position_id INT,

PRIMARY KEY(id))
ENGINE=INNODB;

You can read more about it here: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html