USE zerobase;
• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐
• 기본키는 컬럼 하나로 지정할 수도 있고,
여러 개의 컬럼을 하나의 기본키로 지정할 수도 있다.
CREATE TABLE tablename ( column1 datatype NOT NULL, column2 datatype NOT NULL, ... CONSTRAINT constraint_name # 생략가능 PRIMARY KEY (column1, column2, ...) );
CREATE TABLE person ( pid int NOT NULL, name varchar(16), age int, sex char, PRIMARY KEY (pid) );
CREATE TABLE animal ( name varchar(16) NOT NULL, type varchar(16) NOT NULL, age int, PRIMARY KEY (name, type) );
# KEY 는 하나밖에 없기 때문에 따로 이름을 써서 지정하지 않는다. ALTER TABLE tablename DROP PRIMARY KEY;
ALTER TABLE person DROP PRIMARY KEY;
ALTER TABLE animal DROP PRIMARY KEY;
ALTER TABLE tablename ADD PRIMARY KEY (column1, column2, ...);
ALTER TABLE person ADD PRIMARY KEY (pid); # id가 아니라 pid 인듯
# CONSTRAINT PK_animal 생략가능 ALTER TABLE animal ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);
- 한 테이블을 다른 테이블과 연결해주는 역할이며,
참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)
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, ...) REFERENCES REF_tablename(REF_column) ); # FOREIGN KEY 는 연결되는 다른 참조 테이블이 필요하다 # 그게 REFERENCES # # 결국 제일 위 tablename 과 REF_tablename이 연결되고, # FOREIGN KEY 로 지정된 컬럼과 REF_column이 연결된다
예제 1. CREATE TABLE 에서 FOREIGN KEY를 지정하는 경우
orders 테이블의 pid 는 person 테이블의 pid 를 참조한다
(즉 두 pid 가 연결된다)
FOREIGN KEY 값으로 MUL 로 표시된다
어떤 한 테이블이 참조하는 테이블이 여러 테이블일 수 있다.
그래서 FOREIGN KEY 는 여러개 생성될 수 있는데,
DROP 등을 할때에는 COSNTRAINT 명을 알아야 한다.
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 job ( jid int NOT NULL, name varchar(16), pid int, PRIMARY KEY (jid), FOREIGN KEY (pid) REFERENCES person(pid) );
SHOW CREATE TABLE tablename;
SHOW CREATE TABLE job;
ALTER TABLE tablename DROP FOREIGN KEY;
ALTER TABLE orders DROP FOREIGN KEY FK_person;
이미 생성된 TABLE에 FOREIGN KEY 추가하기
Table 이 생성된 이후에도 ALTER TABLE 을 통해 FOREIGN KEY 를 지정할 수 있다
ALTER TABLE tablename ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);
ALTER TABLE orders ADD FOREIGN KEY (pid) REFERENCES person(pid);
police_station 과 crime_status 테이블 사이에 관계 (Foreign Key)를 설정해 봅시다.
AWS RDS(database-1) 의 zerobase 에서 작업합니다.
# police_station 에서 distinct name count select count(distinct name) from police_station; >> 31 # crime_status 에서 distinct police_station count select count(distinct police_station) from crime_status; >> 31
select distinct name from police_station limit 3; select distinct police_station from crime_status limit 3;
- p.name 의 이름과
- concat('서울', c.police_station, '경 찰 서') 을 비교해서
- 매칭되는 걸 가져온다
- group by 로 중복되지 않게 나옴
- 왼쪽 컬럼은 c.police_station
- 오른쪽 컬럼은 p.name
- 왼쪽과 오른쪽이 매칭되고 있는걸 확인할 수 있다
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;
ALTER TABLE police_station ADD PRIMARY KEY (name);
ALTER TABLE crime_status ADD COLUMN reference VARCHAR(16);
ALTER TABLE crime_status ADD FOREIGN KEY (reference) REFERENCES police_station(name);
- c.reference 를 p.name 으로 업데이트
- 조건 : p.name 이 concat('서울', c.police_station, '경찰서')과 같으면 업데이트
UPDATE crime_status 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;
Foreign Key 를 기준으로 두 테이블을 연관시켜 검색할 수 있다.
crime_status 가 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;
(1) 기본 세팅
cmd 창에서
cd Documents/sql_ws
mysql -u root -p
use zerobase
show tables;
CREATE TABLE study ( study_id int NOT NULL, study_date date, study_time time, patient_id int, PRIMARY KEY (study_id), CONSTRAINT FK_study FOREIGN KEY (patient_id) REFERENCES person(pid) );
ALTER TABLE study DROP PRIMARY key;
ALTER TABLE study DROP FOREIGN key FK_study;
ALTER TABLE study ADD FOREIGN KEY (patient_id) REFERENCES person(pid);
ALTER TABLE study ADD PRIMARY KEY (study_id);
위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.