Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've designed a mysql database made of several independent/unrelated tables to use in my office. Different tables hold information about clients (names, addresses etc), visits to the office (dates, reason etc) or other information like comments (related to a specific visit). When a known client enters the office I can retrieve all the relevant data, insert the new visit data and possible comments about the visit. I need to store visits in a specific order (1st visit 1st, etc). Sometimes I forget to store a visit and I have to enter the data later. The order of the visits gets messed up and I manually (update commands) correct it by changing the visits' autoincrementing primary key number (which represents the order). This way I get the right order of visits but comments (related to the changed primary key) cannot be retrieved. Is there any way to make sure that changing the primary key would make the necessary changes to the rest of the database, so that everything would work as before the changes? Do relational 1:many or other implementations accomplish that? And how? Please note that I'm an amateur, self-taugth "database designer".

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

First, yes it is possible to make sure changing a column in one table gets updated in all related tables. The concept is defined as a Foreign Key. In MySQL, the tables must be using the InnoDB storage engine. Here is the link to the 5.5 documentation on implementing foreign keys.

The basic gist is your child table (the table that relies on a foreign table's column value) needs to have this done to it:

ALTER TABLE bar ADD CONSTRAINT fk_foo FOREIGN KEY foo_id 
 REFERENCES foo (foo_id) ON DELETE CASCADE ON UPDATE CASCADE

Where foo_id exists in both the child table bar and the parent table foo. The foo_id column must be defined exactly the same in both tables (same size, type and unsigned/signed status).

Secondly, I would highly suggest not basing your order of visits on the auto-increment value. Auto-increment columns should have no meaning to your data.

If you have a date column, you should be able to get the order based on that column + the visitor, even if you add it manually at a later date. This would remove the 'need' of updating the autoincrement value.

share|improve this answer
    
Thanks for the answer. Please make this as clear as you can to me: if I have my client Mr XX, who visits the office on June 13, 2013 and a comment is stored for him (referenced by the visit number), will the comment be still retrievable if I change the order of the visits (and the visit number - without changing the value stored on the comments table) ? –  pzogr Jun 14 '13 at 18:32
    
Let me clarify things more: let's say that 'visits' table holds visit number=1 for Mr XX and 'comments' table holds a foreign key relating to the visit number in 'visits'. If I update visit number in 'visits' (let's say to 3), will the foreign key in 'comments' change to 3 as well? –  pzogr Jun 14 '13 at 18:35
    
For your second question, yes as long as you specify ON UPDATE CASCADE in the foreign key. For your first, if you change the visit_number in visits, but not in comments it will lose the relation. –  Derek Downey Jun 14 '13 at 18:50
    
Many thanks Derek! –  pzogr Jun 14 '13 at 19:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.