MySQL: Can I constraint column values in one table to values in a column in another table, by DB design only?

Question

Example:

Table "persons", Column "surname" may only contain values predefined in

Table "names", Column "surnames", which would contain a collection of surnames acceptable for the purpose.

Can I achieve this by design (i.e. without involving any validation code)? On a MyISAM table? No? On InnoDB?

Thank you.

Answer 1

What you're asking for is a foreign key constraint. You'd need to use InnoDB - quote:

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

To add a foreign key constraint within the CREATE TABLE statement for PERSONS:

FOREIGN KEY (surname) REFERENCES names(surnames)

Using an ALTER TABLE statement if the tables already exist:

ALTER TABLE persons 
  ADD CONSTRAINT FOREIGN KEY (surname) REFERENCES names(surname)

Be aware that if you use the ALTER TABLE statement, the data in the PERSONS table can only contain surname values that exist in the NAMES.surname table - it can not be applied until after the data has been fixed.

Answer 2

For MyISAM tables you can achieve desired functionality by using triggers.

For instance (validate insert),

DELIMITER // 
CREATE DEFINER=`root`@`localhost` TRIGGER BEFORE INSERT ON persons
FOR EACH ROW
BEGIN
  DECLARE tmp_surname varchar(100);
  SELECT surname into tmp_surname FROM names WHERE surname = NEW.surname; 
  IF (tmp_surname IS NULL) THEN
    INSERT INTO t1(id,value) VALUES('aaa'); #raise an 'exception'
  END IF;    
END;//
delimiter;

Mysql doesn't have exceptions, but you can terminate execution(and, consequently, 'rollback' changes) by creating an invalid statement