[MariaDB] 제약조건

제약조건(constraint)

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

• CREATE 문으로 테이블을 생성 또는 ALTER 문으로 필드를 추가할 때 설정
  • NOT NUL
  • UNIQUE
  • PRIMARY KEY → NOT NULL, UNIQUE, 한 테이블당 1개
  • FOREIGN KEY

제약조건-NOT NULL

• defalut값은 nullable

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

• CREATE TABLE author (

  id INT NOT NULL,

  name VARCHAR(30),

  …);

• ALTER문을 써서 post의 title을 not null 조건으로 바꿔보자
  • 이미 NULL인 데이터는 삭제

  

  기존 테이블 정보

변경 후

• AUTO_INCREMENT
  • 새로운 레코드가 추가될 때마다 1씩 증가된 값을 저장
  • 적용 후 마지막 인덱스(예:7)을 삭제후 다시 로우를 삽입하면 7로 채워지지 않고 8로 들어옴. (내부에서 따로 COUNT하기 때문)

  ALTER TABLE post MODIFY COLUMN id INT AUTO_INCREMENT;
  INSERT INTO post (title) values ('오토');
  select * from POST;

  

  id 없이 넣어도 자동으로 채워줌

제약조건 - UNIQUE

• 해당 필드는 값이 unique해야 함을 의미

• CREATE TABLE 테이블이름 (필드명 필드타입 UNIQUE,…);

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

• UNIQUE 제약 조건을 설정하면, 해당 필드는 자동으로 인덱스(INDEX)로 설정
  • show index from 테이블명; - 인덱스 조회
  • index 삭제 : ALTER TABLE 테이블이름 DROP INDEX 인덱스명;
  • 제약조건 삭제시 index도 함께 삭제됨, 제약조건 삭제 시 index도 삭제됨

• NULL값 입력 가능

실습

• UNIQUE ALTER문
  • 방법 1(컬럼 제약조건)

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

  • 방법 2(테이블 제약조건 - 별도의 제약조건이름 옵션)

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

• 제약조건 제거
  • 제약조건 목록 조회
  • ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름;

• author 테이블 email에 unique 제약 조건 추가
  • 컬럼 제약조건으로 추가

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

  

  • 제약조건 제거 및 index 제거 (함께 제거됨)

  SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'author'; -- 제약조건명 조회

  

  CONSTRAINT_NAME 확인

  ALTER TABLE author DROP CONSTRAINT email;
  SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'author';

  

  email이 삭제된 것을 확인

  • 테이블 제약조건 추가형식으로 추가

  ALTER TABLE author ADD CONSTRAINT email_unique UNIQUE(email);
  SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'author';
  describe author;
  SHOW INDEX FROM author;

  

  email_unique 제약조건이 생성됨

  

  email 속성에 UNIQUE 제약조건이 설정됨

  

  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 WPDIRWHRJSDLDJDI GKA

• CREATE TABLE 테이블이름 {

  필드이름 필드타입,

  …,

  [CONSTRAINT 제약조건이름]

  FOREIGN KEY (필드이름) REFERENCES 테이블이름(필드이름) [ON DELETE/UPDATE CASCADE]);

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

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

제약조건 - ON UPDATE CASCADE

• 외래키 제약조건에서 ON UPDATE CASCADE 등의 옵션

• POST 테이블에 ON UPDATE CASCADE 설정
  • 먼저, 기존의 foreign key제약조건을 조회 후 삭제
    • SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'post';

    

    • ALTER TABLE post DROP CONSTRAINT post_ibfk_1;
    • ALTER TABLE post DROP INDEX post_ibfk_1;
  • 새롭게 제약조건 추가
    • ALTER TABLE post ADD CONSTRAINT post_author_fk FOREIGN KEY (author_id) REFERENCES author(id) ON UPDATE CASCADE;
  • 테스트
    • 삭제
      • 삭제 불가 → ON DELETE는 CASCADE 적용 안했기 때문
    • 수정
      • author 테이블의 id 수정 시 post 테이블의 id도 같이 수정되는지 확인

    

    post 테이블

    

    author 테이블

    post의 author_id는 각각 author의 id 1, 2번을 참조하고 있음

    → author의 id를 바꾸면 post의 author_id도 바뀌어야 함

    

    author테이블 id 변경

    

    post의 author_id도 변경됨을 확인

제약조건 - ON DELETE CASCADE 실습

• 외래키 제약조건에서 ON DELETE CASCADE 등의 옵션
  • ON DELETE CASCADE 테스트
    • 삭제 시
      • author 삭제 시 post 같이 삭제
    • 수정 시
      • author id 수정 불가 - ON UPDATE는 restrict기 때문

• ON DELETE SET NULL, ON UPDATE SET NULL 또한 동일 방법으로 테스트

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'post';
ALTER TABLE post DROP CONSTRAINT post_author_fk; -- 위에서 조회한 Constraint_NAME
SHOW INDEX FROM post;
ALTER TABLE post DROP INDEX post_author_fk; -- CONSTRAINT_NAME과 같음
ALTER TABLE post ADD CONSTRAINT post_author_fk2 FOREIGN KEY (author_id) REFERENCES author(id) ON UPDATE SET NULL ON DELETE SET NULL;

SELECT * FROM post;
SELECT * FROM author;

post

author

author를 20을 지우면 post id가 1인 author_id는 null이 될 것

null이 됨을 확인

제약조건 - DEFAULT

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

• CREATE TABLE Test {

  id INT,

  name VARCHAR(30) DEFAULT ‘Anoymous’);

• 시간 세팅 시 가장 많이 사용
  • ALTER TABLE author ADD create_at DATETIME DEFAULT CURRENT_TIMESTAMP;
  • ALTER TABLE post ADD create_at DATETIME DEFAULT CURRENT_TIMESTAMP;

Uploaded by N2T