Let’s say I have two tables, Faculty and FacultyEmail who have a 1:1 relationship, with the following attributes:

If it’s truly a 1:1 relationship, a second table is an unnecessary complication. With a single table a generated column can be used.

CREATE TABLE IF NOT EXISTS `Faculty`
(
    FacultyID int not null primary key auto_increment,
    FirstName varchar(255) not null,
    LastName varchar(255) not null,
    Email varchar(255) as
      (lower(concat(firstname,'.',lastname,'@wossamotta.edu'))) stored not null,
    unique(Email)
);

However, a generated column cannot be updated. Your Faculty may not appreciate being unable to pick their email address. Consider using default instead.

    Email varchar(255) not null  
      default(lower(concat(firstname,'.',lastname,'@wossamotta.edu'))),

Unfortunately since it’s a data specification to make the faculty have that specific email and table

If you must have a FacultyEmail “table” for compatibility, consider a view.

create view FacultyEmail
  as select FacultyID, Email from Faculty

Try it.

---

If you really want two tables, you can write an after insert trigger.

create trigger default_faculty_email
  after insert 
  on Faculty for each row
  insert into FacultyEmail (facultyid, email)
    values(
      NEW.facultyid,
      lower(CONCAT(NEW.firstname,'.',NEW.lastname,'@wossamotta.edu'))
    );

However, after that there’s nothing preventing a FacultyEmail from being deleted leaving a Faculty with no FacultyEmail. So wee need another trigger to prevent that.

create trigger ProtectFacultyEmail
  before delete
  on FacultyEmail for each row
  signal sqlstate '45000'
    set message text = 'FacultyEmail cannot be directly deleted. Delete the Faculty row instead.';

This prevents directly deleting from FacultyEmail, but allows the on delete cascade.

As you can see, a second table raises a lot of complications.

Try it.

---

Notes

• I don’t believe there is a need for an on update cascade, there’s nothing in FacultyEmail to update. If the FacultyID can change that’s a poor primary key. Consider an independent ID column separate from any mutable ID.
• varchar(45) does not save any space, it only sets a maximum size. Maximum size is a business rule and should not be hard-coded into the schema.