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