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 have a table Persons with id as pk and auto increment. Then I have a table Addresses with id as pk and fk to the Persons id. But now person can exist without an address, and I don't want a person to exist without an address. I am looking for a standard way to solve this problem. I am using MySql and InnoDB.

share|improve this question
    
You do realise that multiple people live at the same address in many cases? –  Phil Dec 9 '13 at 21:35
    
Yes this is just an example, I am aware this is wrong sorry for this. –  Tony Dec 9 '13 at 21:36

1 Answer 1

up vote 0 down vote accepted

You have many state changes you want to happen in order for your interaction to be considered a success.

This pretty much defines the "atomic" goal of a transactions.

begin; --start the transaction
insert into person values (...);
insert into address values (....);
commit; --finish the transaction

This ensures that if there was an error any where along the way all transaction activity is rolled back. It's an "all or nothing" operation so you don't end up in an in between state you define as undesirable.

share|improve this answer
    
Great answer, thank you very much! –  Tony Dec 10 '13 at 19:23
    
@tony I should have included in my reply that in order for this to be transactional you must use the innodb storage engine. It is the only mysql engine that supports transactions. –  atxdba Dec 10 '13 at 23:27

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.