[CH14]제약 조건
제약 조건?
열에 저장될 데이터의 특성, 조건을 지정. 그에 부합하지 않는 데이터 저장 불가
데이터 무결성을 지키기 위한 안전장치
※ 데이터 무결성 ※
데이터베이스에 저장되는 데이터의 정확성과 일관성 보장한다는 의미.
-영역 무결성 : 열에 저장되는 값의 적정 여부 확인.
자료형, 적절한 형식의 데이터, NULL 여부같은 정해 놓은 범위를 만족하는 데이터임을 규정
-개체 무결성 : 테이블 데이터를 유일하게 식별할 수 있는 기본키는 반드시 값을 가지고 있어야 함. NULL 불가, 중복 불가
-참조 무결성 : 참조 테이블의 외래키 값은 참조 테이블의 기본키로서 존재해야 하며 NULL값 가능
1. 제약 조건 확인하는 방법
USER_CONSTRAINTS // 데이터사전뷰에서 확인하는 방법
--SCOTT 계정 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_%'; --기존에 많은 테이블들 중에서 필요한 것만 조회하려고. 조건.| OWNER | CONSTRAINT_NAME | CONTRAINT_TYPE | TABLE_NAME |
| 제약 조건 소유 계정 | 제약 조건 이름 (미지정시 오라클 자동 지정) |
제약 조건 종류 | 제약 조건 지정한 테이블 이름 |
| CONSTRAINT_TYPE | |||
| C | U | P | R |
| CHECK, NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY |
2. 제약 조건 종류
| 종류 | NULL | 데이터 중복 | |
| NOT NULL | 반드시 열에 값이 존재해야만 하는 경우에 지정 | 불가 | 가능 |
| UNIQUE | 지정한 열이 유일한 값 | 가능 | 불가 |
| PRIMARY KEY | 테이블에 하나만 지정. 자동 인덱스. 지정한 열이 유일한 값. 특정 테이블의 데이터 식별하는 유일한 값. ELECT문을 통핸 검색에 자주 활용 ex)EMPNO같은거 |
불가 | 불가 |
| FOREIGN KEY | 다른 테이블 열 참조하여 존재하는 값만 입력 | ||
| CHECK | 설정한 조건식을 만족하는 데이터만 입력 가능 | ||
3. 제약 조건 생성, 수정, 제거
(1) 제약 조건 지정
-테이블 생성 시, 제약 조건 지정 또는 제약 조건 및 이름 지정_NOT NULL기준(UNIQUE, PK, FK, CHECK 동일)
CREATE TABLE 생성할 테이블 이름(
열 이름1 데이터형 제약 조건
열 이름2 데이터형 제약 조건
열 이름3 데이터형 제약 조건
...
열 이름4 데이터형 제약조건 이름 제약 조건
);--테이블 생성 시 제약 조건 설정
CREATE TABLE TABLE_NOTNULL(
LOGIN_ID VARCHAR2(20) NOT NULL,
LOGIN_PWD VARCHAR2(20) NOT NULL,
TEL VARCHAR2(20)
);
--테이블 생성 시 제약 조건 및 이름 설정
CREATE TABLE TABLE_NOTNULL2 (
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLNN2_LGNPW_NN NOT NULL,
TEL VARCHAR2(20)
);
※ 데이터형 확인 ※
DESC TABLE_NOTNULL;
(2) 데이터 입력
-다양한 조건에서 NULL값 넣어보기
제약조건 NOT NULL에 데이터 입력 : NULL 불가, 데이터 중복 가능
--★ LOGIN_ID, LOGIN_PWD에 NOT NULL / TEL 제약조건 없음 상태 ★
--제약 조건 NOT NULL인 열에 NULL값 넣어보기
INSERT INTO TABLE_NOTNULL (LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01', NULL, '010-1234-5678'); --오류 발생
--제약 조건 없는 TEL에 NULL값 입력 : 열 지정하지 않고, 데이터 입력하지 않으면 NULL값
INSERT INTO TABLE_NOTNULL (LOGIN_ID, LOGIN_PWD)
VALUES('TEST_ID_01', '1234');
-- NOT NULL에 중복데이터 입력 : 가능. 중복 입력할 경우, 횟수만큼 들어감.
INSERT INTO TABLE_NOTNULL (LOGIN_ID, LOGIN_PWD)
VALUES('TEST_ID_01', '1234');
-- NOT NULL 제약 조건이 지정된 열 데이터를 NULL값으로 수정
UPDATE TABLE_NOTNULL
SET LOGIN_PWD = NULL
WHERE LOGIN_ID = 'TEST_ID_01';
SELECT * FROM TABLE_NOTNULL; --테이블 조회(입력된 데이터 확인)
-제약 조건 UNIQUE에 데이터 입력 : 데이터 중복 불가, NULL값 가능.
--★ LOGIN_ID에 UNIQUE 제약 조건 설정된 상태 ★
--TABLE UNIQUE 테이블에 데이터 입력
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01', 'PWD01', '010-1234-5678');
--한번더 입력 실행할 경우, UNIQUE 조건 때문에 오류.
--UNIQUE 제약 조건이 지정된 열에 NULL값 입력
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES(NULL, 'PWD01', '010-1234-5678');
-제약 조건 PRIMARY KEY에 데이터 입력 : 데이터 중복 불가, NULL값 불가
--TABLE_PK 테이블에 중복 데이터 입력 // 중복 불가. 오류 발생
INSERT INTO TABLE_PK(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01', 'PWD02', '010-2345-6789');
--TABLE_PK 테이블에 NULL 입력 - 명시적, 암시적
INSERT INTO TABLE_PK(LOGIN_ID, LOGIN_PWD, TEL)
VALUES(NULL, 'PWD02', '010-2345-6789'); --명시적 널값 불가
INSERT INTO TABLE_PK(LOGIN_PWD, TEL)
VALUES('PWD02', '010-2345-6789'); --암시적 널값 불가
(3) 제약 조건 추가, 수정, 삭제
형식은 같고 NOT NULL | UNIQUE | PRIMARY KEY 차이
--열에 NOT NULL 조건 추가
--└TEL 열에 NULL값이 들어있다면 불가능 : 열 데이터 수정 후 NOT NULL 조건 가능
--TEL 열 데이터 수정
UPDATE TABLE_NOTNULL
SET TEL = '010-1234-5678'
WHERE LOGIN_ID = 'TEST_ID_01';
--열에 NULL값 없을 때 NOT NULL 조건 추가
ALTER TABLE TABLE_NOTNULL
MODIFY(TEL NOT NULL);
-이미 생성한 테이블 열에 제약 조건 및 제약 조건 이름 추가 : ALTER ~ MODIFY
--이미 생성된 테이블 열에 UNIQUE 제약 조건 추가
ALTER TABLE TABLE_UNIQUE
MODIFY(TEL UNIQUE);
--UNIQUE 제약 조건 이름 직접 지정
ALTER TABLE TABLE_UNIQUE2
MODIFY(TEL CONSTRAINT TBLUNQ_TEL_UNQ UNIQUE);
-이미 생성한 테이블에 제약 조건 이름 변경 : REANAME
ALTER TABLE 테이블 이름
RENAME CONSTRAINT 기존 제약 조건 이름 TO 바꿀 제약 조건 이름;ALTER TABLE TABLE_UNIQUE2
RENAME CONSTRAINT TBLUNQ_TEL_UNQ TO TBLUNQ2_TEL_UNQ;
-제약 조건 삭제 : ALTER ~ DROP
ALTER TABLE 테이블 이름
DROP CONSTRAINT 테이블의 제약 조건 이름;ALTER TABLE TABLE_UNIQUE2
DROP CONSTRAINT TBLUNQ2_TEL_UNQ;
(4) 외래키(FOREIGN KEY, 외부키, 참조키, 외부식별자)
| FOREIGN KEY | PRIMARY KEY |
| 자식 테이블 | 부모 테이블 |
| 부모 테이블의 참조하는 열과 데이터 타입 반드시 일치 부모 테이블의 참조하는 열에 존재하는 데이터만 입력 가능 보통 기본키(PK)나 UNIQUE 참조(데이터 중복 불가) |
자식 테이블이나 데이터 삭제와 무관 |
-생성 및 참조 : 부모 테이블(참조해올 데이터 테이블), 자식 테이블(FOREIGN KEY 있는)
--DEPT_FK 테이블 생성
CREATE TABLE DEPT_FK(
DEPTNO NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
--EMP_FK 테이블 생성. DEPT_FK를 참조
CREATE TABLE EMP_FK(
EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO)
); --DEPTNO는 DEPT_FK의 PK(DEPTNO)를 참조하는 외래키.
-데이터사전뷰로 확인
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE '%_FK';
-데이터 삽입
EMP_FK 테이블(FK있는 자식 테이블)에 데이터 삽입하려면 부모 테이블 열에 데이터 있어야 함. 아니면 오류!
--DEPT_FK 테이블(부모 테이블)에 데이터 삽입
INSERT INTO DEPT_FK
VALUES(10, 'TEST_DNAME', 'TEST_LOC');
SELCET * FROM DEPT_FK; --DEPT_FK 테이블 조회
--EMP_FK 테이블(자식 테이블)에 데이터 삽입
INSERT INTO EMP_FK
VALUES(9999, 'TEST_NAME', 'TEST_JOB', NULL, TO_DATE('2001/01/01'), 3000, NULL, 10);
SELECT * FROM EMP_FK; --EMP_FK 테이블 조회. 입력된 데이터 확인.
-참조 행 데이터 삭제
DEPT_FK(부모테이블)의 DEPTNO = 10 데이터 삭제
DELETE FROM DEPT_FK
WHERE DEPTNO = 10;
--자식 요소(EMP_FK)의 DEPTNO가 외래키로 묶여 있기 때문에 삭제되지 않음.
SELECT * FROM EMP_FK;
① 현재 삭제하려는 열 값을 참조하는 데이터 먼저 삭제
ex) EMP_FK 테이블(자식 테이블) DEPTNO = 10 삭제 → DEPT_FK 테이블(부모 테이블) DEPTNO = 10 삭제
SELECT * FROM DEPT_FK;
SELECT * FROM EMP_FK;
DELETE FROM EMP_FK WHERE DEPTNO = 10;
DELETE FROM DEPT_FK WHERE DEPTNO = 10;
② 현재 삭제하려는 열 값을 참조하는 데이터 수정
ex) EMP_FK 테이블(자식) DEPTNO = 10번을 다른 부서번호로 바꾸거나 NULL값 변경 후
→ DEPT_FK 테이블 DEPTNO = 10 삭제
SELECT * FROM DEPT_FK;
SELECT * FROM EMP_FK;
UPDATE EMP_FK
SET DEPTNO = NULL
WHERE DEPTNO = 10;
DELETE FROM DEPT_FK
WHERE DEPTNO = 10;
③ 현재 삭제하려는 열을 참조하는 자식 테이블의 FK 제약조건 해제
ex) ALTER TABLE EMP_FK DISABLE CONSTRAINT EMPFK_DEPTNO_FK;
SELECT * FROM DEPT_FK;
SELECT * FROM EMP_FK;
ALTER TABLE EMP_FK DISABLE CONSTRAINT EMPFK_DEPTNO_FK; --제약 조건 비활성화
--ALTER TABLE EMP_FK ENABLE CONSTRAINT EMPFK_DEPTNO_FK; --제약 조건 활성화
DELETE FROM DEPT_FK
WHERE DEPTNO = 10;
④ ON DELETE CASCADE
열 데이터를 삭제할 때 이 데이터를 참조하고 있는 데이터(자식 테이블)도 함께 삭제
EMP_FK 테이블을 만들 때, 'ON DELETE CASCADE' 조건 추가
--EMP_FK 테이블 생성부터_기존의 테이블 삭제(DROP TABLE ~;)
CREATE TABLE EMP_FK(
EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO) ON DELETE CASCADE
);
--DEPT_FK(부모 테이블) DEPTNO = 10 데이터 삭제
DELETE FROM DEPT_FK
WHERE DEPTNO = 10;
--결과 조회. 데이터가 제대로 삭제되었는지 확인
SELECT * FROM EMP_FK;
--EMP_FK 테이블(자식 테이블) 삭제
DELETE FROM EMP_FK;
⑤ ON DELETE SET NULL
열 데이터를 삭제할 때 이 데이터를 참조하는 데이터를 NULL로 수정
--EMP_FK 테이블 생성부터_기존의 테이블 삭제(DROP TABLE ~;)
CREATE TABLE EMP_FK(
EMPNO NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO) ON DELETE SET NULL
);
--데이터 입력 : DEPT_FK, EMP_FK
INSERT INTO DEPT_FK
VALUES(10, 'TEST_DNAME', 'TEST_LOC');
INSERT_INTO EMP_FK
VALUES(9999, 'TEST_NAME', 'TEST_JOB', NULL,
TO_DATE('2001/01/01', 'YYYY/MM/DD'), 3000, NULL, 10);
--DEPT_FK 테이블(부모) DEPTNO = 10 데이터 삭제
DELETE FROM DEPT_FK
WHERE DEPTNO = 10;
-- DEPT_FK의 DEPTNO = 10 데이터가 삭제되면서
-- EMP_FK의 DEPTNO 값을 NULL로 바꿈.
SELECT * FROM EMP_FK; --결과 조회
SELECT * FROM DEPT_FK;
(5) CHECK
열에 저장할 수 있는 값의 범위 또는 패턴 정의. 조건식 지정
-테이블 생성할 때, CHECK 제약 조건 설정
CREATE TABLE TABLE_CHECK(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LOGINPW_CK CHECK(LENGTH(LOGIN_PWD) > 3),
TEL VARCHAR2(20)
);
-데이터 입력 : 조건 미충족 시 오류
INSERT INTO TABLE_CHECK
VALUES('TEST_ID', '123', '010-1234-5678'); --조건 미충족 오류
INSERT INTO TABLE_CHECK
VALUES('TEST_ID', '1234', '010-1234-5678');
-제약 조건 데이터사전뷰 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_CHECK%';
(5) DEFAULT
저장값이 없는 경우 기본값 지정
-테이블 생성 시, DEFAULT 제약 조건 설정
CREATE TABLE TABLE_DEFAULT(
LOGIN_ID VARCHAR2(20) CONSTRAINT TBLCK2_LOGINID_PK PRIMARY KEY,
LOGIN_PWD VARCHAR2(20) DEFAULT '1234',
TEL
);
-데이터 입력 : DEFAULT값 입력되는지 확인
INSERT INTO TABLE_DEFAULT
VALUES('TEST_ID', NULL, '010-1234-5678'); --지정한 NULL값 입력
INSERT INTO TABLE_DEFAULT(LOGIN_ID, TEL)
VALUES('TEST_ID2', '010-9876-5432'); --지정하지 않아 기본값 입력(사전에 기본값 설정해둬야)
-