💡 CONSTRAINT : 생략가능
ALTER TABLE tablename
DROP PRIMARY KEY;
💡 하나의 컬럼, 여러개의 컬럼 모두 삭제방법 동일
: 이미 만들어진 테이블에 PRIMARY 속성 추가
ALTER TABLE tablename ADD PRIMARY KEY (col1, col2,..);
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> ALTER TABLE person ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| type | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE animal ADD CONSTRAINT PK_animal PRIMARY KEY(name, type);
Query OK, 0 rows affected (0.06 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 | |
+-------+-------------+------+-----+---------+-------+
REFERENCES
mysql> CREATE TABLE orders(oid int not null,
-> order_no varchar(16),
-> id int,
-> PRIMARY KEY (oid),
-> CONSTRAINT FK_person FOREIGN KEY (id) REFERENCES person(id));
mysql> CREATE TABLE job(
-> jid int not null,
-> name varchar(16),
-> id int,
-> PRIMARY KEY (jid),
-> FOREIGN KEY (id) REFERENCES person(id));
Query OK, 0 rows affected (0.02 sec)
mysql> DESC job;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| id | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
💡 CONSTRAINT 자동생성 시 확인 문법
SHOW CREATE TABLE tablename;
mysql> show create table job;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| job | CREATE TABLE `job` (
`jid` int NOT NULL,
`name` varchar(16) COLLATE utf8mb3_bin DEFAULT NULL,
`id` int DEFAULT NULL,
PRIMARY KEY (`jid`),
KEY `id` (`id`),
CONSTRAINT `job_ibfk_1` FOREIGN KEY (`id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
: 한 테이블 내 여러개 있을 수 있으므로 constraint 같이 실행
ALTER TABLE tablename DROP FOREIGN KEY FK_constraint'
: 테이블 생성 이후에도 ALTER TABLE을 통해 FOREIGN KEY 지정 가능
ALTER TABLE tablename ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);
mysql> ALTER TABLE orders ADD FOREIGN KEY (id) REFERENCES person(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)