45.SQL-11

SOWA·2023년 5월 1일

sql

SQL

목록 보기

11/12

🧷 Primary key

기본 키

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

🖇️Primary key 생성 문법

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

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

CREATE TABLE person1
(
  pid int NOT NULL,
  name varchar(16),
  age int,
  sex char,
  PRIMARY KEY (pid)
);

⇊

mysql> create table person1
    -> (
    ->     pid int NOT NULL,
    ->     name varchar(16),
    ->     age int,
    ->     sex char,
    ->     PRIMARY KEY (pid)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

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

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

⇊

mysql> create table animal
    -> (
    ->     name varchar(16) NOT NULL,
    ->     type varchar(16) NOT NULL,
    ->     age int,
    ->     CONSTRAINT PK_Person PRIMARY KEY (name, type)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(16) | NO   | PRI | NULL    |       |
| type  | varchar(16) | NO   | PRI | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

🖇️ PRIMARY KEY 삭제 문법

ALTER TABLE tablename
DROP PRIMARY KEY;

하나의 칼럼이 기본키로 설정된 경우

ALTER TABLE person1
DROP PRIMARY KEY;

⇊

mysql> alter table person1 drop primary key;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

여러개의 칼럼이 기본키로 설정된 경우(삭제방법은 동일)

ALTER TABLE animal
DROP PRIMARY KEY;

⇊

mysql> alter table animal drop primary key;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(16) | NO   |     | NULL    |       |
| type  | varchar(16) | NO   |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

🖇️기존 데이터에 Primary key 속성 추가

ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...)

하나의 칼럼을 기본키로 지정

ALTER TABLE person1
ADD PRIMARY KEY (id);

⇊

mysql> alter table person1 add primary key (pid);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

여러개의 칼럼을 기본키로 지정

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

⇊

mysql> alter table animal add constraint pk_animal primary key (name, type);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(16) | NO   | PRI | NULL    |       |
| type  | varchar(16) | NO   | PRI | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

*CONSTRAINT를 생략했을 경우 자동 생성됨

🧷 FOREIGN KEY

외래키

한 테이블을 다른 테이블과 연결해 주는 역할
참조되는 테이블의 항목은 그 테이블의 기본키(혹은 단일값)
PRIMARY KEY와 달리 여러개 생성 가능

🖇️ 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,...) REFERENCES REF_tablename(REF_colum)
);

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 person1(pid)
);

⇊

mysql> CREATE TABLE orders
    -> (
    ->     oid int not null,
    ->     order_no varchar(16),
    ->     pid int,
    ->     PRIMARY KEY (oid),
    ->     CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person1(pid)
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid      | int         | NO   | PRI | NULL    |       |
| order_no | varchar(16) | YES  |     | NULL    |       |
| pid      | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

CREATE TABLE에서 FOREIGN KEY를 지정할 경우, CONSTRAINT 생략가능

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

⇊

mysql> create table job
    -> (
    ->     jid int not null,     
    ->     name  varchar(16),    
    ->     pid int,
    ->     PRIMARY KEY (jid),
    ->     FOREIGN KEY (pid) REFERENCES person1(pid)
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> desc job;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid   | int         | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| pid   | int         | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.15 sec)

자동생성된 CONSTRAINT 확인법
SHOW CREATE TABLE tablename;

🖇️ FOREIGN KEY 삭제 문법

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

테이블이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY 지정 가능

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

─ 두 테이블사이의 관계(FOREIGN KEY)설정

police_Station과 crime_status 테이블 사이의 관계(foreign key)설정

police_station.name과 crime_status.police_station을 매칭하여 관계 맺기

mysql> select count(distinct name) from police_station; 
+----------------------+
| count(distinct name) |
+----------------------+
|                   31 |
+----------------------+

mysql> select count(distinct police_station) from crime_status;   
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
|                             31 |
+--------------------------------+

경찰서 이름이 각 테이블에서 표시되는 형식 다름

mysql> select distinct name from police_station limit 3;
+--------------------------+
| name                     |
+--------------------------+
| 서울중부경찰서           |
| 서울종로경찰서           |
| 서울남대문경찰서         |
+--------------------------+

mysql> select distinct police_station from crime_status limit 3;   
+----------------+
| police_station |
+----------------+
| 중부           |
| 종로           |
| 남대문         |
+----------------+

crime_status.police_station을 police_station.name과 같이 만들어 비교

mysql> 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                     |
+----------------+--------------------------+
| 중부           | 서울중부경찰서           |
| 종로           | 서울종로경찰서           |
| 남대문         | 서울남대문경찰서         |
| 서대문         | 서울서대문경찰서         |
| 혜화           | 서울혜화경찰서           |
| 용산           | 서울용산경찰서           |
| 성북           | 서울성북경찰서           |
| 동대문         | 서울동대문경찰서         |
| 마포           | 서울마포경찰서           |
| 영등포         | 서울영등포경찰서         |
| 성동           | 서울성동경찰서           |
| 동작           | 서울동작경찰서           |
| 광진           | 서울광진경찰서           |
| 서부           | 서울서부경찰서           |
| 강북           | 서울강북경찰서           |
| 금천           | 서울금천경찰서           |
| 중랑           | 서울중랑경찰서           |
| 강남           | 서울강남경찰서           |
| 관악           | 서울관악경찰서           |
| 강서           | 서울강서경찰서           |
| 강동           | 서울강동경찰서           |
| 종암           | 서울종암경찰서           |
| 구로           | 서울구로경찰서           |
| 서초           | 서울서초경찰서           |
| 양천           | 서울양천경찰서           |
| 송파           | 서울송파경찰서           |
| 노원           | 서울노원경찰서           |
| 방배           | 서울방배경찰서           |
| 은평           | 서울은평경찰서           |
| 도봉           | 서울도봉경찰서           |
| 수서           | 서울수서경찰서           |
+----------------+--------------------------+

police_station.name을 Primary key로 설정

ALTER TABLE police_station
ADD PRIMARY KEY (name);

crime_status 테이블에 Foreign key로 사용할 column 추가

ADD COLUMN reference VARCHAR(16);

FOREIGN KEY 생성

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

FOREIGN KEY 값 UPDATE

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

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;

from.제로베이스 데이터 취업스쿨 강의

SOWA

이전 포스트

44.SQL-10

다음 포스트

45.SQL-11

SQL

🧷 Primary key

🖇️Primary key 생성 문법

🖇️ PRIMARY KEY 삭제 문법

🖇️기존 데이터에 Primary key 속성 추가

🧷 FOREIGN KEY

🖇️ FOREIGN KEY 생성문법

🖇️ FOREIGN KEY 삭제 문법

─ 두 테이블사이의 관계(FOREIGN KEY)설정

from.제로베이스 데이터 취업스쿨 강의

44.SQL-10

46.SQL-12

0개의 댓글