제약조건(constraint)

• 데이터를 입력받을 때 실행되는 검사 규칙
• CREATE문으로 테이블을 생성 또는 ALTER문으로 필드를 추가할 때 설정

• 종류
  • NOT NULL
  • PRIMARTY KEY → NOT NULL, UNIQUE 특성을 가지고 있고 한 테이블 당 1개만 가능
  • FOREIGN KEY
  • UNIQUE → 한 테이블에 여러개 설정가능

---

NOT NULL

• default값은 nullable
• not null제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 한다

  ex) CREATE TABLE author(id INT NOT NULL name VARCHAR(30),...);

• AUTO_INCREMENT 키워드와함께
  • 새로운 레코드가 추가될 때마다 1씩 증가된 값을 저장

    ex) ALTER TABLE author MODIFY COLUMN id INT AUTO_INCREMENT;

• auto_increment는 컬럼에 적용하면 중간에 값을 삭제했을때, 삭제된 숫자부터 시작하는 것이 아니라 그 다음 숫자부터 진행된다.
• ex) 1,2,3,4,5 중에 3을 삭제하고 다시 새로운 레코드를 생성했을 때, 3이 생성되는게 아니라 6이 생성된다.

---

UNIQUE

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

  방법1
  CREATE TABLE 테이블이름 (필드명 필드타입 UNIQUE,...);

  방법2
  CREATE TABLE 테이블이름 (필드이름 필드타입, ... [CONSTRAINT 제약조건이름] UNIQUE(필드이름));
  UNIQUE 제약 조건을 별도로 정의하며, 선택적으로 제약 조건에 이름을 부여하는 방법

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

  show index from 테이블명;

  • index 삭제

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

  • 제약조건 삭제시 index도 같이 삭제, index 삭제시 제약조건도 같이 삭제

---

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 (필드이름)

---

FOREIGN KEY

• 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할
  • 기준이 되는 다른 테이블의 내용을 참조해서 헤코드가 입력
  • 하나의 테이블을 다른 테이블에 의존하게 만드는 것
  • 다른 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약조건이어야 함

CREATE TABLE 테이블 이름 (필드이름 필드타입, ..., [CONSTRAINT 제약조건이름] FOREIGN KEY(필드이름)
REFERENCES 테이블이름(필드이름) [ON DELETE/UPDATE CASCADE]);

• 참조되는 테이블에서 데이터의 수정이나 삭제가 발생시 영향
  • ON DELETE
  • ON UPDATE
  • 기본값은 delete, update 모두 restrict옵션이 걸려 있으므로, 변경하고 싶다면 각각 지정 필요

• 위 설정시 동작옵션
  • CASCADE
    • 참조되는 테이블에서 데이터를 삭제/수정하면 같이 삭제/수정
  • SET NULL
    • 참조되는 테이블에서 데이터를 삭제/수정하면 데이터는 NULL로 변경
  • RESTRICT
    • fk로 잡은 테이블의 데이터가 남아 있으면, fk대산 데이터 수정/삭제 불가
    • 동작옵션을 주지 않으면 기본은 RESTRICT

ON UPDATE CASCADE

ex) ALTER TABLE POST ADD CONSTRAINT post_author_fk FOREIGN KEY(author_id) REFERENCES author(id) ON UPDATE CASCADE;

• CASCADE는 종속된다는 뜻으로 update할때, 참조하는 컬럼의 값이 변경될 떄, 종속되어 있는 외래 키도 값이 동일하게 바뀐다.

ON DELETE CASCADE

• DELETE도 UPDATE와 사용하는 방식은 똑같다.
• delete할 때, cascade하면 참조하는 컬럼의 값이 삭제될 때, 해당되는 외래키의 값이 같이 변경된다.

그외 다른 예시

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

• 외래키가 참조하는 컬럼의 값이 삭제 되거나 변경되었을 때, null값으로 변경된다.

---

DEFAULT

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

  ex) CREATE TABLE test (ID INT, NAME CARCHAR(30), DEFAULT'sim');

• 시간 세팅시 가장 많이 사용

  ex) ALTER TABLE author ADD create_at DATETIME DEFAULT CURRENT_TIMESTAMP;
  ex) ALTER TABLE post ADD create_at DATE TIME DEFAULT CURRENT_TIMESTAMP;

---

흐름제어

CASE

CASE value
WHEN [compare_value] THEN result
WHEN [compare_value] THEN result ...
ELSE result
END

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

ex) SELECT ID
CASE NAME
WHEN '홍길동'
...
END,
email FROM author;

---

흐름제어 -if문

IF문

• IF(a, b, c)

• a는 조건, b는 참일 경우 반환값, c는 거짓일 경우 반환값

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

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

  ex) SELECT id, title, contents,
  IF(author_id = 1, 'Author One', 'Other Author')
  AS author_name
  FROM posts;

IFNULL(a,b)

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

  ex) SELECT id,
  IFNULL(title, 'No Title')
  AS post_title, contents, author_id
  FROM posts;