Re: How to refresh contents of a child table...

[Peter Brawley]

Names change and these are long so they're not ideal keys. Surrogate keys would be preferable.

drop table if exists movies,actors,titles;
create table titles ( 
  tid smallint unsigned primary key auto_increment,
  title varchar(128), 
  genre varchar(32) not null  
); 

create table actors( 
  aid smallint unsigned primary key auto_increment,
  name varchar(128) 
);

create table movies ( 
  mid smallint unsigned primary key auto_increment,
  tid smallint unsigned, 
  aid smallint unsigned, 
  genre varchar(15) references titles(genre), 
  foreign key(tid) references titles(tid) on update cascade on delete cascade,
  foreign key(aid) references actors(aid) on update cascade on delete cascade
  
);

Genre probably should lookup into a table as a FK.