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.

Given a simplified example of two tables

table Contact(ID int primary key, Name text not null)
table ContactPhone(ContactID int not null, PhoneNumber text not null)

Is there a way to tell MySQL that the ContactPhone.ContactID field is referencing the Primary Key of Contact?

Can there be automatic handling? If the Contact is deleted, then relevant ContactPhone.ContactID should be set to zero.

Ideally this flag/code/operation should be attached to the ContactPhone table. This way existing software that interacts with the Contact table can keep on the way it is, and the ContactPhone table and related software can be added or removed as one unit, without the necessity of altering the Contact table.

Specifically I want to be able to be able to empty out the Contact table, reset the AUTO_INCREMENT to =1, and bring in new Contacts AND I don't want to have to tell this program anything about ContactPhone. I want it to be automatic.

share|improve this question
add comment

1 Answer 1

Is there a way to tell MySQL that the ContactPhone.ContactID field is referencing the Primary Key of Contact?

Yes, use a foreign key.

Can there be automatic handling? If the Contact is deleted, then relevant ContactPhone.ContactID should be set to zero.

Yes, add in an action after ON DELETE in the below code.

Here's an example:

create table ContactPhone(
  ContactID int not null, 
  PhoneNumber text not null,
  CONSTRAINT `fk_contactid`      -- this is the foreign key
    FOREIGN KEY (`ContactID` )
    REFERENCES `Contact`(`id` )
    ON DELETE <some action>      -- the on delete action goes here
) engine = InnoDB;               -- make sure to use InnoDB if you want FKs to be enforced

Make sure that either InnoDB is specified as the storage engine, or that your version of MySQL defaults to InnoDB. Other storage engines will parse and silently ignore foreign key constraints.

Here's the grammar for a MySQL foreign key:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION
share|improve this answer
1  
It's worth mentioning that Foreign Keys only work in InnoDB. MyISAM ignores them. –  Derek Downey Mar 8 '12 at 21:02
    
Presumably the referential triggered action they require is ON DELETE SET NULL which you do not mention. –  onedaywhen Mar 9 '12 at 10:08
    
@DTest -- good point, updated post to reflect that. –  Matt Fenwick Mar 9 '12 at 14:47
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.