# [Oracle]主鍵、外鍵 ###### tags: `oracle` `primary key` `foreign key` `database` `SQL` `Oracle SQL Developer` > [time= 2019 11 18 ] > 原文 & 參考： > https://www.1keydata.com/tw/sql/sql-foreign-key.html > https://www.twblogs.net/a/5b7efb112b717767c6ad05f7 `SQL Developer version: 19.1.0.094.2042` <br> ## 主鍵： ```sql= "ID" NUMBER(10, 0) NOT NULL ENABLE PRIMARY KEY, ``` 或 ```sql= CONSTRAINT PK_TABLE_ID PRIMARY KEY(TYPE_ID) ``` <br><br><br> ## 複合主鍵 ```sql= CONSTRAINT PK_TABLE_TOPIC_SUBTOPIC_ID PRIMARY KEY(TOPIC, SUBTOPIC_ID) ``` <br><br><br> ## 在創建 table 後，使用ALTER TABLE語法 ```sql= -- 單主鍵 ALTER TABLE YOUR_TABLE ADD CONSTRAINT PK_TABLE_ID PRIMARY KEY (ID); -- 複合主鍵 ALTER TABLE YOUR_TABLE ADD CONSTRAINT PK_TABLE_TOPIC_SUBTOPIC_ID PRIMARY KEY (TOPIC, SUBTOPIC_ID); ``` <br><br><br> ## 外鍵： ```sql= "MAIL_ADDRESS_ID" NUMBER(10, 0) REFERENCES YOUR_FK_TABLE(MAIL_ADDRESS_ID), ``` 或 ```sql= CONSTRAINT FK_TABLE_MAIL_ADDRESS_ID FOREIGN KEY(MAIL_ADDRESS_ID) REFERENCES YOUR_FK_TABLE(MAIL_ADDRESS_ID) ``` <br><br><br> ## 複合外鍵： ```sql= CONSTRAINT FK_TABLE_TOPIC_MAIL_ADDRESS_ID FOREIGN KEY(TOPIC, SUBTOPIC_ID) REFERENCES YOUR_FK_TABLE(TOPIC, SUBTOPIC_ID) ``` <br><br><br> ## 在創建 table 後，使用ALTER TABLE語法 ```sql= -- 單外鍵 ALTER TABLE YOUR_TABLE ADD (CONSTRAINT FK_TABLE_MAIL_ADDRESS_ID) FOREIGN KEY (MAIL_ADDRESS_ID) REFERENCES YOUR_FK_TABLE (MAIL_ADDRESS_ID); -- 複合外鍵 ALTER TABLE YOUR_TABLE ADD (CONSTRAINT FK_TABLE_MAIL_ADDRESS_ID) FOREIGN KEY (TOPIC, SUBTOPIC_ID) REFERENCES YOUR_FK_TABLE (TOPIC, SUBTOPIC_ID); ``` <br><br><br> ## 完整範例 ### TABLE_A ```sql= CREATE TABLE "WMS"."TABLE_A" ( "ID" NUMBER(10, 0) NOT NULL ENABLE PRIMARY KEY, "TOPIC" VARCHAR2(30 BYTE) NOT NULL ENABLE, "SUBTOPIC_ID" NUMBER(10, 0) NOT NULL ENABLE, "MAIL_ADDRESS_ID" NUMBER(10, 0) , "UPD_DATE" DATE, "CRT_DATE" DATE DEFAULT(SYSDATE) NOT NULL ENABLE , "CRT_NAME" NVARCHAR2(16) NOT NULL ENABLE, CONSTRAINT FK_TABLE_A_TOPIC__SUBTOPIC_ID FOREIGN KEY(TOPIC, SUBTOPIC_ID) REFERENCES TABLE_B(TOPIC, SUBTOPIC_ID), -- 複合外鍵 CONSTRAINT FK_TABLE_B_MAIL_ADDRESS_ID FOREIGN KEY(MAIL_ADDRESS_ID) REFERENCES TABLE_C(MAIL_ADDRESS_ID) --外鍵 ); ``` <br><br><br> ### TABLE_B ```sql= CREATE TABLE "WMS"."TABLE_B" ( "TOPIC" VARCHAR2(30 BYTE) NOT NULL ENABLE , "SUBTOPIC_ID" NUMBER(10, 0) NOT NULL ENABLE , "SUBTOPIC" VARCHAR2(100 BYTE) NOT NULL ENABLE, "MAILTYPE" VARCHAR2(30 BYTE) NOT NULL ENABLE, "MAIL_SUBJECT" VARCHAR2(200 BYTE), "MAIL_BODY" VARCHAR2(500 BYTE), "MAIL_ATTACHMENT" VARCHAR2(500 BYTE), CONSTRAINT PK_TABLE_B_TOPIC_SUBTOPIC_ID PRIMARY KEY(TOPIC, SUBTOPIC_ID) --複合主鍵 ); ``` <br><br><br> ### TABLE_C ```sql= CREATE TABLE "WMS"."TABLE_C" ( "MAIL_ADDRESS_ID" NUMBER(10, 0) NOT NULL ENABLE PRIMARY KEY, "MAIL_ADDRESS" VARCHAR2(100 BYTE) NOT NULL ENABLE, "DC_CODE" VARCHAR2(20 BYTE), "GUP_CODE" VARCHAR2(20 BYTE), "CUST_CODE" VARCHAR2(20 BYTE), "ENABLE" NUMBER(1) DEFAULT(1) NOT NULL CHECK(ENABLE in (0, 1)) ); ```