Results 1 to 2 of 2

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

  1. #1
    Join Date
    Apr 2009
    Location
    Sydney, Australia
    Posts
    110
    Thanks
    15
    Thanked 1 Time in 1 Post

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

    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?

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,692
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

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

    Here is a child table:
    Code:
    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:

    Code:
    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/...nstraints.html
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •