[sql] PRIMARY KEY, FOREGIN KEY

[PRIMARY KEY]

• 각 테이블의 코드를 식별한다.
• 중복되지 않은 고유값을 포함한다.
• NULL 값을 포함할 수 없다. ( NOT NULL 을 안해도 NOT NULL이 된다. )
• 테이블 당 하나의 기본키를 갖는다. ( 두 개 이상의 칼럼을 하나의 기본 키로서 가질 수도 있다. )
• 어차피 하나의 기본키 이기 때문에 CONSTRAINT constraint_name 을 해줄 필요가 없다.

 

• 하나의 칼럼을 기본 키로 가질 때

CREATE TABLE person
(
	pid int NOT NULL,
    name varchar(16),
    age int,
    sex char,
    PIMARY KEY (pid)
)

 

• 두개 이상의 칼럼을 기본 키로 가질 때

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

 

• 기본 키를 삭제
  하나의 칼럼이나 두개의 칼럼이나 삭제하는 방법은 같다.
  기본 키는 하나이기 때문에

ALTER TABLE person
DROP PRIMARY KEY;

 

• 기본 키 생성

ALTER TABLE person
ADD PRIMARY KEY (id);

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

---

[FOREIGN KEY] 

• .한 테이블을 다른 테이블과 연결해주는 역할
  
• 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일 값)
• 단순히 데이터를 조회할 때 쓴다라는 설명 보다는 데이터의 무결성 을 만족 시키는 측면에서 접근하면 좋을 것 같다.

참조 : https://untitledtblog.tistory.com/123

[관계형 데이터베이스] - 무결성 (Integrity)

 

• 테이블 생성할 때 외래 키 부여
  ( CONSTRAINT 는 생략가능, 생략하면 자동으로 constraint name이 정해진다. )

CREATE TABLE ORDERS
(
    oid int NOT NULL,
    order_no varchar(16),
    pid int,
    PRIMARY KEY (oid),
    CONSTRAINT FK_person
    FOREIGN KEY (pid) REFERENCES person(id)
);

 

• 자동 생성된 CONSTRAINT 확인

SHOW CREATE TABLE orders;

 

• FOREIGN KEY  삭제

ALTER TABLE orders
DROP FOREIGN KEY job_ibfk_1;

삭제가 안된 것 같지만,

 

CONSTRAINT NAME 과 REFERENCES 가 사라진 것을 통해 제대로 잘 제거가 됐다는 것을 알 수있다. 

 

 

• 외래 키 생성

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

---

[실습]

• 위의 두 테이블에서 각각 name 칼럼과 police_station 칼럼을 foreign 키로 엮으려고 한다.
• 두 칼럼에 대해 알아보자.

SELECT count(distinct name) FROM police_station;
SELECT count(distinct police_station) FROM crime_status;

SELECT DISTINCT name FROM police_station LIMIT 5;
SELECT DISTINCT police_station FROM crime_status LIMIT 5;

 

• 둘간에 서로 매칭이 되는지 확인해보자.

SELECT c.police_station, p.name
FROM crime_status c, police_station p
WHERE p.name LIKE CONCAT('서울', c.police_station, '경찰서')
GROUP BY c.police_station, p.name;

매칭이 잘 되고 있다.

• police station 에 name을 기본키로 추가

ALTER TABLE police_station
ADD PRIMARY kEY(name);

• crime_status에 reference 라는 칼럼 만들어서 foreign key 로 추가

ALTER TABLE crime_status
ADD COLUMN reference varchar(16);

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

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

데이터가 잘 들어갔습니다.

• 기본 키와 외래 키를 이용해 이제 데이터를 조회해 볼 수 있다.

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

 

 

 

 

p.s. UPDATE SET

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

SET 변경할 칼럼 = 데이터 이다.

위에서는 따라서 reference 라는 칼럼에 p 테이블의 name 칼럼 안의 데이터들이 복사되었고

이때 중요한 점은, WHERE 조건절을 반드시 써줘야 한다는 점이다.

그래야 c.police_station 이 '중부' 인 곳에 '서울중부경찰서' 가 들어가겠다.