[Database] 제약조건

[Database] Table, 테이블

---

제약조건

데이터베이스의 제약조건은 테이블의 해당 컬럼에 사용자가 원치 않은 데이터가 입력, 수정, 삭제되는 것을 방지하기 위한 조건이다.
▶ 데이터의 무결성 유지를 위해 사용자가 지정할 수 있는 성질이다.
▶ 모든 제약조건은 데이터 사전 ( Data Dictionary )에 저장된다.
▶ 제약조건은 테이블 생성 시 지정 가능하고, 테이블 생성 후 구조변경 명령을 통해 추가가 가능하다.

● 제약조건에 종속성이 존재할 경우 테이블 삭제를 방지한다.
● 다음의 제약 조건 유형이 오라클에서 유효한다.

1.  PRIMARY KEY 지정

• 기본키 : UNIQUE + NOT NULL 의 결합과 같다.
• 기본키는 그 데이터 행을 대표하는 컬럼으로, 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키이다.
  ▶ 참조무결성
• UNIQUE 제약조건과 동일하게 기본키를 정의하면 자동으로 인덱스를 생성한다.

※ INDEX
    : 검색 키로, 검색 속도를 향상시킨다.

 

2. UNIQUE 조건

• 데이터의 유일성을 보장(중복 데이터가 없음)
• 자동 인덱스 생성

 

3. CHECK 조건

• 컬럼 값을 어떤 특정 범위로 제한한다.

 

4. DEFAULT (컬럼 기본값 ) 지정

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

 

5. FOREIGN KEY(외래키) 지정

• 기본키를 참조하는 컬럼 혹은 컬럼들의 집합이다.
• 외래키를 가지는 컬럼의 데이터형은 외래키가 참조하는 기본키의 컬럼과 데이터형이 일치해야 한다.
• 외래키에 의해 참조되고 있는 기본키는 삭제가 불가하다.

 

6. NOT NULL 조건

• 컬럼을 필수로 필드화 시킬 때 사용한다.
  ▶ NOT NULL 제약조건 설정 시 해당 컬럼에는 꼭 데이터를 입력해야 한다.

---

테이블 생성 제약조건 정의 구문

• 테이블 레벨 제약조건을 정의할 수 있다.
• 열 레벨 제약조건을 정의할 수 있다.

---

열 레벨 제약조건 정의 구문

•  열 별로 제약조건을 정의한다.
• 무결성 제약조건의 어떤 유형도 정의가 가능하다.

---

NOT NULL 제약조건 정의 구문
▶ 테이블 레벨 제약조건을 사용할 수 없다.
▶ 반드시 COLUMN 레벨에서만 정의가 가능하다.

● 하나 이상의 열을 참조하고, 테이블의 열 정의와는 개별 정의
● NOT NULL 제약조건은 정의할 수 없다.

---

제약 조건 확인
제약 조건을 확인 할 수 있는 테이블은 [USER_CONS_COLUMNS]와 [USER_CONSTRAINTS] 이다.

● USER_CONS_COLUMNS : 컬럼에 할당된 제약조건 조회
● USER_CONSTRAINTS : 유저가 소유한 모든 제약조건 조회

SELECT * FROM USER_CONSTRAINTS;

---

제약조건 선언 구문

CREATE TABLE DEPTS (
    DEPT_NO NUMBER(2)       CONSTRAINT DEPT_DEPT_NO_PK PRIMARY KEY,
    DEPT_NAME VARCHAR2(30)  CONSTRAINT DEPTS_DEPT_NN NOT NULL,
    DPET_DATE DATE          DEFAULT SYSDATE,
    DEPT_PHONE VARCHAR2(30) CONSTRAINT DEPTS_DEPT_PHONE UNIQUE,
    DEPT_GENDER CHAR(1)    CONSTRAINT DEPTS_DEPT_GENDER_CK CHECK(DEPT_GENDER IN ('F','M')),
    LOCAT_ID NUMBER(4)      CONSTRAINT DEPTS_LOCA_ID_FK REFERENCES LOCATIONS(LOCATION_ID)
);

위와 같이 제약조건을 작성할 수 있지만,
CONSTRAINT 구문을 생략해도 제약조건을 입력할 수 있다.
실제로 아래와 같이 작성하는 것이 일반화되어 있다.

CREATE TABLE DEPTS (
    DEPT_NO NUMBER(2)       PRIMARY KEY,
    DEPT_NAME VARCHAR2(30)  NOT NULL,
    DPET_DATE DATE          DEFAULT SYSDATE,
    DEPT_PHONE VARCHAR2(30) UNIQUE,
    DEPT_GENDER CHAR(1)    CHECK(DEPT_GENDER IN ('F','M')),
    LOCAT_ID NUMBER(4)      REFERENCES LOCATIONS(LOCATION_ID)
);

제약조건을 별도로 처리하는 방법
: 아래와 같은 방식으로 작성하게 되면, 제약조건을 여러 개를 지정할 수 있다.

CREATE TABLE DEPTS (
    DEPT_NO NUMBER(2),
    DEPT_NAME VARCHAR2(30) NOT NULL,
    DEPT_DATE DATE DEFAULT SYSDATE,
    DEPT_PHONE VARCHAR2(30),
    DEPT_GENDER CHAR(1),
    LOCA_ID NUMBER(4),
    CONSTRAINT DEPT_NO_PK PRIMARY KEY (DEPT_NO),
    CONSTRAINT DEPT_PHONE_UK UNIQUE (DEPT_PHONE),
    CONSTRAINT DEPTS_DEPT_GENDER_CK CHECK(DEPT_GENDER IN ('F','M')),
    CONSTRAINT DEPTS_LOCA_ID_FK FOREIGN KEY(LOCA_ID) REFERENCES LOCATIONS(LOCATION_ID)
);

---

-- 데이터 삽입 ( 조건 충족 )
INSERT INTO DEPTS(DEPT_NO, DEPT_NAME, DEPT_PHONE, DEPT_GENDER, LOCAT_ID)
VALUES (1, 'ABC', '010...', 'F', 1700);

-- NOT NULL 위배
INSERT INTO DEPTS(DEPT_NO, DEPT_NAME, DEPT_PHONE, DEPT_GENDER, LOCA_ID)
VALUES (1, NULL, '010...', 'F', 1700); 

-- CHECK 제약 위배
INSERT INTO DEPTS(DEPT_NO, DEPT_NAME, DEPT_PHONE, DEPT_GENDER, LOCA_ID)
VALUES (1, 'ABC', '010...', 'X', 1700);

-- 참조제약 위배
INSERT INTO DEPTS(DEPT_NO, DEPT_NAME, DEPT_PHONE, DEPT_GENDER, LOCA_ID)
VALUES (1, 'ABC', '010...', 'F', 170);

-- UNIQUE 제약 위배
INSERT INTO DEPTS(DEPT_NO, DEPT_NAME, DEPT_PHONE, DEPT_GENDER, LOCAT_ID)
VALUES (2, 'ABC', '010...', 'F', 1700);

---

제약조건 관리

● 제약 조건의 추가 또는 삭제는 가능하지만 삭제는 불가능하다.
● 제약조건은 제약조건의 활성화 또는 비활성화가 가능하다.
● MODIFY 절을 사용하여 NOT NULL 조건을 추가할 수 있다.

제약 조건 추가 구문

-- PK추가
ALTER TABLE DEPTS ADD CONSTRAINTS DEPT_NO_PK PRIMARY KEY (DEPT_NO);
-- UNIQUE 추가
ALTER TABLE DEPTS ADD CONSTRAINTS DEPT_NO_UK UNIQUE (DEPT_PHONE);
-- CHECK 추가
ALTER TABLE DEPTS ADD CONSTRAINTS DEPT_GENDER_CK CHECK (DEPT_GENDER IN ('F','M'));
-- FOREIGN KEY 추가
ALTER TABLE DEPTS ADD CONSTRAINT DEPTS_LOCA_ID_FK FOREIGN KEY(LOCA_ID) REFERENCES LOCATIONS(LOCATION_ID);
-- NOT NULL은 ALTER 구문 불가, MODIFY 이용
ALTER TABLE DEPTS MODIFY DEPT_PHONE VARCHAR2(30) NOT NULL;

제약 조건 추가 삭제

ALTER TABLE DEPTS DROP CONSTRAINTS DEPT_NO_UK;

---

제약조건을 이용한 실습

다음과 같은 테이블을 생성하고 데이터를 insert하세요.

조건)
● M_NAME 는 가변문자형, 널값을 허용하지 않음
● M_NUM 은 숫자형, 이름(mem_memnum_pk) primary key
● REG_DATE 는 날짜형, 널값을 허용하지 않음, 이름:(mem_regdate_uk) UNIQUE키
● GENDER 가변문자형
● LOCA 숫자형, 이름:(mem_loca_loc_locid_fk) foreign key – 참조 locations테이블(location_id)

MEMBERS테이블과 LOCATIONS테이블을 INNER JOIN 하고 m_name, m_mum, street_address, location_id 컬럼만 조회 m_num기준으로 오름차순 조회

CREATE TABLE MEMBERS (
    M_NAME VARCHAR2(30) NOT NULL,     
    M_NUM NUMBER(10),        
    REG_DATE DATE NOT NULL,          
    GENDER VARCHAR2(30),
    LOCA NUMBER(10),
    CONSTRAINT MEM_MEMNUM_PK PRIMARY KEY (M_NUM),    
    CONSTRAINT MEM_REGDATE_UK UNIQUE (REG_DATE),
    CONSTRAINT MEM_LOCA_LOC_LOCID_FK FOREIGN KEY(LOCA) REFERENCES LOCATIONS(LOCATION_ID)
);

INSERT INTO MEMBERS ( M_NAME, M_NUM, REG_DATE, GENDER, LOCA )
VALUES ( 'AAA', 1, '2018-07-01' , 'M' , 1800 );
INSERT INTO MEMBERS ( M_NAME, M_NUM, REG_DATE, GENDER, LOCA )
VALUES ( 'BBB', 2, '2018-07-02' , 'F' , 1900 );
INSERT INTO MEMBERS ( M_NAME, M_NUM, REG_DATE, GENDER, LOCA )
VALUES ( 'CCC', 3, '2018-07-03' , 'M' , 2000 );
INSERT INTO MEMBERS ( M_NAME, M_NUM, REG_DATE, GENDER, LOCA )
VALUES ( 'DDD', 4, SYSDATE , 'M' , 2000 );

SELECT 
    M.M_NAME,
    M.M_NUM,
    L.STREET_ADDRESS,
    L.LOCATION_ID
FROM MEMBERS M
JOIN LOCATIONS L
ON M.LOCA = L.LOCATION_ID
ORDER BY M_NUM;