0810_Oracle : 기본키 PRIMARY KEY와 제약조건

기본키(PRIMARY KEY)에 대해 알아보자.

• 테이블의 각 행을 구분하는 유일성 제약 조건이다.
• 테이블에 하나만 만들 수 있다.
• 두 개 이상의 컬럼을 조합해서 하나의 기본키를 만들 수 있다.
• 중복값을 가질 수 없으며, NULL을 가질 수 없다.

 

컬럼 레벨 방식으로 기본키를 부여해보자.

컬럼 레벨 방식으로 기본키를 부여하면 하나의 컬럼으로만 기본키를 만들 수 있다.

CREATE TABLE test1 (
    id VARCHAR2(30) PRIMARY KEY,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST1';

을 통해 제약 조건을 확인해보자

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST1';

 

이 명령어를 통해서는 어떤 컬럼에 제약 조건이 있는지는 확인이 불가능하고 제약조건이 있다는 것만 알 수 있다. 이 명령어를 통해 나타나는 항목 CONSTRAINT_NAME는 이름을 부여하지 않으면 SYS_...로 표기된다. CONSTRAINT_TYPE은 제약 조건의 타입을 알 수 있다. P : 기본키, C : NOT NULL 등의 check 제약을 나타낸다.

 

SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST1';

을 통해 제약조건이름과 컬럼명을 확인해보자.

SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST1';

이 명어를 통해서는 제약조건이름(CONSTRAINT_NAME)과 컬럼명(COLUMN_NAME)을 확인할 수 있으나, 어떤 제약 조건인지는 확인이 불가능하다.

 

제약조건 및 컬럼을 확인하기 위해서는 어떻게 해야할까?

-- 제약조건 및 컬럼 확인
SELECT u1.table_name, column_name, constraint_type, u1.constraint_name, search_condition
FROM user_constraints u1
JOIN user_cons_columns u2 ON u1.constraint_name = u2.constraint_name
WHERE u1.table_name = UPPER('TEST1');

이 명령어를 통해 제약조건 및 컬럼을 확인할 수 있다. 이 명령어의 동작 원리는 다음시간에 자세히 알아보도록 하겠다.

 

컬럼 방식 기본키를 통해 테이블을 만들어 보자. ( 제약조건명 부여)

-- 컬럼 방식 기본키, 제약조건명 부여. 제약조건명은 중복적인 이름을 가질 수 없다.
CREATE TABLE test2 (
    id VARCHAR2(30) CONSTRAINT pk_test2_id PRIMARY KEY,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST2';

id 에 PRIMARY KEY를 주고 제약조건명을 pk_test2_id 라고 주었다. 이러면 다음에 확인할 때 pk (PRIMARY KEY의 Acronym)이고 테이블명 컬럼을 주었다. 이것을 USER_CONSTRAINTS 명령어를 입력했을 때 CONSTRAINT_NAME에서 확인할 수 있어서 어떤 컬럼인지 원래는 알 수 없으나 이름을 주었기 때문에 컬럼명으로 유추할 수 있다.

 

테이블 레벨 방식으로 기본키를 부여해보자. 

-- 테이블 레벨 방식으로 기본키 부여(제약조건이름은 부여하지 않음)
CREATE TABLE test3 (
    id VARCHAR2(30),
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    PRIMARY KEY( id )
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST3';

-- 테이블 레벨 방식으로 기본키 부여(제약조건이름 부여)
CREATE TABLE test4 (
    id VARCHAR2(30),
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    CONSTRAINT pk_test4_id PRIMARY KEY( id )
);
SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST4';

-- 두 개의 컬럼으로 기본키 부여
CREATE TABLE test5 (
    id VARCHAR2(30),
    code NUMBER,
    pwd VARCHAR2(30) NOT NULL,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    CONSTRAINT pk_test5_id PRIMARY KEY( id, code )
    -- 복합키로 기본키를 구성할 때는 꼭 이렇게 작성해야함
);

SELECT * FROM USER_CONSTRAINTS WHERE table_name= 'TEST5'; -- 확인용
SELECT * FROM USER_CONS_COLUMNS WHERE table_name= 'TEST5'; -- 확인용

마지막에 PRIMARY KEY(컬럼명)을 기술함으로서 id에 PRIMARY KEY를 부여해주었다.

두 개 이상의 컬럼으로 기본키를 부여할 때는 꼭 테이블 레벨 방식으로 기본키를 부여해야한다. 

확인용의 쿼리 결과

 

USER_CONSTRAINTS 명령어로는 제약조건을 확인할 수 있으나컬럼의 이름을 알 수 없다. 하지만 CONSTRAINT_NAME을 주어서 ID컬럼이 기본키임을 유추할 수 있다.USER_CON_COLUMNS명령어를 통해 컬럼의 이름을 확인 할 수 있다. code도 PRIMARY KEY를 구성하는 컬럼임을 유추할 수 있다.