0

A project requires me to build a java application that allows users to quickly create, update and delete labor reports (previously done by hand).

I clearly need a database for this and have spent the past week researching and slowly designing, but cant seem to clearly define what makes the difference between foreign key cascade option and creating triggers. Are they that different? Or just two ways to accomplish the same task in my case?

I ask this because is want changes for specific fields in one table to be reflected in all related tables that reference it.

I am using MySQL and MySQL Workbench for modeling and eventual forward engineering.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
exit_1
  • 1,240
  • 4
  • 13
  • 32

1 Answers1

2

Triggers can run arbitrary code. You could implement foreign keys (with or without cascade) via triggers.

Foreign keys (with or without cascade) do one, specific, focused job. In most database systems, you'll not be able to write a trigger that is more efficient than the code that is built in to support foreign keys (there'll be plenty of ways to write less efficient triggers).

For MySQL specifically, you may need to consider that it supports multiple storage engines and different engines have differences in their support of various foreign key features (see Using FOREIGN KEY constraints. If the engine that you're planning to use doesn't support the feature that you want to use, then you have no choice but to use triggers.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • thanks @Damien_The_Unbeliever. If I want to test/learn how the foreign key constraints will really effect my tables by manually inserting and editing rows, can I do that from MySQL Workbench in just my data models; or, do I need to forward engineer it first? – exit_1 Jan 21 '14 at 15:02
  • @solleks - I'm far from an expert on that - maybe search for or ask a separate question on that? – Damien_The_Unbeliever Jan 21 '14 at 15:08