基于关联表数据生成列：1:1关联表自动生成Email的DDL实现

Absolutely, this is totally doable! But there's a key detail to note first: MySQL's generated columns (the GENERATED ALWAYS AS syntax) can only reference columns within the same table. Since your Email needs data from the linked Faculty table, we can't directly use a cross-table generated column here. Instead, triggers are the cleanest, most efficient way to handle this—they’ll automatically populate and update the Email field whenever changes happen in the Faculty table.

Solution 1: Use Triggers (Recommended, No Redundant Data) #

First, adjust your table DDLs to set up the basic structure (we’ll handle the Email logic via triggers):

CREATE TABLE IF NOT EXISTS `Faculty` (
 `FacultyID` INT NOT NULL,
 `FirstName` VARCHAR(45) NOT NULL,
 `LastName` VARCHAR(45) NOT NULL,
 PRIMARY KEY (`FacultyID`)
);

CREATE TABLE IF NOT EXISTS `FacultyEmail` (
 `FacultyID` INT NOT NULL,
 `Email` VARCHAR(90) NOT NULL, -- Triggers will manage this field
 PRIMARY KEY (`FacultyID`),
 FOREIGN KEY (`FacultyID`) REFERENCES `Faculty` (`FacultyID`) ON DELETE CASCADE ON UPDATE CASCADE
);

Next, create an AFTER INSERT trigger to generate the Email when a new faculty member is added:

DELIMITER //
CREATE TRIGGER trg_faculty_after_insert
AFTER INSERT ON Faculty
FOR EACH ROW
BEGIN
  -- Use LOWER() to ensure standard lowercase email format
  INSERT INTO FacultyEmail (FacultyID, Email)
  VALUES (NEW.FacultyID, LOWER(CONCAT(NEW.FirstName, '.', NEW.LastName, '@gmail.com')));
END //
DELIMITER ;

Then, add an AFTER UPDATE trigger to refresh the Email if the faculty member’s name changes:

DELIMITER //
CREATE TRIGGER trg_faculty_after_update
AFTER UPDATE ON Faculty
FOR EACH ROW
BEGIN
  -- Only update the Email if FirstName or LastName actually changed
  IF NEW.FirstName <> OLD.FirstName OR NEW.LastName <> OLD.LastName THEN
    UPDATE FacultyEmail
    SET Email = LOWER(CONCAT(NEW.FirstName, '.', NEW.LastName, '@gmail.com'))
    WHERE FacultyID = NEW.FacultyID;
  END IF;
END //
DELIMITER ;

Solution 2: Redundant Columns with Generated Column (Less Ideal) #

If you’re okay with duplicating FirstName and LastName in the FacultyEmail table (to use a generated column), you can do this. But note: you’ll still need triggers to keep the duplicated fields in sync with the Faculty table, making this less efficient than the trigger-only approach.

CREATE TABLE IF NOT EXISTS `Faculty` (
 `FacultyID` INT NOT NULL,
 `FirstName` VARCHAR(45) NOT NULL,
 `LastName` VARCHAR(45) NOT NULL,
 PRIMARY KEY (`FacultyID`)
);

CREATE TABLE IF NOT EXISTS `FacultyEmail` (
 `FacultyID` INT NOT NULL,
 `FirstName` VARCHAR(45) NOT NULL,
 `LastName` VARCHAR(45) NOT NULL,
 `Email` VARCHAR(90) GENERATED ALWAYS AS (LOWER(CONCAT(FirstName, '.', LastName, '@gmail.com'))) STORED,
 PRIMARY KEY (`FacultyID`),
 FOREIGN KEY (`FacultyID`) REFERENCES `Faculty` (`FacultyID`) ON DELETE CASCADE ON UPDATE CASCADE
);

Quick Tips: #

• The LOWER() function ensures the email follows standard case-insensitive formatting (avoids duplicate entries or confusion).
• The ON DELETE CASCADE foreign key rule automatically deletes the corresponding FacultyEmail record if a faculty member is removed from the Faculty table.
• The update trigger only modifies the Email when necessary, preventing unnecessary database writes.

内容的提问来源于stack exchange，提问作者asathkum