table을 만들 때 특정 column에 UNIQUE
제약 조건을 걸 수 있다. UNIQUE
제약 조건이 있다면 특정 column에 고유성이 부여된다.
CREATE TABLE companies (
supplier_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE,
address VARCHAR(255) NOT NULL,
PRIMARY KEY (supplier_id)
);
phone
은 UNIQUE
라는 것을 볼 수 있다. 이렇게되면 동일한 값을 가질 수 없게 되는 것이다.
좀 더 간단한 예제를 들어보도록 하자.
CREATE DATABASE constraints;
USE constraints;
CREATE TABLE contacts (
name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE
);
INSERT INTO contacts (name, phone) VALUES("billyob", '878135');
제대로 데이터가 들어갔는 지 확인해보도록 하자.
SELECT * FROM contacts;
+---------+--------+
| name | phone |
+---------+--------+
| billyob | 878135 |
+---------+--------+
1 row in set (0.00 sec)
phone
column은 UNIQUE
제약사항이 걸려있다. 만약 같은 data를 넣으려고 한다면 어떻게 될까?
INSERT INTO contacts (name, phone) VALUES("gyu", '878135');
ERROR 1062 (23000): Duplicate entry '878135' for key 'contacts.phone'
phone
이 UNIQUE
제약 사항이 있으므로 같은 값을 가지는 row가 있다면 들어가지 않도록 하는 것이다.
CHECK
는 TABLE을 만들 때 직접 column에 제약 조건을 만들 수 있다.
가령, 나이를 의미하는 age
column의 값이 0보다 커야한다면 다음과 같이 쓸 수 있다.
CREATE TABLE users (
username VARCHAR(20) NOT NULL,
age INT CHECK (age > 0)
);
이제 데이터를 넣어보도록 하자.
INSERT INTO users(username, age) VALUES('bluecat', 50);
INSERT INTO users(username, age) VALUES('lion', -3);
마지막 row
를 추가하면 서 다음의 error가 발생할 것이다. ERROR 3819 (HY000): Check constraint 'users_chk_1' is violated.
이는 우리가 만든 CHECK
제약 사항에 의해서 해당 row가 들어가지 않는다는 것을 의미한다.
다음으로 더 복잡한 제약 사항을 만들어보도록 하자. 가령, 앞 뒤가 똑같은 팰린드롭 table을 만들고 싶다고 하자. 아래와 같이 만들 수 있다.
CREATE TABLE palindromes (
word VARCHAR(100) CHECK(REVERSE(word) = word)
);
데이터를 넣어보도록 하자.
INSERT INTO palindromes(word) VALUES('mommy');
INSERT INTO palindromes(word) VALUES('mom');
mom
은 성공하지만 mommy
는 실패하는 것을 볼 수 있을 것이다. 이 처럼 CHECK
안에 각종 연산자들을 추가하여 나만의 제약 조건들을 만들 수 있다.
제약 사항 때문에 발생한 에러들을 보면 다음과 같다. ERROR 3819 (HY000): Check constraint 'palindromes_chk_1' is violated.
제약 사항인 palindromes_chk_1
에 의해서 특정 연산이 실패했다는 것이다. 그런데 palindromes_chk_1
라는 이름은 우리가 붙여준 것이 아니라 시스템이 붙여준 것이다. 따라서 해당 제약 사항에 대한 이름을 만들어주도록 하자.
아래는 user2
table의 age
column이 0보다 크거나 같아야한다는 age_not_negative
제약 사항을 만든 것이다.
CREATE TABLE users2 (
username VARCHAR(20) NOT NULL,
age INT,
CONSTRAINT age_not_negative CHECK (age >= 0)
);
age_not_negative
제약 사항에 위배되는 데이터를 넣어보도록 하자.
INSERT INTO users2(username, age) VALUES('bluecat', -50);
ERROR 3819 (HY000): Check constraint 'age_not_negative' is violated.
age_not_negative
제약 조건이 위배되었다고 나오게 된다. 제약 조건에 이름을 붙이면 테이블 관리가 유용해지고 디버깅이 쉬워진다.
column들을 조합하여 하나의 제약 조건 사항을 만들 수 있다. 가령 회사의 경우 회사 이름, 회사 주소 각각은 고유성이 없지만, 이 둘이 합쳐지면 고유성이 존재한다. 따라서 아래와 같이 name_address
제약 조건을 만들 수 있다.
CREATE TABLE companies (
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
CONSTRAINT name_address UNIQUE (name , address)
);
name_address
제약 조건은 name
과 address
둘을 합쳐 만든 제약 조건으로 name
과 address
둘을 같이 봤을 때 고유해야한다는 것이다. 실제로 데이터를 넣어보도록 하자.
INSERT INTO companies(name, address) VALUES('luigis', '123 spruce');
INSERT INTO companies(name, address) VALUES('luigis', '123 spruce');
ERROR 1062 (23000): Duplicate entry 'luigis-123 spruce' for key 'companies.name_address'
이런 에러가 발생하고 row가 하나만 추가된다.
UNIQUE
와 같이 CHECK
역시도 여러 column들을 활용하여 복잡한 제약 사항을 만들 수 있다.
아래는 houses
table에서는 sale_price
(판매 가격)가 purchase_price
(구입 가격)보다 높아야한다는 CHECK
제약 조건을 만들었다.
CREATE TABLE houses (
purchase_price INT NOT NULL,
sale_price INT NOT NULL,
CONSTRAINT sprice_gt_pprice CHECK(sale_price >= purchase_price)
);
즉, 내가 구입한 가격보다는 비싸게 판매해야한다는 것이다. 실제 data를 넣어보도록 하자.
INSERT INTO houses(purchase_prices, sale_price) VALUES(300, 200);
이렇게 CHECK
제약 사항에 다수의 column들을 조합하여 복잡한 제약 사항을 만드는 것도 가능하다는 것이다.
테이블을 수정하는 방법으로 ALTER
를 사용할 수 있다. column을 추가, 삭제, 수정 할 수 있으며 제약 사항들도 수정이 가능하다.
ALTER
의 기본적인 사용 방법은 다음과 같다.
ALTER TABLE <table-name>
ADD COLUMN <column-name> <TYPE>
ADD
말고도, DROP
, MODIFY
등이 있다.
아래는 우리가 위에서 방금 만들었던 companies
table이다. 참고로 COLUMN
은 생략이 가능하다.
CREATE TABLE companies (
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
CONSTRAINT name_address UNIQUE (name , address)
);
companies
table에 phone
이라는 새로운 column을 추가해보도록 하자.
ALTER TABLE companies
ADD COLUMN phone VARCHAR(15);
phone
column을 추가했다. 제대로 추가되었는 지 확인해보도록 하자.
SELECT * FROM companies;
+--------+------------+-------+
| name | address | phone |
+--------+------------+-------+
| luigis | 123 spruce | NULL |
+--------+------------+-------+
1 row in set (0.00 sec)
phone
column이 추가되었고, 기존의 row에는 NULL
로 값이 들어가있다. 실제로는 NULL이 안들어가도록 기본값을 넣어주는 것이 좋다.
companies
table에 employee_count
column을 추가하되 NULL이 불가능하도록 하자. 이때에는 default 값을 채워주도록 하자.
ALTER TABLE companies
ADD COLUMN employee_count INT NOT NULL DEFAULT 1;
확인해보면 다음과 같다.
SELECT * FROM companies;
+--------+------------+-------+----------------+
| name | address | phone | employee_count |
+--------+------------+-------+----------------+
| luigis | 123 spruce | NULL | 1 |
+--------+------------+-------+----------------+
1 row in set (0.00 sec)
default값이 잘 들어간 것을 볼 수 있다.
column을 삭제할 때도 동일한 ALTER
을 사용한다.
ALTER TABLE <table-name>
DROP COLUMN <column-name>;
DROP
을 사용하고 column이름을 적어주면 된다.
우리가 이전에 companies
table에 추가한 phone
column을 삭제시켜주도록 하자.
ALTER TABLE companies
DROP COLUMN phone;
column이 잘 삭제되었는 지를 확인해보도록 하자.
DESC companies;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name | varchar(255) | NO | PRI | NULL | |
| address | varchar(255) | NO | PRI | NULL | |
| employee_count | int | NO | | 1 | |
+----------------+--------------+------+-----+---------+-------+
잘 삭제된 것을 볼 수 있다.
RENAME
을 사용하면 table 이름도 간단하게 수정이 가능하다.
RENAME TABLE <origin-table-name> TO <new-table-name>;
companies
table의 이름을 suppliers
로 바꿔보도록 하자.
RENAME TABLE companies to suppliers;
잘 수정되었는 지 확인해보도록 하자.
DESC suppliers;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name | varchar(255) | NO | PRI | NULL | |
| address | varchar(255) | NO | PRI | NULL | |
| employee_count | int | NO | | 1 | |
+----------------+--------------+------+-----+---------+-------+
잘 수정된 것을 볼 수 있다. RENAME
은 사실 ALTER
로도 사용이 가능하다. 그러나 ALTER
로 사용하기 보다는 RENAME
으로 사용하는 것이 훨씬 더 간단하고 편하다.
ALTER
를 통해서 suppliers
의 이름을 변경해보도록 하자.
ALTER TABLE suppliers RENAME TO companies;
DESC companies;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| name | varchar(255) | NO | PRI | NULL | |
| address | varchar(255) | NO | PRI | NULL | |
| employee_count | int | NO | | 1 | |
+----------------+--------------+------+-----+---------+-------+
변경이 잘 된 것을 확인할 수 있다.
다음으로 column 이름을 바꿔보도록 하자. 바꾸는 방식은 다음과 같다.
ALTER TABLE <table-name>
RENAME COLUMN <column-name> TO <new-column-name>;
companies
table의 name
column을 company_name
으로 바꿔보도록 하자.
ALTER TABLE companies
RENAME COLUMN name TO company_name;
확인해보도록 하자.
DESC companies;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| company_name | varchar(255) | NO | PRI | NULL | |
| address | varchar(255) | NO | PRI | NULL | |
| employee_count | int | NO | | 1 | |
+----------------+--------------+------+-----+---------+-------+
이미 존재하는 column을 수정해보도록 하자. column의 type부터, contraint 등을 ALTER
를 통해서 수정이 가능하다.
ALTER TABLE <table-name>
MODIFY <colunm-name> ...;
...
에 수정할 type과 constraint들을 넣어주면 된다.
먼저 우리의 companies
table을 보도록 하자.
DESC companies;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| company_name | varchar(255) | NO | PRI | NULL | |
| address | varchar(255) | NO | PRI | NULL | |
| employee_count | int | NO | | 1 | |
+----------------+--------------+------+-----+---------+-------+
company_name
column은 varchar
이고 255
글자이다. 이 값의 크기를 작게 바꿔보도록 하자.
아래는 company_name
을 VARCHAR(100)
으로 바꾸고 default value로 unknown
을 추가한 것이다.
ALTER TABLE companies
MODIFY company_name VARCHAR(100) DEFAULT 'unknown';
위의 SQL문이 잘 적용되었는 지 확인해보도록 하자.
DESC companies;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| company_name | varchar(100) | YES | MUL | unknown | |
| address | varchar(255) | NO | | NULL | |
| employee_count | int | NO | | 1 | |
+----------------+--------------+------+-----+---------+-------+
추가적으로 column 이름도 바꾸고 type도 바꿀 수 있는 CHANGE
명령어가 있다.
ALTER TABLE <table-name>
CHANGE <original-column-name> <new-column-name> VARCHAR(50);
ALTER
를 통해서 이미 생성된 table에 constraint를 추가, 삭제할 수 있다.
먼저 이전에 만들었던 houses
table을 보도록 하자.
CREATE TABLE houses (
purchase_price INT NOT NULL,
sale_price INT NOT NULL,
CONSTRAINT sprice_gt_pprice CHECK(sale_price >= purchase_price)
);
purchase_price
에 대한 제약 조건을 추가하여 purchase_price
가 양수만 들어가도록 하자.
ALTER TABLE houses
ADD CONSTRAINT positive_price CHECK (purchase_price >= 0);
purchase_price
가 양수만 들어가도록 제약 조건을 추가한 것이다. 실제로 데이터를 추가하여 제약조건이 잘 적용되었는 지 보도록 하자.
INSERT INTO houses
VALUES(-1, 4);
ERROR 3819 (HY000): Check constraint 'positive_price' is violated.
이 발생한다. 제약 조건이 잘 적용된 것을 볼 수 있다.
마지막으로 우리가 생성한 제약 조건을 삭제해보도록 하자.
ALTER TABLE houses DROP CONSTRAINT positive_price;
제대로 잘 삭제되었는 지 확인해보도록 하자.
INSERT INTO houses
VALUES(-1, 4);
Query OK, 1 row affected (0.00 sec)
제약 조건이 사라져 데이터가 잘 들어간 것을 볼 수 있다.