-- 剛好有邦友在問關於外鍵的事
https://ithelp.ithome.com.tw/questions/10198828

-- 寫了一個簡單範例.

create table actresses (
  id int unsigned not null auto_increment primary key
, name varchar(20) not null
);

insert into actresses (name) values
('小島みなみ'),('初川みなみ');

select *
  from actresses;

+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 小島みなみ      |
|  2 | 初川みなみ      |
+----+-----------------+
2 rows in set (0.00 sec)

-- 接著建立 table , 其欄位 actress_id 與 actresses(id) 關聯

create table videos (
  id int unsigned not null auto_increment primary key
, video_id char(8) not null
, actress_id int unsigned not null 
, foreign key (actress_id) references actresses (id)
);

insert into videos (video_id, actress_id) values
('SSNI-744', 1), ('SSPD-154', 1),
('MIDE-755', 2), ('MIDE-743', 2);

-- 簡單的查詢
select a.name
     , v.video_id
  from actresses a
  join videos v
    on (a.id = v.actress_id);
 
+-----------------+----------+
| name            | video_id |
+-----------------+----------+
| 小島みなみ      | SSNI-744 |
| 小島みなみ      | SSPD-154 |
| 初川みなみ      | MIDE-755 |
| 初川みなみ      | MIDE-743 |
+-----------------+----------+
4 rows in set (0.00 sec)

-- 以上是一個簡單的兩個 table 的 範例.

-- 但是有些影片是由兩位女優共演的,那我們要如何對應此情境？
-- 我們可以做以下動作
-- 先將 videos 中的 actress_id 改名為 first_actress_id

alter table videos
rename column actress_id to first_actress_id;

-- 然後增加 second_actress_id
alter table videos
add column second_actress_id int unsigned default null;

-- 新增外鍵關聯
alter table videos
add constraint foreign key (second_actress_id) references actresses (id);

-- 修改完成! 這時候的 videos 內容

select * from videos;
+----+----------+------------------+-------------------+
| id | video_id | first_actress_id | second_actress_id |
+----+----------+------------------+-------------------+
|  1 | SSNI-744 |                1 |              NULL |
|  2 | SSPD-154 |                1 |              NULL |
|  3 | MIDE-755 |                2 |              NULL |
|  4 | MIDE-743 |                2 |              NULL |
+----+----------+------------------+-------------------+
4 rows in set (0.00 sec)

-- 接著輸入資料有兩位女優合演的資料
-- 小島是前輩,所以是 first

insert into videos (video_id, first_actress_id, second_actress_id)
values
('SSNI-417', 1, 2), ('MIDE-618', 1, 2);

-- 接著我們來查詢
select video_id
     , a1.name as 'first'
     , a2.name as 'second'
  from videos v
  left join actresses a1
    on (v.first_actress_id = a1.id)
  left join actresses a2
    on (v.second_actress_id = a2.id);
    
+----------+-----------------+-----------------+
| video_id | first           | second          |
+----------+-----------------+-----------------+
| SSNI-417 | 小島みなみ      | 初川みなみ      |
| MIDE-618 | 小島みなみ      | 初川みなみ      |
| SSNI-744 | 小島みなみ      | NULL            |
| SSPD-154 | 小島みなみ      | NULL            |
| MIDE-755 | 初川みなみ      | NULL            |
| MIDE-743 | 初川みなみ      | NULL            |
+----------+-----------------+-----------------+
6 rows in set (0.00 sec)


-- 上面這段查詢可以建立 view , 方便後續使用
create view video_actress as
select video_id
     , a1.name as 'first'
     , a2.name as 'second'
  from videos v
  left join actresses a1
    on (v.first_actress_id = a1.id)
  left join actresses a2
    on (v.second_actress_id = a2.id);

-- 就可以直接查詢了

select *
  from video_actress;

+----------+-----------------+-----------------+
| video_id | first           | second          |
+----------+-----------------+-----------------+
| SSNI-417 | 小島みなみ      | 初川みなみ      |
| MIDE-618 | 小島みなみ      | 初川みなみ      |
| SSNI-744 | 小島みなみ      | NULL            |
| SSPD-154 | 小島みなみ      | NULL            |
| MIDE-755 | 初川みなみ      | NULL            |
| MIDE-743 | 初川みなみ      | NULL            |
+----------+-----------------+-----------------+
6 rows in set (0.00 sec)