Mysql – Linking identifiers across tables in MySQL (Foreign Keys)

foreign keyMySQL

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.

Best Answer

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
Related Question