[SQL] PRIMARY KEY / FOREIGN KEY

1️⃣ PRIMARY KEY(기본 키) - 생성하기

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL값을포함할수없음
• 테이블 당 하나의 기본키를 가짐

• 여러 개의 칼럼을 기본키로 설정하는 경우

  CREATE TABLE  animal
  (
      name varchar(16) NOT NULL,
      type varchar(16) NOT NULL,
      age int,
      PRIMARY KEY (name, type)
  );

  

2️⃣ PRIMARY KEY 삭제 문법

• 여러 개의 컬럼이 기본키로 설정되어 있는 경우에도 동일하다.

  ALTER TABLE person
  DROP PRIMARY KEY;

3️⃣ PRIMARY KEY 생성 문법 - 테이블 생성 이후

• 하나의 칼럼을 기본키로 지정하는 경우

  ALTER TABLE person
  ADD PRIMARY KEY (pid);

  

• 여러개의 칼럼을 기본키로 지정하는 경우

  ALTER TABLE animal
  ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);

  

4️⃣ FOREIGN KEY(외래 키) - 생성하기

• 한 테이블을 다른 테이블과 연결해주는 역할이며,
  참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

• CREATE TABLE 에서 FOREIGN KEY를 지정하는 경우

  CREATE TABLE orders
  (
      oid int not null,
      order_no varchar(16),
      pid int,
      PRIMARY KEY (oid),
      CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid)
  );

  

• CREATE TABLE 에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT 를 생략할 수 있다.

  CREATE TABLE job
  (
      jid int not null,
      name varchar(16),
      pid int,
      PRIMARY KEY (jid);
      FOREIGN KEY (pid) REFERENCES person(pid)
  );

5️⃣ FOREIGN KEY - CONSTRAINT 확인 문법

• 자동 생성된 CONSTRAINT 를 확인하는 방법

  SHOW CREATE TABLE job;

  

6️⃣ FOREIGN KEY 삭제 문법

• 기존 테이블에서 외래 키를 삭제한다.

  ALTER TABLE tablename
  DROP FOREIGN KEY FK_constraint;

  ALTER TABLE orders
  DROP FOREIGN KEY FK_person;

  

7️⃣ FOREIGN KEY 생성하기 - 테이블 생성 이후

• 기존 테이블에서 외래 키를 생성한다.

  ALTER TABLE tablename
  ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

  ALTER TABLE orders
  ADD FOREIGN KEY (pid) REFERENCES person(pid);

  

  

8️⃣ 예제

• police_station 과 crime_status 테이블 사이에 관계 (Foreign Key)를 설정한다. AWS RDS(database-1) 의 zerobase 에서 작업한다.

🤔 분석하기

---

• police_station.name 과 crime_status.police_station 을 매칭하여 관계를 맺는다.
  

💡 경찰서 이름이 각 테이블에서 표시되는 형식이 다르다!
-> police_station의 name을 참조해 crime_status의 reference 컬럼(ex. 서울중부경찰서 형태)을 만들고 -> police_station의 name과 동일한 값을 찾는다. -> 지역별 경찰서 주소 찾기가 가능하다.

• crime_status.police_station 을 police_station.name 과 같이 만들어서 비교 (보기)
  

👩🏻‍💻 풀이 과정

---

1. police_station.name 을 Primary Key 로 설정

• 참조가 필요한 컬럼이 있는 테이블에서 기본키를 만든다.

  ALTER TABLE police_station
  ADD PRIMARY KEY (name);

2. crime_status 테이블에 Foreign Key 로 사용할 Column 추가

• 수정이 필요한 컬럼이 있는 테이블에서 외래키를 만든다.

  ALTER TABLE crime_status
  ADD COLUMN reference VARCHAR(16);

  

3. Foreign Key 생성

   ALTER TABLE crime_status
   ADD FOREIGN KEY (reference) REFERENCES police_station(name);

   

4. Foreign Key 값 Update

   UPDATE crime_satus c, police_station p
   SET c.reference = p.name
   WHERE p.name LIKE concat('서울', c.police_station, '경찰서');

   SELECT distinct police_station, reference
   FROM crime_status;

   

5. Foreign Key 를 기준으로 두 테이블을 연관시켜 검색할 수 있다.

   SELECT c.police_station, p.address
   FROM  crime_status c, police_station p
   WHERE c.reference = p.name
   GROUP BY c.police_station;