Unique foreign key constraint between three tables
I would use one table for both completed and missed appointments. Using your notation:

appointments: 
id, 
visitor_id, 
scheduled_time, 
status, 
CHECK(status in('Scheduled', 'Completed', 'Missed')), 
Reason_missed, 
CHECK((status='Missed' and reason_missed is not null) or (status&lt;&gt;'Missed' and reason_missed is null)), 
unique(id, status)

attendances: 
id, 
appointment_id (nullable), 
arrival_time, 
appointment_status,
check((appointment_id is null and appointment_status is null) or (appointment_id is not null and appointment_status in('Scheduled', 'Completed'),
foreign key(appointment_id, appointment_status) references appointments(id, status) on update cascade