[Database] 제약조건 및 흐름제어

제약조건

 

데이터를 입력받을 때 실행되는 검사 규칙

CREATE문으로 테이블을 생성 또는 ALTER 문으로 필드를 추가할 때 설정

제약 조건을 지우면 INDEX도 같이 사라지고, INDEX만 지워도 제약 조건까지 지워짐

 

 

✅  종류

1. NOT NULL

2. PRIMARY KEY -> NOT NULL, UNIQUE 한 테이블당 1개

3. FOREIGN KEY

4. UNIQUE -> 한 테이블에 여러 개 설정 가능

 

 

 

1. NOT NULL

- default 값은 nullable

- not null 제약조건이 설정된 필드는 무조건 데이터를 가지고 있어야 한다.

 

 

 

💻  NOT NULL 실습 

 

post 테이블의 title 컬럼에 NOT NULL 설정하기

기존 post row 중 null인 데이터 삭제하고 진행할 것.

 

DELETE FROM post WHERE title IS NULL;

ALTER TABLE post MODIFY COLUMN title varchar(255) NOT NULL;

 

 

 

 

- AUTO_INCREMENT 키워드와 함께

새로운 레코드가 추가될 때마가 1씩 증가된 값을 저장

내부적인 count 값이 있기 때문에 row를 삭제하고 다시 추가하면 id 값이 증가된(하나 건너뛴) 상태로 입력됨.

id = 1, 2, 3, 4, 5 저장 후 id=5인 row를 삭제하고 다시 row를  추가하면 id=6으로 추가됨.

삭제된다고 id 값이 다시 줄어들지 않음. id=4 다음 데이터는 id=6인 row가 된다.

-> 후에 id=5인 row를 강제로 집어넣으면 들어감.

-> 그렇지만 실무에서는 id가 건너 뛰어 있어도 상관없음. 건너 뛴 row를 접근할 일 X

=> id 설정할 때는 AUTO_INCREMENT 키워드를 설정하자 !

 

 

 

💻  AUTO_INCREMENT 실습 

 

(1) author, post 테이블의 id에 auto_increment로 바꿔보자

 

ALTER TABLE post MODIFY COLUMN id INT AUTO_INCREMENT;

ALTER TABLE author MODIFY COLUMN id INT AUTO_INCREMENT;

-> FK 제약조건 때문에 변경이 안됨

 

 

 

(2) post 테이블에 id 없이 insert, 그 후 insert한 데이터 삭제해보고 다시 id없이 insert

 

 

INSERT INTO post(title, created_time) VALUES('에너지 바 정보', '2023-11-20');

 

 

 

 

DELETE FROM post WHERE id=6;

 

 

 

 

INSERT INTO post(title, created_time) VALUES('단백질 바 정보', '2023-11-20');

 

 

 

 

 

2. UNIQUE 

UNIQUE 제약 조건을 설정하면, 해당 필드는 값이 unique해야함을 의미

 

방법1.

CREATE TABLE 테이블이름 (필드명 필드타임 UNIQUE, ...);

 

방법2.

CREATE TABLE 테이블이름 (필드이름 필드타입, ..., [CONSTRAINT 제약조건이름] UNIQUE (필드이름));

UNIQUE 제약 조건을 별도로 정의하며, 선택적으로 제약 조건에 이름을 부여하는 방법

 

UNIQUE 제약 조건을 설정하며, 해당 필드는 자동으로 인덱스(INDEX) 설정

중복이 적을수록 INDEX가 효율적 => UNIQUE 제약이 있으면 중복이 없기 때문에 굉장히 효율적

SHOW INDEX FROM 테이블명;

제약조건 삭제 시 index 삭제, index 삭제 시 제약조건 삭제

 

 

 

✅  제약조건

-제약조건 추가

방법1. 컬럼 제약조건으로 추가

ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입 UNIQUE

 

방법 2. 테이블 제약조건 추가

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 UNIQUE(필드 이름)

 

- 제약조건 목록조회

 

SELECT * FROM Information_schema.table_constraints WHERE table_schema='board';

SELECT * FROM Information_schema.table_constraints WHERE table_name='author';

 

 

- 제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름;

 

ALTER TABLE author DROP CONSTRAINT email;

 

 

 

 

💻  제약조건 실습1

 

(1) author 테이블 email에 unique 제약조건 추가 -> 컬럼 제약조건으로 추가

 

ALTER TABLE author MODIFY COLUMN email varchar(255) UNIQUE;

 

 

 

 

 

(2) 제약조건 제거 및 index 제거

 

SELECT * FROM Information_schema.table_constraints WHERE table_name='author';

 

 

 

ALTER TABLE author DROP CONSTRAINT email;

 

 

 

(3) 테이블 제약조건 추가형식으로 추가

 

ALTER TABLE author ADD CONSTRAINT email UNIQUE(email);

 

🔒 ON DELETE와 ON UPDATE는 RESTRICT 옵션이 DEFAULT로 걸린다

 

 

 

 

(+)

 

ALTER TABLE author MODIFY COLUMN email varchar(500);

describe author;

 

ALTER TABLE author MODIFY COLUMN email varchar(500) UNIQUE; 상태인 email을

ALTER TABLE author MODIFY COLUMN email varchar(500); 명령어를 통해

UNIQUE 제약 조건을 제거해줘도 제약조건이 사라지지 않음

제약 조건은 제약 조건 조회 & 삭제 명령어를 통해 지우자 !

 

 

3. PRIMARY KEY

- PRIMARY KEY 제약 조건을 가진 컬럼을 기본키(PK)라고 함

NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가진다.

 

- PRIMARY KEY는 테이블당 오직 하나의 필드에만 설정

UNIQUE, NOT NULL은 한 테이블의 여러 필드에 설정 가능

 

- 없던 PK를 설정하기 위한 ALTER문 예제

방법1.

ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입 PRIMARY KEY

방법 2.

ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름 PRIMARY KEY(필드이름)

 

 

4. FOREIGN KEY

CREATE TABLE 테이블이름( 필드이름, 필드타입, ...,

                                                [CONSTRAINT 제약조건이름] FOREIGN KEY(필드이름)

                                                REFERENCES 테이블이름 (필드이름) );

 

- 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할

- 기준이 되는 다른 테이블의 내용을 참조해서 레코드가 입력

- 하나의 테이블을 다른 테이블에 의존하게 만드는 것

- 다른 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이어야 함

 

- 참조되는 테이블(author)에서 수정이나 삭제가 발생할 때 영향

   ON DELETE, ON UPDATE

   기본 값은 delete, update 모두 restrict 옵션이 걸려 있으므로, 변경하고 싶다면 각각 지정 필요

 

- 위 설정 시 동작옵션

   1. CASCADE

   참조되는 테이블에서 데이터를 삭제/수정하면 같이 삭제/수정

   = author 테이블에서 한 row를 수정하면, post 테이블에서 해당 id 값을 참조하는 row의 author_id가 수정한 값으로 변경

 

   2. SET NULL

   참조되는 테이블에서 데이터를 삭제/수정하면 데이터는 NULL로 변경

   = author 테이블에서 한 row를 지우면, post 테이블에서 해당 id 값을 참조하는 row의 author_id가 null로 변경

 

   3. RESTRICT

   FK로 잡은 테이블의 데이터가 남아 있으면, FK 대상 데이터는 수정/삭제 불가

   = post에 author_id가 남아 있으면 author의 

   동작옵션을 주지 않으면 기본은 RESTRICT

 

 

 

💻  제약조건 실습2 

 

(1) POST 테이블에 ON UPDATE CASCADE 설정

먼저, 기존의 foreign key 제약조건을 조회 수 삭제

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='post'

 

 

ALTER TABLE post DROP FOREIGN KEY post_ibfk_1;

 

 

ALTER TABLE post DROP INDEX author_id;

SHOW INDEX FROM author;

 

 

(2) 새롭게 제약조건 추가 ( ON UPDATE CASCADE) ALTER TABLE post ADD CONSTRAINT post_author_fk FOREIGN KEY (author_id) REFERENCES author(id) ON UPDATE CASCADE;

🔒 ON DELETE는 RESTRICT 옵션이 DEFAULT로 걸린다

 

 

--author id를 update 했을 때 post에 author_id가 같이 바뀌었는지 확인--

변경 전 post 테이블

 

첫번째, 두번째 row가 각각 author_id 2, 3을 참조하는 것을 확인할 수 있다.

 

post 테이블의 author_id가 참조하고 있는 author 테이블의 id 값 변경

 

변경 후 post 테이블

 

ON UPDATE CASCADE 옵션으로 author 테이블의 id 값을 변경하면 post의 author_id의 값이 자동으로 변경된다.

 

 

 

💻  제약조건 실습3

 

(1) POST 테이블에 ON DELETE SET NULL ON UPDATE SET NULL 설정

먼저, 기존에 있는 인덱스, 제약조건 모두 제거

 

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='post';

ALTER TABLE post DROP foreign key post_author_fk;

 

 

 

(2) ON DELETE SET NULL, ON UPDATE SET NULL 설정

 

ALTER TABLE post ADD CONSTRAINT post_author_fk1 FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE SET NULL ON UPDATE SET NULL;

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='post';

 

 

(3) 테스트

--author id를 update 했을 때 post의 author_id가 null로 바뀌었는지 확인--

변경 전 author 테이블

변경 후 author 테이블

 

author 테이블의 id 값을 20->2, 30->3으로 변경

 

변경 전 post 테이블

변경 후 post 테이블

 

ON UPDATE SET NULL 옵션으로 author 테이블의 id 값을 변경하면 post의 author_id의 값이 자동으로 null로 변경된다.

ON DELETE SET NULL 삭제도 동일하게 적용됨. 나는 안할거임ㅎㅎ

 

 

4. DEFAULT

데이터를 입력할 때 해당 필드 값을 전달하지 않으면, 자동으로 설정된 기본값을 저장

문법

   CREATE TABLE Test ( ID INT, Name VARCHAR(30) DEFAULT 'null');

시간 세팅시 가장 많이 사용

   ALTER TABLE author ADD create_at DATETIME DEFAULT CURRENT_TIMESTAMP;

 

 

 

✅  INDEX

- INDEX

INDEX 삭제

id 컬럼에 기본키 설정 후 ALTER TABLE .. MODIFY COLUMN id INT 이런 식으로 변경해도

이전에 만들어 졌던 INDEX는 지워지지 않음. 별도로 관리되는 파일

ALTER TABLE 테이블이름 DROP INDEX 인덱스명;

 

 

 

✅  흐름제어

- CASE

CASE와 END로 이루어져 있고, 원하는 조건 내에 존재하지 않으면 ELSE문을 타고, ELSE문이 없을 경우 NULL을 return

 

CASE value

   WHEN [compare_value] THEN result

   WHEN [compare_value] THEN result

   ELSE result

END

 

 

💻  흐름제어 실습1

 

post 테이블에서 id, title, contents, 그리고 author_id의 경우 author_type이라는 이름으로 조회

author_id가 만약 1이면 first_author, 2이면 second_author, 3이상이면 etc_authors로 조회

 

SELECT id, title, contents,

CASE author_id

    WHEN 1 THEN 'First_author'

    WHEN 2 THEN 'Second_author'

    ELSE 'etc_authors'

END AS author_type

FROM post;

 

author 테이블

 

결과

 

(+) 응용

author_id가 만약 1이면 first_author, 2이면 second_author, null이면 anonymous, 그 외는 etc_authors로 조회

 

SELECT id, title, contents,

CASE

    WHEN author_id=1 THEN 'First_author'

    WHEN author_id=2 THEN 'Second_author'

    WHEN author_id IS null THEN 'Anonymous'

    ELSE 'etc_authors'

END AS author_type

FROM post;

 

 

 

 

- IF

IF(a, b, c)

a : 조건

b : 참일 경우 반환 값

c : 거짓일 경우 반환 값

만약 a 조건이 참이면 b를 반환하고, 거짓이면 c를 반환합니다.

ex) SELECT IF(0<1, 'yes', 'no');

 

IFNULL(a, b);

만약 a의 값이 NULL이 아니면 a 그 자체를 반환하고, NULL이면 b를 반환

ex) SELECT id, title, contents, IFNULL(author_id, 'anonymous') FROM post;

 

 

💻  흐름제어 실습2

 

(1) if문법에서 만약 id가 1이면 first_author, 그렇지 않으면 others 출력하시오

id, title, contents, author_type 형식으로 출력

SELECT id, title, contents, IF(author_id=1, 'first_author', 'others') FROM post;

 

(2) SELECT id, title, contents, IFNULL(author_id, 'anonymous') FROM post;의 결과값을 동일하게 if문으로 출력하시오

SELECT id, title, contents, IF(author_id IS NULL, 'anonymous', author_id) FROM post;

 

(3) 프로그래머스

조건에 부합하는 중고거래 상태 조회하기

12세 이하인 여자 환자 목록 출력하기 풀기