SQL | Primary key, Foreign key

Primary Key

: 기본 키, 테이블 당 하나의 기본키를 가지고, 중복되지 않는 고유값을 포함한다. 테이블의 각 레코드를 식별할 수 있고, NULL 값을 포함할 수 없다.

• 생성 문법

CREATE TABLE tablename
(
	column1 datatype NOT NULL
    column2 datatype NOT NULL
    ...
    CONSTRAINT constraint_name
     PRIMARY KEY (column1, column2, ...)
);

• 생성 예제

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

• 삭제 문법

ALTER TABLE	tablename
DROP PRIMARY KEY;

#기본키가 하나든 여러 개든 삭제방법 동일

• 추가 문법

#새로운 필드를 추가하고, 그 필드를 기본 키로 설정하는 문법
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...);

#기존에 존재하는 필드를 기본 키로 설정하는 문법
ALTER TABLE tablename
MODIFIY COLUMN 필드이름 필드 타임 PRIMARY KEY

OR

ALTER TABLE tablename
MODIFIY COLUMN [CONSTRAINT 제약조건이름] PRIMARY KEY(필드이름)

• 추가 예제

#하나의 칼럼을 기본키로 지정하는 경우
ALTER TABLE person
ADD PRIMARY KEY (id);

#여러 개의 칼럼을 기본키로 지정하는 경우 [CONSTRAINT가 있음]
ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);

🤔CONSTRAINT는 제약이라는 뜻으로 데이터베이스 테이블의 각 레코드를 고유하게 식별한다.

Foreign Key

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

• 생성 문법

CREATE TABLE tablename
(
	column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype,
    column4 datatype,
    ...
    CONSTRAINT constraint_name
     PRIMARY KEY (column1, column2, ...)
    CONSTRAINT constraint_name
     FOREIGN KEY (column3, column4, ...) REFRENCES REF_tablename(REF_column)
);

• 생성 예제

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를 생략할 수 있다.

※자동생성된 constraint를 확인하는 방법 : SHOW CREATE TABLE tablename;

• 삭제 문법

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

• 추가 문법

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

• foreign key 사용 예제

#police_station 과 crime_status 테이블 사이에 관계(foreign key)를 설정
#aws rds의 zerobase에서 작업, police_station.name과 crime_status.police_station 매칭

#0.각 테이블에서 표시하는 형식이 다르면 같도록 만든다.
#1. police_station.name을 primary key로 설정
ALTER TABLE police_station
ADD PRIMARY KEY (name);

#2.crime_status테이블에 foreign key로 사용할 열 추가
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_status c, police_station p
SET c.reference = p.name
WHERE p.name LIKE concat('서울', c.police_station, '경찰서');

#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