[Oracle] 제약조건

• 제약 조건 (CONSTRAINTS)

컬럼에 대한 속성 정의하는 것. 데이터의 무결성을 보장한다.

테이블에 부적절한 데이터가 들어오는 것을 사전에 차단하도록 규칙을 정하는 것이다.

 

• 제약 조건을 설정하는 방법 2가지

1. 테이블 생성 시 제약 조건도 같이 설정
2. 테이블 생성 후 제약 조건 추가 (보통 2번으로 많이 한다.)

 

• 제약 조건 확인

 SELECT A.TABLE_NAME
     , A.CONSTRAINT_NAME
     , A.CONSTRAINT_TYPE
     , B.COLUMN_NAME     
     , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = 'EX1'
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;

 

• 제약 조건 삭제

 ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건명];

 

---

 

• NOT NULL

해당 컬럼에는 반드시 데이터가 들어가야 한다. NULL 값을 넣을 수 없다.

 

예시

-- 1. 
CREATE TABLE TEST.EX1 (
    EMPLOYEE_ID NUMBER(6) NOT NULL,
    PHONE_NUMBER VARCHAR2(20) 
);

-- 2. 
ALTER TABLE TEST.EX1 MODIFY (PHONE_NUMBER CONSTRAINT N_EX1 NOT NULL);

→ 다른 제약조건과 다르게, 테이블 생성 후 제약 조건을 추가할 때 NOT NULL은 MODIFY를 사용해서 설정해야 한다.

     칼럼에 기본값으로 허용되어 있는 NULL 값을 NOT NULL로 변경하는 것이기 때문

 

---

• UNIQUE

해당 컬럼에 들어가는 값은 테이블 전체에서 유일해야 한다. 중복값을 허용하지 않는다.

UNIQUE 조건을 설정하면 자동으로 인덱스가 생성된다.

 

예시

-- 1.
CREATE TABLE TEST.EX1 (
    EMPLOYEE_ID NUMBER(6) UNIQUE NOT NULL,  /* NOT NULL과 함께 부여 */
    PHONE_NUMBER VARCHAR2(20) UNIQUE
);

-- 2.
ALTER TABLE TEST.EX1 ADD [CONSTRAINT U_EX1] UNIQUE(PHONE_NUMBER);

[CONSTRAINT 제약조건명] 은 따로 지정하지 않을 수 있다. 지정해주지 않으면 시스템에서 임의로 설정해준다.

 

---

• PRIMARY KEY

기본키. UNIQUE + NOT NULL 의 특성을 가지고 있다.

테이블 당 1개의 기본키만 생성할 수 있다. (여러 컬럼을 묶어 하나의 기본키로 만드는 것이 가능하다.)

데이터 무결성을 지켜주는 역할.

 

예시

-- 1. 
CREATE TABLE TEST.EX1 (
    EMPLOYEE_ID NUMBER(6) PRIMARY KEY, 
    PHONE_NUMBER VARCHAR2(20)
);

-- 2.
CREATE UNIQUE INDEX PK_EMPLOYEES ON TEST.EMPLOYEES(EMPLOYEE_ID);

ALTER TABLE TEST.EMPLOYEES ADD (
    CONSTRAINT PK_EMPLOYEES
    PRIMARY KEY (EMPLOYEE_ID)
);

※ PRIMARY KEY 변경

1. Primary Key 삭제

ALTER TABLE TEST.EX1 DROP PRIMARY KEY;

→ PRIMARY KEY란 속성을 삭제한다. 컬럼의 데이터는 남아 있다.

 

CASE # 1. 새 칼럼을 추가하여 Primary Key 로 변경

ALTER TABLE TEST.EX1 ADD EMPLOYEE_NUMBER NUMBER(6);                        -- 컬럼 추가
CREATE UNIQUE INDEX PK2_EX1 ON TEST.EX1(EMPLOYEE_NUMBER);                  -- 인덱스 생성
ALTER TABLE TEST.EX1 ADD CONSTRAINT PK2_EX1 PRIMARY KEY(EMPLOYEE_NUMBER);  -- PK 추가

→ TYPE이 NUMBER(6)인 EMPLOYEE_ID 라는 컬럼이 PRIMARY KEY 속성을 가지고 테이블에 추가된다.

 

CASE # 2. 기존 컬럼을 PRIMARY KEY 로 설정

ALTER TABLE TEST.EX1 ADD CONSTRAINT PL_EX1 PRIMARY KEY (EMPLOYEE_ID);

→ 기존에 있던 컬럼 EMPLOYEE_ID 가 PRIMARY KEY 가 된다.

※ UNIQUE NOT NULL 을 부여하면 자동으로 PK가 될까?

→ PK의 특성이 있을 뿐 PK로 자동 등록되지는 않는다.

 

TABLE COLUMN 정보와 PK 삭제 시도로 확인

     Column		     Type		    Nullable
     -------------	-------------	-------------
1    EMPLOYEE_ID	NUMBER(6)	     Not Null	-- PK면 PK1 등으로 표시됨
2    PHONE_NUMBER	VARCHAR2(20)

ALTER TABLE TEST.EX1 DROP PRIMARY KEY;
--> ORA-02441 : 존재하지 않는 기본 키를 삭제할 수 없습니다.

 

KEY TYPE 조회를 해보면 U와 C 속성만 있다.

 SELECT A.TABLE_NAME
     , A.CONSTRAINT_NAME
     , A.CONSTRAINT_TYPE
     , B.COLUMN_NAME     
     , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = 'EX1'
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;


  TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE COLUMN_NAME  POSITION
  ---------- --------------- --------------- ------------ ---------
1 EX1	     SYS_C006362     U	             EMPLOYEE_ID  1
2 EX1	     SYS_C006359     U	             PHONE_NUMBER 1 
3 EX1	     SYS_C006361     C	             EMPLOYEE_ID

 

이 상태에서 PK를 등록하려고 하면 이미 UNIQUE KEY와 PRIMARY KEY 가 있다고 뜬다.

ALTER TABLE TEST.EX1 ADD CONSTRAINT PL_EX1 PRIMARY KEY (EMPLOYEE_ID);
--> ORA-02261 : 고유 키 또는 기본 키가 이미 존재하고 있습니다

 

해당 컬럼 (EMPLOYEE_ID)를 사용하는 UNIQUE KEY 가 있기 때문에 PRIMARY KEY 추가가 안되는 것

 

이 컬럼을 PK 로 등록하기 위해서는.. 일단 컬럼을 DROP하고 다시 추가해주면 PK로 등록되긴 한다.

ALTER TABLE TEST.EX1 DROP COLUMN EMPLOYEE_ID;

ALTER TABLE TEST.EX1 ADD EMPLOYEE_ID NUMBER(6) PRIMARY KEY;

 

근데 컬럼 자체를 그냥 드랍하면 안된다.

 

그럼 속성을 다 제거하고 PRIMARY KEY로 등록해보자.

 ALTER TABLE TEST.EX1 DROP CONSTRAINT SYS_C006362;                      -- UNIQUE 제거
 ALTER TABLE TEST.EX1 DROP CONSTRAINT SYS_C006361;                      -- NOT NULL 제거
 ALTER TABLE TEST.EX1 ADD CONSTRAINT PL_EX1 PRIMARY KEY (EMPLOYEE_ID);

 SELECT A.TABLE_NAME 
     , A.CONSTRAINT_NAME
     , A.CONSTRAINT_TYPE
     , B.COLUMN_NAME     
     , B.POSITION
  FROM ALL_CONSTRAINTS  A
     , ALL_CONS_COLUMNS B
 WHERE A.TABLE_NAME      = 'EX1'
   AND A.CONSTRAINT_TYPE = 'P'
   AND A.OWNER           = B.OWNER
   AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 ORDER BY B.POSITION;
 
  TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE COLUMN_NAME  POSITION
  ---------- --------------- --------------- ------------ ---------
1 EX1	     PL_EX1          P	             EMPLOYEE_ID  1

→ UNIQUE 속성을 제거하면 PK로 지정할 수 있다.

 

---

• FOREIGN KEY

외래키.

테이블 간의 참조 데이터 무결성 보장

참조 데이터 무결성 보장을 통해, 참조 관계에 있는 테이블의 데이터 추가, 삭제, 수정을 통제할 수 있다.

 

제약사항

1. 참조하는 테이블이 먼저 생성되어 있어야 한다.
2. 외래키가 참조하는 컬럼은 참조하는 테이블의 기본키(PRIMARY KEY)이어야 한다.
3. 여러 컬럼을 외래키로 할 경우, 참조하는 테이블의 기본키와 컬럼 개수 및 순서가 같아야 한다.

 

예시

-- 1.
CREATE TABLE EX1(
    EMPLOYEE_ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE EX2(
    NAME VARCHAR(10),
    PHONE_NUMBER NUMBER(10),
    CONSTRAINT FK_EX2 FOREIGN KEY(PHONE_NUMBER) REFERENCES EX1(EMPLOYEE_ID)
);

-- 2.
ALTER TABLE EX1 ADD [CONSTRAINT U_EX1] UNIQUE(EMPLOYEE_ID);
ALTER TABLE EX2 ADD [CONSTRAINT FK_EX2] FOREIGN KEY(PHONE_NUMBER) REERENCES EX1(EMPLOYEE_ID);

 

 

※ 제약사항 확인

1. 참조하는 테이블이 먼저 생성되어 있어야 한다.

-- EX1 테이블 미생성

CREATE TABLE EX2(
    NAME VARCHAR(10),
    PHONE_NUMBER NUMBER(10),
    CONSTRAINT FK_EX2 FOREIGN KEY(PHONE_NUMBER) REFERENCES EX1(EMPLOYEE_ID)
);
--> ORA-00942: 테이블 또는 뷰가 존재하지 않습니다.

 

2. 기본키가 아닌 컬럼을 참조할 수 없다.

ALTER TABLE TEST.EX1 ADD EMPLOYEE_NUMBER NUMBER(6);

CREATE TABLE EX2(
    NAME VARCHAR(10),
    PHONE_NUMBER NUMBER(10),
    CONSTRAINT FK_EX2 FOREIGN KEY(PHONE_NUMBER) REFERENCES EX1(EMPLOYEE_NUMBER)
);
--> ORA-02270: 이 열목록에 대해 일치하는 고유 또는 기본 키가 없습니다.

 

3. 외래키와 참조하는 테이블의 기본키는 데이터 유형이 일치해야 한다.

CREATE TABLE EX2(
    NAME VARCHAR(10),
    EMAIL VARCHAR(20),
    CONSTRAINT FK_EX2 FOREIGN KEY(EMAIL) REFERENCES EX1(EMPLOYEE_ID)
);
--> ORA-02267: 열의 데이터 유형이 참조 열의 데이터 유형과 일치하지 않습니다.

 

---

• CHECK

컬럼에 입력되는 데이터를 CHECK 하여

미리 지정된 조건에 맞을 경우에만 입력을 허용한다.

-- 1.
CREATE TABLE TEST.EX1 (
    EMPLOYEE_NUMBER NUMBER(6)
    CONSTRAINT CHECK_EX1 CHECK (EMPLOYEE_NUMBER BETWEEN 1000 AND 9999)
);

-- 2.
ALTER TABLE EX1 ADD [CONSTRAINT CHECK_EX1] CHECK(EMPLOYEE_NUMBER BETWEEN 1000 AND 9999);

 

※ 조건에 맞지 않는 데이터를 추가하면 당연히 오류가 난다.

INSERT INTO TEST.EX1 VALUES(10000);
--> ORA-02290: 체크 제약조건(TEST.CHECK_EX1)이 위배되었습니다.

 

---

• DEFAULT

데이터를 입력하지 않아도 지정된 값이 기본으로 입력된다.

-- 1. 
CREATE TABLE TEST.EX1 ( 
    EMPLOYEE_ID NUMBER(6) PRIMARY KEY, 
    PHONE_NUMBER VARCHAR2(20),
    DEPARTMENT_ID NUMBER(6) DEFAULT 10,
    MANAGER_ID VARCHAR(10) DEFAULT 'N'
);

-- 2.
ALTER TABLE TEST.EX1 ADD MANAGER_ID VARCHAR(10) DEFAULT 'N';

 

※ NOT NULL DEFAULT

• NOT NULL

NULL 을 입력하거나, 아무것도 입력하지 않아 NULL 값이 넘어가는 경우에 모두 오류를 생성한다.

 

• DEFAULT 

사용자가 값을 입력하지 않는 경우 DEFAULT 값을 해당 칼럼에 입력한다.

사용자가 NULL을 입력해도, 값을 입력한 것으로 보아 DEFAULT는 활성화되지 않는다.

 

• NOT NULL DEFAULT

사용자가 값을 입력하지 않는 경우엔 DEFAULT 값이 입력되고, 

NULL 값을 입력하는 경우 NOT NULL 제약조건으로 인해 오류가 발생한다.

 

---

• DISABLE / ENABLE

• DISABLE

1. NOVALIDATE (DEFAULT)

제약조건을 일시적으로 무력화시킨다. 제약 조건에 맞지 않는 데이터를 입력할 수 있다.

ALTER TABLE TEST.EX1 DISABLE [NOVALIDATE] CONSTRAINT U_EX1

2. VALIDATE

데이터의 변경이 안되게끔 하는 옵션. 해당 칼럼의 내용을 변경하는 INSERT, UPDATE, DELETE 작업을 수행할 수 없다.

ALTER TABLE TEST.EX1 DISABLE VALIDATE CONSTRAINT U_EX1

 

• ENABLE

1. NOVALIDATE

(ENABLE 하는 시점 이전의 테이블에 들어있던) 기존 데이터는 검사하지 않고

ENABLE 하는 시점 이후부터 입력되는 데이터만 제약조건을 적용해서 검사한다.

ALTER TABLE TEST.EX1 ENABLE NOVALIDATE CONSTRAINT U_EX1

2. VALIDATE (DEFAULT)

기존 데이터와 신규 데이터 모두 검사. 

 

하지만 기존 데이터에서 제약 조건을 위반하는 값이 있다면 에러가 발생하며 ENABLE 작업이 취소된다.

위반하는 값을 찾아서 조치 후 ENABLE 작업을 시도해야 한다.

→ EXCEPTIONS TABLE 사용

ALTER TABLE TEST.EX1 VALIDATE CONSTRAINT U_EX1 EXCEPTIONS INTO SYS.EXCEPTIONS;

 

-- EXCEPTIONS TABLE 조회
SELECT ROWID, NO FROM TEST.EX1 WHERE ROWID IN (SELECT ROW_ID FROM EXCEPTIONS);

-- 제약 조건 위반 값 업데이트
UPDATE TEST.EX1 SET PHONE_NUMBER = '010-9979-2702' WHERE ROWID = 'AAAHM1AAEAAAAD1AAA'

-- 에러 내역 삭제
TRUNCATE TABLE SYS.EXCEPTIONS ;

 

---

참고

https://preamtree.tistory.com/31

[ORACLE] ORACLE의 제약조건(Constraints)

https://hayleyfish.tistory.com/47

CONSTRAINT - 제약조건

http://www.gurubee.net/lecture/1013

테이블의 제약조건