러닝 SQL-(2-2) 데이터베이스 생성과 데이터 추가

간단한 테이블 생성 테스트를 위해 person 테이블과 각 사람이 좋아하는 음식에 대한 favorite_food 테이블을 만들어보도록 하겠습니다.

Person 테이블 생성

(성, 이름, 눈동자색, 생일, 주소) 정보 등이 담긴 person 테이블 생성을 위해서는 아래와 같은 SQL 스키마 문을 입력하면 됩니다.

• 가장 아래에 있는 CONSTRAINT 로 시작하는 절은 해당 테이블의 기본 키로 사용할 열을 데이터베이스 서버에 알려주는 기본 키 제약조건 입니다.
• eye_color 열은 특별히 3가지 종류만 갖도록 제약하기 위해 ENUM 을 활용하여 체크 제약조건을 설정했습니다.

MariaDB [sakila]> CREATE TABLE person
    -> (person_id SMALLINT UNSIGNED,
    ->  fname VARCHAR(20),
    ->  lname VARCHAR(20),
    ->  eye_color ENUM('BR', 'BL', 'GR'),
    ->  birth_date DATE,
    ->  street VARCHAR(30),
    ->  city VARCHAR(20),
    ->  state VARCHAR(20),
    ->  country VARCHAR(20),
    ->  postal_code VARCHAR(20),
    ->  CONSTRAINT pk_person PRIMARY KEY (person_id)
    -> );
Query OK, 0 rows affected, 1 warning (0.023 sec)

테이블 확인

MariaDB [sakila]> desc person;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| person_id   | smallint(5) unsigned | NO   | PRI | NULL    |       |
| fname       | varchar(20)          | YES  |     | NULL    |       |
| lname       | varchar(20)          | YES  |     | NULL    |       |
| eye_color   | enum('BR','BL','GR') | YES  |     | NULL    |       |
| birth_date  | date                 | YES  |     | NULL    |       |
| street      | varchar(30)          | YES  |     | NULL    |       |
| city        | varchar(20)          | YES  |     | NULL    |       |
| state       | varchar(20)          | YES  |     | NULL    |       |
| country     | varchar(20)          | YES  |     | NULL    |       |
| postal_code | varchar(20)          | YES  |     | NULL    |       |
+-------------+----------------------+------+-----+---------+-------+
10 rows in set (0.005 sec)

favorite_food 테이블 생성

• 한 사람이 좋아하는 음식이 두 가지 이상일 수 있기에, person_id와 food 두 개의 기본 키를 갖도록 합니다.
• favorite_food 테이블의 person_id 열의 값에 person 테이블에 있는 값만 포함되도록 가장 아래의 외래 키 제약조건을 설정합니다.

MariaDB [sakila]> CREATE TABLE favorite_food
    ->  (person_id SMALLINT UNSIGNED,
    ->  food VARCHAR(20),
    ->  CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
    ->  CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
    ->  REFERENCES person (person_id)
    -> );
Query OK, 0 rows affected, 1 warning (0.023 sec)

테이블 확인

person_id와 food 모두가 PRIMARY KEY로 설정된 것을 확인할 수 있습니다.

MariaDB [sakila]> desc favorite_food;
+-----------+----------------------+------+-----+---------+-------+
| Field     | Type                 | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO   | PRI | NULL    |       |
| food      | varchar(20)          | NO   | PRI | NULL    |       |
+-----------+----------------------+------+-----+---------+-------+
2 rows in set (0.004 sec)

테이블 수정

숫자 키 데이터 생성

테이블에 데이터를 삽입할 때, 기본 키(person 테이블의 경우 person_id) 값이 자동으로 제공되도록 수정해야합니다. 이 때, 외래 키 제약조건으로 인해 곧바로 수정이 불가능하므로, 아래와 같이 제약 조건을 풀어주고 작업을 수행해야 합니다.

set foreign_key_checks=0; /* 제약조건 해제 */
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT; /* 자동 증가 기능 추가 */ 
set foreign_key_checks=1; /* 제약조건 재설정 */

위 스키마 문을 적용한 후, 다시 테이블을 확인해보면 person_id의 Extra 부분에 auto_increment가 들어있음을 확인할 수 있습니다.

MariaDB [sakila]> desc person;
+-------------+----------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+----------------------+------+-----+---------+----------------+
| person_id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |

데이터 삽입 및 확인

person 테이블에 Harry Porter를 추가해보겠습니다.

MariaDB [sakila]> INSERT INTO person
    -> (fname, lname, eye_color, birth_date)
    -> VALUES ('Harry', 'Porter', 'GR', '1980-07-31');
Query OK, 1 row affected (0.002 sec)

이 때, 주소와 관련된 어떤 열도 값을 제공하지 않았지만 각 열들이 null을 허용하므로 문제없이 저장됩니다. 아래와 같은 명령어를 통해 정상적으로 값이 저장되었는지 확인할 수 있습니다.

MariaDB [sakila]> SELECT person_id, fname, lname, birth_date FROM person;
+-----------+-------+--------+------------+
| person_id | fname | lname  | birth_date |
+-----------+-------+--------+------------+
|         1 | Harry | Porter | 1980-07-31 |
+-----------+-------+--------+------------+
1 row in set (0.001 sec)

Harry Porter가 좋아하는 음식들도 추가해보겠습니다.

MariaDB [sakila]> INSERT INTO favorite_food (person_id, food)
    -> VALUES (1, 'pizza');
Query OK, 1 row affected (0.001 sec)
MariaDB [sakila]> INSERT INTO favorite_food (person_id, food)
    -> VALUES (1, 'cookies');
Query OK, 1 row affected (0.001 sec)
MariaDB [sakila]> INSERT INTO favorite_food (person_id, food)
    -> VALUES (1, 'nachos');
Query OK, 1 row affected (0.001 sec)

알파벳 순서로 Harry Porter가 좋아하는 음식을 조회해보겠습니다.

MariaDB [sakila]> SELECT food
    -> FROM favorite_food
    -> WHERE person_id = 1
    -> ORDER BY food;
+---------+
| food    |
+---------+
| cookies |
| nachos  |
| pizza   |
+---------+
3 rows in set (0.001 sec)

데이터 수정

처음 생성시에 입력하지 않았던 해리포터의 주소를 UPDATE 문을 이용해 추가해보겠습니다.

MariaDB [sakila]> UPDATE person
    -> SET street = '4 Privet Drive',
    -> city = 'Little Whinging',
    -> state = 'Surrey',
    -> country = 'UK',
    -> postal_code = 'WD25 7FD'
    -> WHERE person_id = 1;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

정상적으로 반영되었는지 한 번 확인해보겠습니다.

MariaDB [sakila]> SELECT * FROM person WHERE person_id = 1;
+-----------+-------+--------+-----------+------------+----------------+-----------------+--------+---------+-------------+
| person_id | fname | lname  | eye_color | birth_date | street         | city            | state  | country | postal_code |
+-----------+-------+--------+-----------+------------+----------------+-----------------+--------+---------+-------------+
|         1 | Harry | Porter | GR        | 1980-07-31 | 4 Privet Drive | Little Whinging | Surrey | UK      | WD25 7FD    |
+-----------+-------+--------+-----------+------------+----------------+-----------------+--------+---------+-------------+
1 row in set (0.000 sec)

주의 사항

제약조건에 위배되게 데이터를 생성하거나 수정하려고 할 경우 문제들이 생길 수 있습니다.

고유하지 않은 기본 키

테이블에 이미 person_id=1 인 Harry Porter가 있을 때, 같은 id로 Hermione Granger를 추가하려고 하면 아래와 같이 실패합니다.

MariaDB [sakila]> INSERT INTO person
    -> (person_id, fname, lname, eye_color, birth_date)
    -> VALUES (1, 'Hermione', 'Granger', 'BR', '1979-09-19');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

존재하지 않는 외래 키

존재하지 않는 person_id 에 대해 favorite_food를 추가하려고 하면 아래와 같이 실패합니다.

MariaDB [sakila]> INSERT INTO favorite_food (person_id, food)
    -> VALUES (2, 'chicken');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`sakila`.`favorite_food`, CONSTRAINT `fk_fav_food_person_id` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`))

외래 키 제약조건은 InnoDB 스토리지 엔진을 사용하여 테이블을 생성했을 때만 적용할 수 있다고 합니다.

열 값 위반

person 테이블의 eye_color 처럼 가능한 값이 제한된 열의 경우 다른 값을 지정하려고 하면 아래와 같이 실패합니다. 에러 메세지가 조금 이상하네요.

MariaDB [sakila]> UPDATE person
    -> SET eye_color = 'WH'
    -> WHERE person_id = 1;
ERROR 1265 (01000): Data truncated for column 'eye_color' at row 1

잘못된 날짜 변환

date 열의 값으로 제공하는 문자열이 기본 형식에 어긋날 경우 아래와 같이 실패합니다.

MariaDB [sakila]> UPDATE person
    -> SET birth_date = '07-31-1980'
    -> WHERE person_id = 1;
ERROR 1292 (22007): Incorrect date value: '07-31-1980' for column `sakila`.`person`.`birth_date` at row 1

다른 형식의 값으로 데이터를 업데이트하고 싶을 경우 str_to_date 함수를 아래와 같이 활용하면 됩니다.

MariaDB [sakila]> UPDATE person
    -> SET birth_date = str_to_date('08-31-1980', '%m-%d-%Y')
    -> WHERE person_id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

데이터 삭제

delete 문을 활용하면 해당하는 행을 삭제할 수 있습니다.

MariaDB [sakila]> DELETE FROM person WHERE person_id = 2;
Query OK, 1 row affected (0.001 sec)

테이블 삭제

마지막으로 예시로 만든 테이블을 삭제해보도록 하겠습니다.

MariaDB [sakila]> DROP TABLE favorite_food;
Query OK, 0 rows affected (0.003 sec)
MariaDB [sakila]> DROP TABLE person;
Query OK, 0 rows affected (0.003 sec)

---

참고

이 글 및 이와 관련된 앞으로의 글의 모든 내용은 한빛미디어의 러닝 SQL의 내용을 공부하며 요약한 것입니다.