Mysql – Foreign key in MySQL

The name "creatorUser" fails when there is a multi-column foreign key relationship.

As with the other posters here, I strongly support using the same column name for the same attribute, regardless of what table it is in.

Imagine a table of books, called "Books". If the primary key is "ISBN" then the foreign key in referencing tables should be "ISBN" - not "BookID" or "BOOKISBNID" or "BookISBN".

My reference for questions like these is Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems).

The workaround, if that's the right word for it, seems like it would be to make the same changes to the child table in an AFTER UPDATE trigger on the parent table, containing the same logic as the BEFORE UPDATE trigger on the child table, wrapped inside a test to only do anything if the primary key in the parent table has actually changed, which would have caused a cascaded update.

DELIMITER $$
CREATE TRIGGER parent_after_update AFTER UPDATE ON parent_table FOR EACH ROW
BEGIN

# not using null-safe <=> comparison since PK cannot be null

IF NEW.primary_key != OLD.primary_key THEN

  UPDATE child_table ct
     SET /* the same logic that's in the before update trigger on the child table */
   WHERE ct.parent_primary_key = NEW.primary_key;

END IF;
END $$
DELIMITER ;

If primary_key on parent_table just now changed, then by definition, all of the rows child_table with the new value in their parent_primary_key column must have just been updated via a cascaded update, because until that value exists in parent_table.primary_key, there could not have been any rows already existing with the new value in child_table.parent_primary_key ... until the parent table's primary_key was updated just now, those rows could not possibly have existed, since they would have violated the referential constraint of the foreign key (referencing a nonexistent value).

Of course, I'm using "primary key" here when in reality the foreign key could reference any superkey of the parent table.

However, this calls into question what you're really using the cascaded updates to accomplish in the first place and whether that application of cascaded updates constitutes a sound design. You seem to be deriving another column from the value, and storing that derived column, and both of these seem like less-than-ideal practices.

Normally, a cascaded update, at least in principle if not in fact, seems like something that is not a change to the data, as an update changing the child table directly would be. "These" child records reference "that" row in the parent table. After a cascaded update, they still do, and nothing has changed, only the key is different.