SQL Code

Create Foreign Keys in MySQL

If you're using/controlling MySQL from a GUI tool, you may have the ability to easily add foreign key constraints. However, if you have to use the command prompt, here's the SQL statement code to manually add a foreign key constraint.

When you use this code, the foreign key will be created with a sequentially numbered name, such as 0_172. Knowing the number/name is important if you ever have to drop the foreign key constraint.

These two lines: "on delete cascade" and "on update cascade" are very important. These are switches that will signal the database to do the work for you in update/delete scenarios. For example, say in your CRM application, you were going to delete a contact. You'd also want to delete any appointments you have with that contact. Well, instead of programming the logic to delete the contact, then separately delete the appointments, you just set "on delete cascade". That way when you delete the tuple from the contacts table, all records constrained to it in the appointments table are automatically deleted. This is a good way to ensure and maintain data constistency and integrity.


alter table appointments
add foreign key ( contact_id ) 
references contacts( id ) 
on delete cascade
on update cascade
Share

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image