Kindly appreciate the notice.

The triggers are for insert and update on the reference tables — the master dictionary if you will. Trigger firings, therefore, are extremely rare — the frequency of “discovering” a new word to add. These reference tables are virtually read only.

The more active write tables are strictly no-frills word-usage data collection. No triggers involved.

With pleasure. Do take notice that triggers will *considerably* slow down your queries. If this is a one time job, for importing data from MSSQL, this may be fine; but make sure to check on your performance if you’re going live with active triggers.

I am in the process of moving a large very specialized linguistic database in MS SQL to MySQL (way cheaper on aws.amazon). Referential integrity was enforced in SQL Server using triggrs that checked the inserted values against views — this was the ONLY way possible given the highly customized way words in this database are categorized (over 400 unique lexical senses). I was beating my head bloody trying to figure out a way to “trick” MySQL into similar functionality, and happily like the song says “then came you!”

Again, thank you for your generosity sharing your knowledge.

thanks for this important correction. I’ve updated the text. I have to check how I came to that conclusion.

In your sample code above, it is best if you specified ENGINE=InnoDB for those who have MyISAM as default storage engine.

When doing the above with MyISAM, no error is ever produced. Moreover, trying to ALTER tables to InnoDB still works fine, since the foreign key was silently dropped as we were using MyISAM. Will check if there’s an open bug on this.

Regards,
Shlomi