created a column based on data from another table

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.

    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,''))) stored not null,

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  

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)

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.


  • 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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top