[Oracle SQL] DDL - 테이블 생성(CREATE), 제약 조건

DDL(Data Definition Language)은 데이터베이스의 구조를 생성(CREATE)하거나 수정(ALTER) 및 삭제(DROP)하는데 사용되는 데이터 정의어입니다. DML과 달리 DDL은 자동으로 COMMIT 되기 때문에 데이터베이스에 즉각 영향을 미치며 데이터베이스 사전(DATA DICTIONARY)에 정보가 저장됩니다.

오라클 데이터베이스는 테이블(table), 인덱스(index), 뷰(view), 시퀀스(sequence), 동의어(synonym)와 같은 여러 개의 데이터 구조를 가지고 있으며, 이를 오라클 객체라고 부릅니다. DDL은 이러한 오라클 객체를 생성하거나 수정 및 삭제할 때 사용합니다. 이 중 가장 중요한 객체인 테이블을 생성하고 수정 및 삭제하는 방법을 알아보겠습니다.

 

 

테이블 생성 (CREATE)

 

테이블을 생성하는 기본 문법은 다음과 같습니다.

CREATE TABLE [스키마.]테이블명
( 컬럼명 데이터타입  [ DEFAULT값 | 제약조건 ][,...] );

 

주로 사용하는 데이터 타입은 다음과 같습니다.

 

데이터 타입	설명
VARCHAR2(size)	가변 길이의 문자 데이터
NUMBER(p,s)	가변 길이의 숫자 데이터 p는 전체 자리수, s는 소수점 자리수를 의미
DATE	날짜 및 시간

 

다른 옵션이나 제약조건 없이 테이블을 생성하는 SQL문입니다.

CREATE TABLE test_employee
 (empno NUMBER(4),
 ename VARCHAR2(20),
 hiredate DATE,
 sal NUMBER(7,2));

 

 

 

1. DEFAULT 옵션

 

데이터를 입력할 때 값을 지정하지 않으면 null값이 입력됩니다. 이때 값을 지정하지 않아도 null값이 아닌 기본값이 입력되도록 DEFAULT 옵션을 설정할 수 있습니다. 

 

입사일 컬럼에 DEFAULT 옵션을 사용한 test_employee2 테이블을 생성해 보겠습니다.

CREATE TABLE test_employee2
 (empno NUMBER(4),
 ename VARCHAR2(20),
 hiredate DATE DEFAULT SYSDATE,
 sal NUMBER(7,2));

 

여기에 입사일을 지정하지 않은 사원의 정보를 입력합니다.

INSERT INTO test_employee2 (empno,ename,sal) 
VALUES (10,'홍길동',1000);

입사일 값을 지정하지 않아도 null로 입력되지 않고, 기본값인 현재 날짜로 입력된 것을 확인할 수 있습니다. 

 

 

 

 

2. 제약 조건 (Constraints Rule)

 

제약 조건은 테이블에 올바르지 않은 데이터가 저장되는 것을 방지하지 위해 테이블 생성 시 각 컬럼에 대해 정의하는 여러 가지 규칙을 의미합니다. 오라클에서는 다음의 다섯 가지 제약 조건이 제공됩니다.

 

제약 조건 타입	설명
NOT NULL	해당 컬럼 값은 NULL을 허용하지 않는다. 컬럼 레벨 방식만 지원한다.
UNIQUE	해당 컬럼 값은 중복이 불가하다.
PRIMARY KEY	해당 컬럼 값은 NULL을 허용하지 않으며, 중복이 불가하다.
FOREIGN KEY	해당 컬럼의 값이 다른 테이블의 컬럼 값을 참조해야 한다. 즉, 참조되는 컬럼에 없는 값은 저장이 불가하다.
CHECK	해당 컬럼에 가능한 데이터 값의 범위나 조건을 지정한다.

 

제약 조건을 설정하는 방법은 컬럼을 정의하면서 같이 제약 조건을 명시하는 컬럼 레벨 방법과, 모든 컬럼을 정의하고 마지막에 제약 조건을 추가하는 테이블 레벨 방법이 있습니다. 컬럼 레벨 제약 조건은 한 개의 컬럼에 한 개의 제약 조건만 부여할 수 있는 반면, 테이블 레벨 제약 조건은 한 개의 컬럼에 여러 개의 제약 조건을 부여하거나 여러 개의 컬럼에 한 개의 제약 조건을 부여할 수 있습니다. 다른 제약 조건들과 달리 NOT NULL 제약 조건은 컬럼 레벨 방식만 가능합니다. 

 

 

 

2-1. PRIMARY KEY 제약 조건

 

기본키(Primary Key)는 테이블에서 해당 행을 다른 행과 구분할 수 있도록 식별 기능을 가진 컬럼이며, 테이블당 하나의 기본키만 가질 수 있습니다. 해당 컬럼의 값은 null값을 허용하지 않으며 유일해야 합니다. 다음은 컬럼 레벨 방식으로 기본키 제약 조건을 설정하는 문법입니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입 [CONSTRAINT 제약조건명] PRIMARY KEY,
 컬럼명 데이터타입,
 ...
 );

제약조건명은 사용자가 임의로 설정할 수 있으며 생략해도 오라클이 자동으로 설정해 주지만, 이후 제약조건명을 사용할 때 편의성을 위해 '테이블명_컬럼명_제약조건타입'과 같은 형태로 지정하는 것이 좋습니다. 

 

부서번호를 기본키로 설정한 부서 테이블을 만들어 보겠습니다.

CREATE TABLE department
 (deptno NUMBER(2) CONSTRAINT department_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15),
 loc VARCHAR2(15) 
 );

 

부서번호 컬럼에 중복되는 데이터가 들어갈 수 없는 것을 확인합니다. 

INSERT INTO department (deptno,dname,loc)
VALUES (10,'인사','서울');
INSERT INTO department (deptno,dname,loc)
VALUES (10,'개발','경기');  --> 오류
--> ORA-00001: unique constraint (SCOTT.DEPARTMENT_DEPTNO_PK) violated

 

NULL값을 허용하지 않는 것도 확인할 수 있습니다.

INSERT INTO department (deptno,dname,loc)
VALUES (NULL,'인사','서울');  --> 오류
--> ORA-01400: cannot insert NULL into ("SCOTT"."DEPARTMENT"."DEPTNO")

 

* 데이터 사전 (DATA DICTIONARY)

생성된 제약 조건을 확인하기 위해서 데이터 사전을 조회할 수 있습니다.

SELECT * FROM USER_CONSTRAINTS WHERE table_name='DEPARTMENT';
SELECT * FROM USER_CONS_COLUMNS WHERE table_name='DEPARTMENT';

제약 조건의 타입은 다음의 약어로 저장됩니다.

 

약어	설명
P	PRIMARY KEY
R	FOREIGN KEY  (Reference의 약어)
U	UNIQUE
C	NOT NULL, CHECK

 

이번에는 테이블 레벨 방식으로 기본키 제약 조건을 추가하는 방법입니다. 테이블 레벨 방식에서는 어떤 컬럼에 제약 조건을 부여할 것인지 명시해 주어야 합니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입,
 컬럼명 데이터타입,
 ...,
 [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명[,컬럼명2]) 
 );

 

department 테이블과 동일하게 부서번호를 기본키로 설정하는 테이블을 생성합니다.

CREATE TABLE department2
 (deptno NUMBER(2),
 dname VARCHAR2(15),
 loc VARCHAR2(15),
 CONSTRAINT department2_deptno_pk  PRIMARY KEY(deptno)
 );

 

테이블 레벨 방식에서는 두 개 이상의 복합컬럼에도 기본키를 설정할 수 있습니다. 부서번호와 지역을 조합하여 기본키로 설정하는 SQL문입니다.

CREATE TABLE department3
 (deptno NUMBER(2),
 dname VARCHAR2(15),
 loc VARCHAR2(15),
 CONSTRAINT department3_deptno_pk  PRIMARY KEY(deptno,loc)
 );

 

부서번호와 지역을 쌍으로 비교하기 때문에 부서번호가 같아도 지역이 다르면 입력되는 것을 확인할 수 있습니다.

INSERT INTO department3 (deptno,dname,loc) 
VALUES (10,'인사','제주');
INSERT INTO department3 (deptno,dname,loc) 
VALUES (10,'인사','서울');

 

 

 

2-2. UNIQUE 제약 조건

 

컬럼의 모든 데이터가 유일해야 하는 경우 사용할 수 있습니다. 기본키와 다른 점은 하나의 테이블에 UNIQUE 제약 조건을 여러 개 지정할 수 있고 null값도 저장할 수 있다는 것입니다. 다음은 컬럼 레벨 방식으로 UNIQUE 제약 조건을 설정하는 문법입니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입 [CONSTRAINT 제약조건명] UNIQUE,
 컬럼명 데이터타입,
 ...
 );

 

컬럼 레벨 방식으로 부서명 컬럼을 UNIQUE로 지정하는 테이블을 생성해 보겠습니다.

CREATE TABLE department4
 (deptno NUMBER(2) CONSTRAINT department4_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15) CONSTRAINT department4_dname_uk UNIQUE,
 loc VARCHAR2(15) 
 );

 

부서명 컬럼에 중복되는 데이터가 들어갈 수 없는 것을 확인할 수 있습니다.

INSERT INTO department4 (deptno,dname,loc)
VALUES (10,'개발','서울');
INSERT INTO department4 (deptno,dname,loc)
VALUES (20,'개발','경기');  --> 오류
--> ORA-00001: unique constraint (SCOTT.DEPARTMENT4_DNAME_UK) violated

 

마찬가지로 테이블 레벨 방식에서 UNIQUE 제약 조건을 설정할 수 있습니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입,
 컬럼명 데이터타입,
 ...,
 [CONSTRAINT 제약조건명] UNIQUE(컬럼명[,컬럼명2]) 
 );

CREATE TABLE department5
 (deptno NUMBER(2),
 dname VARCHAR2(15),
 loc VARCHAR2(15),
 CONSTRAINT department5_deptno_pk PRIMARY KEY(deptno),
 CONSTRAINT department5_dname_uk UNIQUE(dname)
 );

 

 

 

2-3. NOT NULL 제약 조건

 

해당 컬럼에 null값이 저장되는 것을 방지합니다. 주의할 점은 테이블 레벨 방식으로는 사용할 수 없으며 컬럼 레벨 방식만 지원된다는 것입니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL,
 컬럼명 데이터타입,
 ...
 );

 

부서 지역을 NOT NULL 제약 조건으로 지정하여 테이블을 생성합니다. 

CREATE TABLE department6
 (deptno NUMBER(2) CONSTRAINT department6_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15) CONSTRAINT department6_dname_uk UNIQUE,
 loc VARCHAR2(15) CONSTRAINT department6_loc_nn NOT NULL
 );

 

부서 지역 컬럼에 NULL값을 저장할 수 없다는 것을 확인할 수 있습니다.

INSERT INTO department6 (deptno,dname,loc)
VALUES (30,'인사',NULL);  --> 오류
--> ORA-01400: cannot insert NULL into ("SCOTT"."DEPARTMENT6"."LOC")

 

 

 

2-4. CHECK 제약 조건

 

해당 컬럼에 저장되는 데이터를 검사하여 조건과 일치하는 데이터만 저장이 가능하도록 합니다. SELECT문의 WHERE절에서 사용했던 다양한 연산자들과 함께 사용 가능합니다. 다음은 컬럼 레벨 방식으로 CHECK 제약 조건을 설정하는 문법입니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식),
 컬럼명 데이터타입,
 ...
 );

 

부서명 컬럼에 '개발'과 '인사'만 저장 가능하도록 지정할 수 있습니다.

CREATE TABLE department7
 (deptno NUMBER(2) CONSTRAINT department7_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15) CONSTRAINT department7_dname_ck CHECK(dname IN('개발','인사')),
 loc VARCHAR2(15) CONSTRAINT department7_loc_nn NOT NULL
 );

 

다른 값을 저장하려 하면 오류가 발생하는 것을 확인할 수 있습니다.

INSERT INTO department7 (deptno,dname,loc)
VALUES (10,'회계','서울');  --> 오류
--> ORA-02290: check constraint (SCOTT.DEPARTMENT7_DNAME_CK) violated

 

 

다음과 같이 테이블 레벨 방식으로 설정 가능합니다.

CREATE TABLE department8
 (deptno NUMBER(2),
 dname VARCHAR2(15),
 loc VARCHAR2(15) CONSTRAINT department8_loc_nn NOT NULL,
 CONSTRAINT department8_deptno_pk PRIMARY KEY(deptno),
 CONSTRAINT department8_dname_ck CHECK(dname IN('개발','인사'))
 );

 

 

 

2-5. FOREIGN KEY 제약 조건

 

외래키(Foreign Key)는 참조키라고도 부르며, 해당 테이블에서 다른 테이블을 참조할 때 올바른 데이터 값만 참조 가능하도록 제약하는 방법입니다. 외래키로 설정된 컬럼의 데이터는 외래키가 참조하고 있는 컬럼의 데이터에 존재하는 값또는 null값만 입력할 수 있으며, 참조당하고 있는 컬럼의 레코드는 삭제가 불가능합니다.

예를 들어 말하자면, emp 테이블의 deptno 컬럼은 dept 테이블의 deptno 컬럼을 참조하고 있습니다. dept 테이블의 deptno 컬럼에는 10, 20, 30, 40 만 존재하므로, emp 테이블의 deptno 컬럼에는 그 외의 값을 입력할 수 없습니다. 또한 emp 테이블에서 30번 부서인 사원이 존재하기 때문에, dept 테이블에서 30번 부서를 삭제할 수 없습니다. 이러한 관계에서 참조하는 emp 테이블을 자식 테이블이라고 하고, 참조당하는 dept 테이블을 부모 테이블이라고 합니다. 

 

다음은 컬럼 레벨 방식으로 FOREIGN KEY 제약 조건을 설정하는 문법입니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 부모테이블명(컬럼명),
 컬럼명 데이터타입,
 ...
 );

주의할 점은, 참조하는 부모 테이블의 컬럼이 반드시 기본키(PRIMARY KEY) 또는 UNIQUE 제약 조건이 설정된 컬럼이어야 한다는 것입니다. 데이터가 중복되면 어떤 것을 참조할 지 모호해 지기 때문입니다. 

 

부서 번호 컬럼을 기본키로 설정하는 부모 테이블을 생성 후 데이터를 입력해 보겠습니다.

CREATE TABLE dept02
 (deptno NUMBER(2) CONSTRAINT dept02_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15),
 loc VARCHAR2(15)
 );

INSERT INTO dept02 (deptno,dname,loc) VALUES (10,'인사','서울');
INSERT INTO dept02 (deptno,dname,loc) VALUES (20,'개발','광주');
INSERT INTO dept02 (deptno,dname,loc) VALUES (30,'관리','부산');
INSERT INTO dept02 (deptno,dname,loc) VALUES (40,'영업','경기');
COMMIT;

 

dept02 테이블의 deptno 컬럼을 참조하는 외래키를 갖는 자식 테이블을 생성합니다.

CREATE TABLE emp02
 (empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename VARCHAR2(15),
 deptno NUMBER(2) CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno)
 );

 

다음과 같이 사원 정보를 저장합니다. 부모 테이블에 있는 데이터 값을 입력하거나 null값을 지정하는 것은 가능하지만, 부모 테이블에 없는 값을 입력하면 오류가 발생하는 것을 확인할 수 있습니다.

INSERT INTO emp02 (empno,ename,deptno) VALUES (1000,'John',10);
INSERT INTO emp02 (empno,ename,deptno) VALUES (2000,'Smith',20);
INSERT INTO emp02 (empno,ename,deptno) VALUES (3000,'Sam',NULL); 
INSERT INTO emp02 (empno,ename,deptno) VALUES (4000,'Mike',50); --> 오류
--> ORA-02291: integrity constraint (SCOTT.EMP02_DEPTNO_FK) violated - parent key not found

 

다음은 테이블 레벨 방식으로 FOREIGN KEY 제약 조건을 설정하는 문법입니다. 어떤 컬럼을 외래키로 설정할 것인지 명시해 준 뒤 이 외래키가 어떤 테이블의 어떤 컬럼을 참조하는지 적어줍니다.

CREATE TABLE 테이블명
 (컬럼명 데이터타입,
 컬럼명 데이터타입,
 ...,
 [CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명) REFERENCES 부모테이블명(컬럼명)
 );

CREATE TABLE emp03
 (empno NUMBER(4),
 ename VARCHAR2(15),
 deptno NUMBER(2),
 CONSTRAINT emp03_empno_pk PRIMARY KEY(empno),
 CONSTRAINT emp03_deptno_fk FOREIGN KEY(deptno) REFERENCES dept02(deptno)
 );

 

데이터 사전에서 제약 조건을 확인할 수 있습니다. 

SELECT table_name,constraint_type,constraint_name,r_constraint_name
FROM user_constraints
WHERE table_name IN('DEPT02','EMP02','EMP03');

 

* FOREIGN KEY 제약 조건의 추가 옵션

외래키를 사용할 때 주의할 점은, 자식 테이블에서 참조하고 있는 부모 테이블의 레코드는 삭제가 불가하다는 것입니다. 참조되고 있는 행이 삭제되면 자식 테이블의 데이터가 존재하지 않는 데이터를 참조하는 상황이 되어 외래키 제약 조건을 위배하게 되기 때문입니다.

DELETE FROM dept02
WHERE deptno=10;
--> ORA-02292: integrity constraint (SCOTT.EMP02_DEPTNO_FK) violated - child record found

 

이런 상황을 해결하기 위해서 다음 두 가지 옵션을 사용할 수 있습니다.

(1) ON DELETE CASCADE 옵션 : 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 행도 연쇄적으로 삭제되도록 합니다. 

CREATE TABLE emp02
 (empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename VARCHAR2(15),
 deptno NUMBER(2)
    CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno) ON DELETE CASCADE
 );

 

(2) ON DELETE SET NULL 옵션 : 부모 테이블의 행이 삭제되면, 해당 행을 참조하는 자식 테이블의 컬럼값을 null로 설정합니다.

CREATE TABLE emp02
 (empno NUMBER(2) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename VARCHAR2(15),
 deptno NUMBER(2) 
    CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno) ON DELETE SET NULL
 );

 

 

DDL에서 테이블을 생성하는 방법과 각 제약 조건에 대해 정리해 보았습니다. 다음 글에서는 이어서 테이블 수정 및 삭제 방법에 대해 알아보겠습니다.