CREATE TABLE nodejs.users(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT UNSIGNED NOT NULL,
married TINYINT NOT NULL,
comment TEXT NULL,
created_at DATETIME NOT NULL DEFAULT now(),
PRIMARY KEY(id),
UNIQUE INDEX name_UNIQUE (name ASC))
COMMENT = '사용자 정보'
DEFAULT CHARSET=utf8
ENGINE=InnoDB;
결과
mysql> CREATE TABLE nodejs.users(
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> age INT UNSIGNED NOT NULL,
-> married TINYINT NOT NULL,
-> comment TEXT NULL,
-> created_at DATETIME NOT NULL DEFAULT now(),
-> PRIMARY KEY(id),
-> UNIQUE INDEX name_UNIQUE (name ASC))
-> COMMENT = '사용자 정보'
-> DEFAULT CHARSET=utf8
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.07 sec)
table 정보 확인
mysql> DESC users;
+------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | int(10) unsigned | NO | | NULL | |
| married | tinyint(4) | NO | | NULL | |
| comment | text | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+------------------+------+-----+-------------------+-------------------+
6 rows in set (0.01 sec)
그 뒤, 사용자의 댓글을 저장하는 테이블 생성
아래 코드 입력
CREATE TABLE nodejs.comments (
id INT NOT NULL AUTO_INCREMENT,
commenter INT NOT NULL,
comment VARCHAR(100) NOT NULL,
created_at DATETIME NOT NULL DEFAULT now(),
PRIMARY KEY(id),
INDEX commenter_idx (commenter ASC),
CONSTRAINT commenter
FOREIGN KEY (commenter)
REFERENCES nodejs.users (id)
ON DELETE CASCADE
ON UPDATE CASCADE)
COMMENT = '댓글'
DEFAULT CHARSET=utf8
ENGINE=InnoDB;
결과
mysql> CREATE TABLE nodejs.comments (
-> id INT NOT NULL AUTO_INCREMENT,
-> commenter INT NOT NULL,
-> comment VARCHAR(100) NOT NULL,
-> created_at DATETIME NOT NULL DEFAULT now(),
-> PRIMARY KEY(id),
-> INDEX commenter_idx (commenter ASC),
-> CONSTRAINT commenter
-> FOREIGN KEY (commenter)
-> REFERENCES nodejs.users (id)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE)
-> COMMENT = '댓글'
-> DEFAULT CHARSET=utf8
-> ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.06 sec)
테이블명 확인
mysql> SHOW TABLES;
+------------------+
| Tables_in_nodejs |
+------------------+
| comments |
| users |
+------------------+
2 rows in set (0.00 sec)
