---
# System prepended metadata

title: SQL 複合主鍵

---

# SQL 複合主鍵
https://www.itread01.com/content/1547717253.html

https://www.itread01.com/content/1549211792.html

指令
```sql=
ALTER TABLE LESSION_FLA
ADD CONSTRAINT FLA PRIMARY KEY (STU_NO, COUR_NO);
```
![](https://i.imgur.com/GeaciOq.png)
![](https://i.imgur.com/iUuu3jj.png)

---
![](https://i.imgur.com/KX9DMBF.png)
 ```sql=
 SELECT * FROM LESSION_TIME;
 
 SELECT * FROM LESSION_COURSE JOIN LESSION_TIME ON LESSION_COURSE.COUR_NO=LESSION_TIME.COUR_NO;
 
  SELECT * FROM LESSION_COURSE JOIN LESSION_TIME ON LESSION_COURSE.COUR_NO=LESSION_TIME.COUR_NO;
 
  SELECT * FROM LESSION_COURSE JOIN LESSION_DETAIL ON LESSION_DETAIL.COUR_NO=LESSION_COURSE.COUR_NO 
  JOIN LESSION_TIME  ON LESSION_TIME.LESSION_TIME_NO=LESSION_DETAIL.LESSION_TIME_NO;
 ```
我們要違反正規化~

不違反 照著er做
![](https://i.imgur.com/fAM3x9i.png)
 ```sql=
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:MI:SS';

SELECT * FROM LESSON 
JOIN LESSON_DETAIL ON LESSON_DETAIL.LESSNO=LESSON.LESSNO
JOIN LESSON_TIME  ON LESSON_TIME.LTIME_NO=LESSON_DETAIL.LTIME_NO
WHERE LESSON_DETAIL.LESSNO=LESSON.LESSNO and LESSON_TIME.LTIME_NO=LESSON_DETAIL.LTIME_NO;
 ```
 
 
 ----
#  教練
 ![](https://i.imgur.com/zLdksuD.png)
![](https://i.imgur.com/Zhidj8X.png)

```sql
ALTER TABLE activity
ADD FOREIGN KEY (c_no) REFERENCES coach(coach_no);
----
教練修改 活動狀態
UPDATE activity
SET A_STATUS=1
WHERE A_no=01;
```