[MySQL] 제약조건 | 식별자, 기본키, 복합키, 유니크, 체크

 

---

 

1.  식별자

​

🍋 테이블의 특정 레코드를 읽거나 변경하려면 레코드끼리 구분할 수 있는 고유의 키 (=식별자)가 필요

🍋 키는 1) 값이 꼭 있어야 하며, 2) 구분을 위해 고유값을 가져야 함

 

적합한 키	주민등록번호, 학번, 사번, IP, ISBN
부적합한 키	키, 몸무게, 제품 색상, 직급
애매한 키	이름, 핸드폰, 이메일

 

🍋 조건을 만족하는 필드를 후보키 Candidate Key라고 하며 한 테이블에 여러 개의 후보키가 있음

🍋 이 중 레코드를 가장 잘 대표하는 키 하나를 골라 기본키 Primary Key (PK)로 선정

​

  💡 기본키가 충족해야 할 조건

       1)  대표성 : 레코드를 상징하는 값이어야 한다.

       2)  자주 참조하는 속성 : 기본키에는 기본적으로 인덱스가 생성되어 검색 효율이 좋다.

       3)  가급적 짧은 속성 : 테이블 간의 연결고리가 되므로 비교 속도가 빨라야 한다.

​

INSERT INTO tCity VALUES ('춘천',1116,27,'n','강원');

 

  📌  iCity는 도시명인 name이 기본 키이며, 위의 쿼리문은 에러가 생김.

  📌  춘천이 테이블에 이미 있는데 또 삽입하면 어떤 레코드가 진짜 춘천에 대한 정보인지 구분 x

         ➡️ 모호함이 생기면 무결성이 깨지므로 기본키에 대해서는 중복을 허락하지 않음

​

​

🍋 DBMS는 기본키를 특별하게 관리한다. NULL 금지와 중복 방지는 물론이고 인덱스를 생성하여 검색 속도를 높임.

🍋 기본키는 검색시 조건문에 활용하며 테이블간의 관계를 구성하는 연결고리로 사용

​

 

💡 테이블에 지정된 키를 보려면 다음 쿼리문을 입력,실행

 

SHOW KEYS FROM 테이블이름;

 

---

 

2.  기본키 설정

​

👩🏻‍💻 제약을 선언하는 위치에 따라 컬럼 제약과 테이블 제약이 있다.

👩🏻‍💻 컬럼 제약은 컬럼 선언 뒤에 위치하며 테이블 제약은 모든 컬럼 선언이 끝난 후 마지막 위치에 옴.

 

CREATE TABLE 테이블 ( 
   필드 선언, <- 이 위치에 오면 컬럼 제약 
   필드 선언, 
   필드 선언, 
            <- 이 위치에 오면 테이블 제약 
)

 

 

⚡️ NULL 허용 여부나, 기본값 등 컬럼에 대한 속성은 컬럼 제약으로 지정.

⚡️ 기본키는 컬럼 제약으로 선언할 수 도 있고 테이블 제약으로 선언할 수도 있음.

​

 

💡 각 제약의 형식

    * 컬럼 기본키 제약 : [CONSTRAINT 이름] PRIMARY KEY

    * 테이블 기본키 제약 : [CONSTRAINT 이름] PRIMARY KEY(대상 필드)

​

⚡️ PRIMARY KEY 제약은 NOT NULL 속성을 겸함.

⚡️ 제약의 이름을 생략하면 서버가 자동으로 이름을 붙임.

 

CREATE TABLE tCity ( 
  name CHAR(10), 
  area INT NULL , 
  popu INT NULL , 
  metro CHAR(1) NOT NULL, 
  region CHAR(6) NOT NULL, 
  CONSTRAINT PK_tCity_name PRIMARY KEY(name) 
 );

 

---

 

3.  복합키

​

👩🏻‍💻 기본키 필드가 꼭 하나여야 한다는 법은 없으며 하나의 필드만으로 레코드를 특정하기 어려운 경우가 있다.

👩🏻‍💻 이런 경우 두 개 이상의 필드를 묶어 기본키로 지정. 이것을 복합키 Composite Key라고 한다.

​

  ⚡️ tCity 테이블의 경우, 도시명이 중복되지 않는다는 가정을 하고 있지만 현실은 다름

  ⚡️ 경기도 광주, 전라도 광주 처럼 도시 이름이 중복되는 경우가 있는데 tCity 테이블의 구조로는 두 도시 모두 입력할 수 없음

         ➡️ 이름만으로 도시를 특정할 수 없으니 지역과 함께 묶어서 기본키로 정의해야 함.

 

CREATE TABLE tCity ( 
   name CHAR(10) PRIMARY KEY, 
   region CHAR(6) PRIMARY KEY, 
   area INT NULL , 
   popu INT NULL , 
   metro CHAR(1) NOT NULL 
);

 

    📌  name, region 두 개의 필드에 PRIMARY KEY를 지정하면 에러가 남 (테이블당 기본키는 하나만 지정 가능)

    📌  복합키로 지정할 때는 테이블 제약으로 기본키를 지정하며 괄호 안에 필드 목록을 콤마로 구분하여 나열한다.

 

CREATE TABLE tCityCompoKey ( 
  name CHAR(10) NOT NULL, 
  region CHAR(6) NOT NULL, 
  area INT NULL , 
  popu INT NULL , 
  metro CHAR(1) NOT NULL, 
  CONSTRAINT PK_tCity_name_region PRIMARY KEY (name, region) 
);

INSERT INTO tCityCompoKey VALUES ('광주', '전라', 123, 456,'y'); 
INSERT INTO tCityCompoKey VALUES ('광주', '경기', 123, 456,'n');

 

​

📌 겹치는 name 이 있어도 에러없이 정상적으로 실행

📌 복합키도 중복값을 허용하지 않지만 복합키를 구성하는 개별 키는 중복해도 무방

       ➡️  두 필드가 동시에 같지만 않으면 됨.

​

​

 

---

 

4.  유니크

​

👩🏻‍💻 유니크 UNIQUE 제약은 필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 강제

👩🏻‍💻 유니크는 기본키를 보조하는 중복 방지 제약

​

 💡 기본키 제약과의 차이점

      1)  기본키는 NULL을 허용하지 않지만 유니크는 NULL을 허용

            ➡️  단 NULL끼리도 중복 할 수 없어 딱 하나의 NULL만 존재할 수 있음

      2)  UNIQUE와 NOT NULL을 동시에 지정하면 기본키와 유사해짐.

            ➡️  기본키는 테이블당 하나만 지정할 수 있지만 유니크는 개수에 상관없이 얼마든지 지정 가능

      3)  기본키는 자동으로 인덱스를 생성하여 레코드의 정렬 순서를 결정하지만 유니크는 그렇지 않다

             ➡️  인덱스를 생성하더라도 기본키의 인덱스와는 종류와 효율이 다름

​

  📌  만일 tCity 테이블이 도시끼리 인구수가 같아서는 안된다는 규칙이 있다면 popu필드에 대해 UNIQUE 제약을 설정

 

CREATE TABLE tCityUnique ( 
   name CHAR(10) PRIMARY KEY, 
   area INT NULL , 
   popu INT UNIQUE NULL, 
   metro CHAR(1) NOT NULL, 
   region CHAR(6) NOT NULL 
);

 

  📌  이 테이블의 도시끼리는 인구가 같아서는 안됨. NULL은 가능하지만 하나만 가능.

  📌  NULL을 허가하지 않으려면 UNIQUE NOT NULL로 지정. 두 개 이상의 키를 묶어 복합 UNIQUE 제약을 걸 수도 있음

 

CREATE TABLE tCityUnique ( 
   name CHAR(10) PRIMARY KEY, 
   area INT NULL , 
   popu INT NULL, 
   metro CHAR(1) NOT NULL, 
   region CHAR(6) NOT NULL, 
   CONSTRAINT Unique_tCity_area_popu UNIQUE(area, popu) 
 );

  

  📌  이 테이블의 도시는 이름이 고유해야 하며 area와 popu가 모두 같아서는 안됨. 둘 중 하나라도 달라야 함.

​

 

---

 

5.  체크

​

👩🏻‍💻  체크 제약은 필드의 값 종류를 제한

👩🏻‍💻  모든 속성은 유의미한 범위가 있고 상식적으로 가능한 값과 그렇지 않은 값이 있음.

       ➡️ 예를 들어 도시가 아무리 거대해도 인구 100억을 넘길 수는 없고 면적이 음수가 될 수 없음.

       ➡️ 체크 제약은 이런 무의미한 값을 걸러냄

👩🏻‍💻 타입은 물리적인 형식을 점검하는데 비해 체크는 논리적인 값의 형식을 점검

​

  ⚡️ 필드 선언문에 CHECK 키워드와 함께 필드값으로 가능한 값을 조건문으로 지정

  ⚡️ WHERE 절의 조건을 지정하는 모든 문법을 쓸 수 있다.

 

CREATE TABLE tCheckTest ( 
   gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'), 
   grade INT NULL CHECK(grade >= 1 AND grade <= 3), 
   origin CHAR(3) NULL CHECK(origin IN ('동','서','남','북')), 
   name CHAR(10) NULL CHECK(name LIKE '김%') 
);

 

 

  📌  각 필드에 조건문으로 체크 제약을 지정

  📌  성별을 저장하는 gender 필드는 '남', 아니면 '여'만 가능

         ➡️ gender가 둘 중 하나임을 명시하여 이 두 값 이외에는 입력을 금지

  📌  grade는 중고등학교의 학년을 표현하는데 1 ~ 3까지만 유효

         ➡️ 초등학교라면 6까지, 대학교라면 4까지 범위를 늘이면 됨.

         ➡️ 일정 범위내일 때는 최소값, 최대값을 지정하는 대신 BETWEEN AND 조건문이 편리

              ex. grade INT NULL CHECK (grade BETWEEN 1 AND 3)

  📌  origin 필드는 방위를 나타내는데 동서남북 넷 중 하나만 가능

         ➡️ 여러 개의 임의값 중 하나를 지정할 때는 IN 연산자가 편리

  📌  name 필드는 성씨가 김씨인 경우만 받아들임

         ➡️ 부분 문자열을 점검할 때는 LIKE 연산자를 사용

 

 

// 에러 없이 실행 가능한 쿼리문들 
INSERT INTO tCheckTest (gender) VALUES ('여'); 
INSERT INTO tCheckTest (grade) VALUES (1); 
INSERT INTO tCheckTest (origin) VALUES ('동'); 
INSERT INTO tCheckTest (name) VALUES ('김좌진');

// 제약조건을 위반하여 에러 처리가 되는 쿼리문들 
INSERT INTO tCheckTest (gender) VALUES ('노'); 
INSERT INTO tCheckTest (grade) VALUES (0); 
INSERT INTO tCheckTest (origin) VALUES ('중'); 
INSERT INTO tCheckTest (name) VALUES ('청산리');

 

  ⚡️ 삽입할 때 뿐만 아니라 UPDATE할 때도 체크 제약 조건을 점검

 

UPDATE tCheckTest SET grade = 4 WHERE grade IS NOT NULL;

 

  📌 4학년은 체크 조건에 위배되어 아무 레코드도 갱신하지 않음

 

// region 필드는 반드시 체크 제약 조건에 지정한 지역 중의 하나여야 한다. 
region CHAR(6) NOT NULL CHECK (region IN ('경기', '충청', '강원','경상', '전라', '제주'));
INSERT INTO tCity VALUES ('울릉',72,1,'n','우산'); // 이 문장은 에러처리

 

  📌 '우산'이라는 지역명을 인정하지 않기 때문에 에러

  📌 서버는 레코드를 삽입, 변경할 때마다 제약 조건을 점검하며 클라이언트에서 무슨 짓을 하더라도 이 계약 조건을 어길 수 없음

        ∴  따라서 tCity에 있는 모든 도시는 6개 지역 중 하나에 소속됨을 보장 할 수 있음

​

​

​

​

​

​

[ 내용 참고 : IT 학원 강의 ]