PRIMARY KEY에 대해 알아보자 (feat. CANDIDATE KEY, COMPOSITE KEY, UNIQUE)

안녕하세요, 끙정입니다.

 

오늘은 기본키, PRIMARY KEY에 대해 알아보겠습니다.

무결성을 지키기 위해서는 레코드가 중복되면 안됩니다.

그러기 위해서는 레코드 고유의 값이 필요합니다.

바로 기본키입니다.

 

1. 식별자

테이블의 특정 레코드를 읽거나 변경하려면 레코드끼리 구분할 수 있는 고유의 키가 필요합니다.

이러한 고유의 키가 될 수 있는 조건은

 

1) 값이 꼭 존재해야 하며

2) 구분을 위한 고유값을 가져야 합니다.

 

예를 들어 어떤 테이블에 아래와 같은 필드 목록이 있다고 가정합니다.

주민등록번호, 사번, 몸무게, 키, 직급, 이름, 핸드폰, 이메일.

이러한 필드 목록 중에서 어떤 필드를 기본키로 결정할지는 사용자의 선택이지만,

그 중에서 가장 적합한 것을 골라야 합니다.

 

적합한 키	주민등록번호, 사번
부적합한 키	몸무게, 키, 직급
애매한 키	이름, 핸드폰, 이메일

 

주민등록번호나 사번은 모든 사람이 고유한 값을 가지기 때문에 적합합니다.

몸무게나 키, 직급은 동일한 값을 가지는 사람이 많기에 기본키가 될 수 없습니다.

이름 또한 중복값이 존재할 수 있기 때문에 불가능하고,

핸드폰과 이메일은 고유한 값이긴 하지만 핸드폰과 이메일이 없는 사람도 있을 수 있으며,

값이 바뀌는 경우가 있기 때문에 애매합니다.

 

주민등록번호, 사번처럼 조건을 만족하는 필드를 후보키 CANDIDATE KEY 라고 합니다.

이 중 레코드를 가장 잘 대표하는 키 하나를 골라 기본키 PRIMARY KEY로 선정합니다.

짧게 줄여 PK라고 부르겠습니다.

 

PK는 물리적인 조건 외에도 다음 요건을 만족해야 합니다.

 

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

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

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

 

PK가 중복되는 레코드를 삽입해보겠습니다.

 

/* PK가 중복되는 레코드를 삽입해보자. */
INSERT INTO tCity VALUES ('춘천', 1116, 27, 'n', '강원');

 

PK제약조건 위반이라고 친절하게 설명해준다.

 

2. 기본키 PRIMARY KEY 지정

제약을 선언하는 위치에 따라 컬럼 제약과 테이블 제약이 있습니다.

컬럼 제약은 컬럼 선언 뒤에 위치하며,

테이블 제약은 모든 컬럼 선언이 끝난 후 마지막 위치에 옵니다.

 

CREATE TABLE 테이블명
(
    필드 선언, /* 이 위치에 오면 컬럼 제약 */
    필드 선언,
    필드 선언,
    /* 이 위치에 오면 테이블 제약 */
);

 

NULL 허용 여부나 기본값, 체크 등 컬럼에 대한 속성은 컬럼 제약으로 지정합니다.

기본키는 컬럼 제약으로 선언할 수도 있고 테이블 제약으로 선언할 수도 있습니다.

 

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

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

 

PK제약은 NOT NULL 속성을 겸합니다.

기본키는 당연히 NULL을 허용하지 않으므로 굳이 NOT NULL을 붙일 필요는 없습니다.

물론 붙여도 상관은 없습니다.

 

PK와 NULL을 같이 쓰는 것은 논리적으로 말이 되지 않습니다.

이 경우 오라클은 NOT NULL을 강제 적용하며,

SQL Server는 에러가 뜹니다.

 

 

제약도 추가, 변경, 삭제 간으한 편집 대상이기 때문에 이름이 있어야 합니다.

제약의 이름을 생략하면 서버가 자동으로 이름을 붙입니다.

그러나 굉장히 길고 복잡한 이름이 붙여지기 때문에 원하는 이름을 붙이는 것이 수월합니다.

 

CREATE TABLE tPK
(   /* PK_tPK_name 이라는 이름을 주었다. */
	name CHAR(10) CONSTRAINT PK_tPK_name PRIMARY KEY,
	phone CHAR(10) NOT NULL
);

 

테이블 제약을 주고자 할 때는 아래와 같이 쿼리를 날리면 됩니다.

 

CREATE TABLE tPK
(
	name CHAR(10), /* 테이블 제약이 들어가기 때문에 별다른 제약을 주지 않는다. */
	phone CHAR(10) NOT NULL,
        CONSTRAINT PK_tPK_name PRIMARY KEY (name)
);

 

필드 선언문에 PK를 지정해주는 컬럼 제약이 가장 간단한 방법이지만,

여러 필드를 묶어 복합키로 지정하거나 외래키를 지정할 때는 더 범용적인 문법이 필요합니다.

따라서 테이블 제약도 반드시 알아 두어야 합니다.

 

3. 복합키 COMPOSITE KEY

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

예를 들면 도시 이름을 PK로 가지는 tCity 테이블이 있다고 가정할 때,

'광주' 라는 도시 이름이 과연 경기도 광주인지, 전라도 광주인지 알 수 없습니다.

이때 무결성은 깨지게 됩니다.

 

이럴때 사용하는 것이 복합키 COMPOSITE KEY입니다.

그래서 name과 함께 region을 복합키로 지정하고자 합니다.

 

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

 

그러나 위 코드는 에러가 발생합니다.

복합키는 두 개의 필드를 묶어서 기본키로 지정하는 것이지 기본키가 두 개인 것은 아닙니다.

테이블당 기본키는 무조건 하나만 지정할 수 있습니다.

 

두 개 이상의 필드를 복합키로 지정할 때는 테이블 제약으로 가능하며,

괄호 안에 필드 목록을 콤마로 구분하여 나열합니다.

 

CREATE TABLE tCity
(
    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)
);

 

이제 tCity 테이블은 name이 중복된 레코드도 존재할 수 있고, region이 중복된 레코드도 존재할 수 있지만,

name과 region이 모두 같은 레코드는 존재할 수 없습니다.

 

4. 유니크 UNIQUE

유니크 UNIQUE 제약은 필드의 중복값을 방지하여 모든 필드가 고유한 값을 가지도록 강제합니다.

PK 제약과 유사하지만 몇 가지 차이점이 있습니다.

 

1) PK는 NULL을 허용하지 않지만 유니크는 NULL을 허용한다. 단 NULL끼리도 중복할 수 없어 딱 하나의 NULL만 존재할 수 있다.

2) UNIQUE와 NOT NULL을 동시에 지정하면 PK와 유사해진다. 그러나 PK는 테이블당 하나만 지정할 수 있지만 유니크는 개수에 상관없이 얼마든지 지정할 수 있다.

3) PK는 자동으로 인덱스를 생성하여 레코드의 정렬 순서를 결정하지만 유니크는 그렇지 않다. 인덱스를 생성하더라도 PK의 인덱스와는 종류와 효율이 다르다.

 

유니크는 PRIMARY KEY와 유사하게 제약을 걸 수 있습니다.

 

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

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

 

복합 유니크 제약도 예시를 들었지만, 실제로 사용하는 경우는 많지 않습니다.

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24