SQL 재활 훈련 6일차 - Constraint, ALTER

0

sql

목록 보기
6/9

Contraints(제약 조건)

UNIQUE constraint

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)
);

phoneUNIQUE라는 것을 볼 수 있다. 이렇게되면 동일한 값을 가질 수 없게 되는 것이다.

좀 더 간단한 예제를 들어보도록 하자.

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'

phoneUNIQUE 제약 사항이 있으므로 같은 값을 가지는 row가 있다면 들어가지 않도록 하는 것이다.

CHECK constraints

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안에 각종 연산자들을 추가하여 나만의 제약 조건들을 만들 수 있다.

NAMED contraints

제약 사항 때문에 발생한 에러들을 보면 다음과 같다. 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 제약 조건이 위배되었다고 나오게 된다. 제약 조건에 이름을 붙이면 테이블 관리가 유용해지고 디버깅이 쉬워진다.

Multi-Column checks

column들을 조합하여 하나의 제약 조건 사항을 만들 수 있다. 가령 회사의 경우 회사 이름, 회사 주소 각각은 고유성이 없지만, 이 둘이 합쳐지면 고유성이 존재한다. 따라서 아래와 같이 name_address 제약 조건을 만들 수 있다.

CREATE TABLE companies (
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    CONSTRAINT name_address UNIQUE (name , address)
);

name_address 제약 조건은 nameaddress 둘을 합쳐 만든 제약 조건으로 nameaddress 둘을 같이 봤을 때 고유해야한다는 것이다. 실제로 데이터를 넣어보도록 하자.

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를 사용할 수 있다. 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 삭제하기

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       |       |
+----------------+--------------+------+-----+---------+-------+

잘 삭제된 것을 볼 수 있다.

table 또는 column 이름 변경

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       |       |
+----------------+--------------+------+-----+---------+-------+

table column 수정

이미 존재하는 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_nameVARCHAR(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를 통한 constraint 추가, 삭제

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)

제약 조건이 사라져 데이터가 잘 들어간 것을 볼 수 있다.

0개의 댓글