MySQL 제약조건

제약조건

• 데이터의 무결성을 지키기 위해 제한하는 조건
• 데이터의 무결성 : 데이터에 결함이 없음을 의미
• 고유한 값이 필요한 데이터에 중복값이 발생하면 데이터의 무결성조건을 지키지 못함

제약 조건 종류

PRIMARY KEY 제약조건

• 데이터를 고유한 컬럼으로 구분할 수 있게 해주는 컬럼을 PK로 지정
• PK제약조건은 중복 될 수 없으며 , NULL값이 입력 될 수 없음
• 기본키로 생성한 것은 자동으로 클러스터형 인덱스가 생성됨
• 테이블은 기본키를 1개만 가질 수 있음
• 테이블의 특성을 가장 잘 반영하는 컬럼을 선택하여 PK지정
• 고유한 값 : 회원 아이디, 주민번호, 핸드폰 번호등을 PK로 지정할 수 있음

PK 제약조건 설정방법 1

• PK로 지정할 컬럼의 마지막에 PRIMARY KEY 키워드를 넣어 PK로 지정함

CREATE TABLE 테이블명 
( id  CHAR(8) NOT NULL PRIMARY KEY, 
  name    VARCHAR(10) NOT NULL, 
  height  TINYINT UNSIGNED NULL
);

• DESCRIBE 구문으로 생성된 테이블의 구조 확인

DESCRIBE 테이블명;

PK 제약조건 설정방법 2

• 테이블의 마지막에 PRIMARY KEY 구문과 열이름을 지정해서 PK 지정

CREATE TABLE member 
( id  CHAR(8) NOT NULL, --PK를 지정해 주지 않음
  name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  PRIMARY KEY (id) --테이블의 제일 마지막에 PK(열_이름)을 지정
);

PK 제약조건 설정방법 3

• ALTER TABLE로 테이블 수정
• ADD CONSTRAINT키워드로 제약조건 추가
• PRIMARY KEY로 기본키로 지정하려는 컬럼 지정

-- 제약조건 지정 없이 테이블을 먼정 생성
CREATE TABLE 테이블명 
( id  CHAR(8) NOT NULL, 
  name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
);
-- 생성된 테이블을 ALTER TABLE 구문으로 제약조건 수정
ALTER TABLE 테이블명
-- ADD CONSTARINT는 제약조건을 추가하는 키워드
     ADD CONSTRAINT 
     -- PK로 지정하려는 컬럼을 지정
     PRIMARY KEY (id);

PK 제약조건 별칭

CREATE TABLE user
( id  CHAR(8) NOT NULL, 
  name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  --컬럼에 별칭을 설정
  CONSTRAINT PRIMARY KEY PK_user_id (id)
);

FOREIGN KEY 제약조건

• 두 테이블 사이의 관계를 연결해주고 데이터의 무결성을 보장해주는 역할
• 외래 키가 설정된 열은 꼭 다른 테이블의 기본키와 연결됨
• 기본키가 있는 테이블을 기준 테이블 , 외래 키가 있는 테이블을 참조 테이블 이라고함

FK 제약조건 설정방법 1

• 부모테이블이 존재한다는 가정 하에 FOREIGN KEY () REFERENCES 구문으로 외래 키 지정
• 기준 테이블의 열이 PK 또는 Unique 제약조건을 가지고 있지 않다면 외래 키 관계는 설정되지 않음

CREATE TABLE 테이블명
(  id         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   user_id      CHAR(8) NOT NULL, 
   name     CHAR(6) NOT NULL, 
   -- FORIEGN KEY(컬럼명) REFERENCES 기준_테이블명 (컬럼명)
   FOREIGN KEY(user_id) REFERENCES user(user_id)
);

FK 제약조건 설정방법 2

• ALTER TABLE + ADD CONTRAINT 키워드로 외래 키 설정

CREATE TABLE 테이블명 
(  id         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   user_id      CHAR(8) NOT NULL, 
   name     CHAR(6) NOT NULL
);
ALTER TABLE 테이블명
    ADD CONSTRAINT 
    FOREIGN KEY(user_id) REFERENCES user(user_id);

CASCADE

• 기본 키 - 외래 키 관계가 설정되면 기준 테이블의 열은 변경되거나 삭제되지 않음
• 기준 테이블의 열이 변경되거나 삭제 되면 자동으로 참조 테이블의 데이터도 변경하기 위해 CASCADE문 사용
• ON UPDATE CASCADE 조건 설정시 기준테이블에서 변경된 회원의 이름이 존재 할때 참조 테이블에 존재하는 회원의 이름도 자동으로 변경해줌
• ON DELETE CASCADE 조건 설정시 회원 테이블에 탈퇴한 회원이 발생했을떄 참조 한 테이블에서도 자동으로 해당 회원에 대한 데이터를 삭제함

CREATE TABLE 테이블명 
(  id         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   user_id      CHAR(8) NOT NULL, 
   name     CHAR(6) NOT NULL
);
ALTER TABLE 테이블명
    ADD CONSTRAINT 
    FOREIGN KEY(user_id) REFERENCES user(user_id);
       ON UPDATE CASCADE
       ON DELETE CASCADE;

UNIQUE 제약조건

• 고유 키 제약조건은 중복되지 않는 유일한 값을 입력할 때 사용하는 제약조건
• 기본 키 제약조건과의 가장큰 차이점은 기본 키 제약조건은 NULL을 허용하지 않지만 고유 키 제약 조건은 NULL을 허용함
• 기본 키는 테이블에 1개만 설정해야하지만 고유 키는 여러개를 설정 할 수 있음
• EX) 주민등록번호, 핸드폰 번호 , 이메일 주소, 사원번호등에 사용가능

UNIQUE 제약조건 설정방법

• 테이블 생성시 email 테이블이 NULL을 허용하고 중복된 email 주소가 입력될 수 없도록 제약조건 설정

CREATE TABLE user
( id  CHAR(8) NOT NULL PRIMARY KEY, 
  name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL,
  --NULL을 허용하고, 이메일에 중복된 데이터 없이 이메일이 고유하도록 제약조건 설정
  email       CHAR(30)  NULL UNIQUE
);

CHECK 제약조건

• 입력되는 데이터를 점검하는 기능
• name 컬럼에 정규표현식을 사용해 영문이름은 입력될 수 없도록 CHECK 제약조건 설정

CREATE TABLE user
( id  CHAR(8) NOT NULL PRIMARY KEY, 
  name    VARCHAR(10) NOT NULL CHECK (name ~ '^[a-zA-Z]+$')) , 
  phone1      CHAR(3)  NULL
);

• ALTER TABLE + CHECK + IN 연산자로 입력되는 데이터를 제한 할 수 있음

ALTER TABLE member
    ADD CONSTRAINT 
    CHECK  (phone1 IN ('02', '031', '032', '054', '055', '061' )) ;

DEFAULT 정의

• 값을 입력하지 않았을 떄 자동으로 입력될 값을 미리 지정해 놓는 제약조건

CREATE TABLE user 
( id  CHAR(8) NOT NULL PRIMARY KEY, 
 --name 컬럼에 이름이 입력되지 않았을때 익명 이라는 데이터가 입력되도록 기본 값 설정
  name    VARCHAR(10) NULL DEFALUT '익명', 
  phone      CHAR(3)  NULL
);

ALTER TABLE user
    ALTER COLUMN name SET DEFAULT '익명';

NULL 값 허용

• NULL 값을 허용하려면 NULL을 생략 하거나 NULL을 명시하고, 허용하지 않으려면 NOT NULL 키워드를 사용
• PK가 설정된 컬럼에는 NULL이 존재 할 수 없으므로 생략하면 자동으로  NOT NULL로 설정됨
• NULL값은 '아무 것도 없다'의 의미 공백과 0과는 다름
• 집계함수 사용시 NULL이 존재하는 컬럼을 제외하고 집계
• +,-,*,/ 등의 연산에도 NULL이 존재하면 어떠한 연산과는 상관없이 NULL값을 리턴함