📚 Study/Oracle
Oracle :: 무결성, 제약조건(PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, CHECK)
bono-hye
2023. 10. 30. 23:54
(파일명 20231030_01_hr)
■ 무결성
■ 개체 무결성 (Entity Integrity)
- 릴레이션에 저장되는 튜플(tuple)의 유일성을 보장하기 위한 제약 조건
- ex ) EMP 테이블에 조건이 모두 같은 SMITH 2명이 있음
■ 참조 무결성 (Relational Integrity)
- 릴레이션 간의 데이터 일관성을 보장하기 위한 제약 조건
- ex ) 홍길동, 부서번호 60인데 DEPT 테이블에 부서번호 60은 존재하지 않음
■ 도메인 무결성 (Domain Integrity)
- 허용 가능한 값의 범위를 저장하기 위한 제약 조건
- ex ) HIREDATE에 1234가 입력됨
■ 제약조건의 종류
1. PRIMARY KEY(PK : P) → 기본키, 고유키, 식별키, 식별자
: 해당 컬럼의 값은 반드시 존재해야 하며, 유일해야 한다. (NOT NULL과 UNIQUE가 결합된 상태)
2. FOREIGN KEY(FK : F : R) → 외래키, 외부키, 참조키
: 해당 컬럼의 값은 참조되는 테이블의 컬럼 데이터들 중 하나와 일치하거나 NULL을 가짐
3. UNIQUE (UK : U)
: 테이블 내에서 해당 컬럼의 값은 항상 유일해야 한다.
4. NOT NULL (NN : CK : C)
: 해당 컬럼은 NULL을 포함할 수 없다.
5. CHECK (CK : C)
: 해당 컬럼에 저장 가능한 데이터의 범위나 조건을 지정한다.
■ PRIMARY KEY
1. 테이블에 대한 기본 키를 생성한다.
2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합.
기본 키는 테이블 당 하나만 존재한다. BUT, 반드시 하나의 컬럼으로만 구성되는 것은 아니다!
NULL 일 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수 없도록 처리한다. (유일성)
UNIQUE INDEX가 오라클 내부적으로 자동 생성된다.
3. 형식 및 구조
① 컬럼 레벨의 형식
컬럼명 데이터 타입 [CONSTRAINT CONSTRAINT명] PRIMARY KEY[(컬럼명, ...)]
② 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 PRIMARY KEY(컬럼명, ...)
4. CONSTRAINT 추가 시 CONSTRAINT 명을 생략하면 오라클 서버가 자동으로 CONSTRAINT명을 부여한다.
일반적으로 CONSTRAINT 명은 『테이블명_컬럼명_CONSTRAINT약자』 형식으로 기술한다.
--○ PK 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST1
( COL1 NUMBER(5) PRIMARY KEY
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST1이(가) 생성되었습니다.
SELECT *
FROM TBL_TEST1;
DESC TBL_TEST1;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 VARCHAR2(30)
*/
-- 데이터 입력
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(1, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(1, 'TEST');
--> 에러 발생
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(1, 'ABCD');
--> 에러 발생
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(2, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(3, NULL);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1) VALUES(4); --(COL2에는 NULL을 입력하겠다는 의미)
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1) VALUES(4);
--> 에러 발생
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(5, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST1(COL1, COL2) VALUES(NULL, NULL);
--> 에러 발생
INSERT INTO TBL_TEST1(COL2) VALUES('ABCD');
--> 에러 발생
-- 확인
SELECT *
FROM TBL_TEST1;
--==>>
/*
1 TEST
2 TEST
3 (null)
4 (null)
5 ABCD
*/
--○ 커밋
COMMIT;
--==>> 커밋 완료.
DESC TBL_TEST1;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5) → PK 제약조건 확인 불가
COL2 VARCHAR2(30)
*/
--※ 제약조건 확인
SELECT *
FROM USER_CONSTRAINTS;
--==>>
/*
HR REGION_ID_NN C REGIONS "REGION_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR REG_ID_PK P REGIONS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR REG_ID_PK
HR COUNTRY_ID_NN C COUNTRIES "COUNTRY_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR COUNTRY_C_ID_PK P COUNTRIES ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR COUNTRY_C_ID_PK
HR COUNTR_REG_FK R COUNTRIES HR REG_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR LOC_CITY_NN C LOCATIONS "CITY" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR LOC_ID_PK P LOCATIONS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR LOC_ID_PK
HR LOC_C_ID_FK R LOCATIONS HR COUNTRY_C_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR DEPT_NAME_NN C DEPARTMENTS "DEPARTMENT_NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR DEPT_ID_PK P DEPARTMENTS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR DEPT_ID_PK
HR DEPT_LOC_FK R DEPARTMENTS HR LOC_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JOB_TITLE_NN C JOBS "JOB_TITLE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JOB_ID_PK P JOBS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR JOB_ID_PK
HR EMP_LAST_NAME_NN C EMPLOYEES "LAST_NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_EMAIL_NN C EMPLOYEES "EMAIL" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_HIRE_DATE_NN C EMPLOYEES "HIRE_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_JOB_NN C EMPLOYEES "JOB_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_SALARY_MIN C EMPLOYEES salary > 0 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_EMAIL_UK U EMPLOYEES ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR EMP_EMAIL_UK
HR EMP_EMP_ID_PK P EMPLOYEES ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR EMP_EMP_ID_PK
HR EMP_DEPT_FK R EMPLOYEES HR DEPT_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_JOB_FK R EMPLOYEES HR JOB_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR EMP_MANAGER_FK R EMPLOYEES HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR DEPT_MGR_FK R DEPARTMENTS HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_EMPLOYEE_NN C JOB_HISTORY "EMPLOYEE_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_START_DATE_NN C JOB_HISTORY "START_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_END_DATE_NN C JOB_HISTORY "END_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_JOB_NN C JOB_HISTORY "JOB_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_DATE_INTERVAL C JOB_HISTORY end_date > start_date ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_EMP_ID_ST_DATE_PK P JOB_HISTORY ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29 HR JHIST_EMP_ID_ST_DATE_PK
HR JHIST_JOB_FK R JOB_HISTORY HR JOB_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_EMP_FK R JOB_HISTORY HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR JHIST_DEPT_FK R JOB_HISTORY HR DEPT_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2014-05-29
HR SYS_C004102 O EMP_DETAILS_VIEW ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 2014-05-29
HR SYS_C007015 P TBL_TEST1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2023-10-30 HR SYS_C007015
*/
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TBL_TEST1';
--==>> HR SYS_C007015 P TBL_TEST1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2023-10-30 HR SYS_C007015
--※ 제약조건이 지정된 컬럼 확인(조회)
SELECT *
FROM USER_CONS_COLUMNS;
--==>>
/*
HR REGION_ID_NN REGIONS REGION_ID
HR REG_ID_PK REGIONS REGION_ID 1
HR COUNTRY_ID_NN COUNTRIES COUNTRY_ID
HR COUNTRY_C_ID_PK COUNTRIES COUNTRY_ID 1
HR COUNTR_REG_FK COUNTRIES REGION_ID 1
HR LOC_ID_PK LOCATIONS LOCATION_ID 1
HR LOC_CITY_NN LOCATIONS CITY
HR LOC_C_ID_FK LOCATIONS COUNTRY_ID 1
HR DEPT_ID_PK DEPARTMENTS DEPARTMENT_ID 1
HR DEPT_NAME_NN DEPARTMENTS DEPARTMENT_NAME
HR DEPT_MGR_FK DEPARTMENTS MANAGER_ID 1
HR DEPT_LOC_FK DEPARTMENTS LOCATION_ID 1
HR JOB_ID_PK JOBS JOB_ID 1
HR JOB_TITLE_NN JOBS JOB_TITLE
HR EMP_EMP_ID_PK EMPLOYEES EMPLOYEE_ID 1
HR EMP_LAST_NAME_NN EMPLOYEES LAST_NAME
HR EMP_EMAIL_NN EMPLOYEES EMAIL
HR EMP_EMAIL_UK EMPLOYEES EMAIL 1
HR EMP_HIRE_DATE_NN EMPLOYEES HIRE_DATE
HR EMP_JOB_NN EMPLOYEES JOB_ID
HR EMP_JOB_FK EMPLOYEES JOB_ID 1
HR EMP_SALARY_MIN EMPLOYEES SALARY
HR EMP_MANAGER_FK EMPLOYEES MANAGER_ID 1
HR EMP_DEPT_FK EMPLOYEES DEPARTMENT_ID 1
HR JHIST_EMPLOYEE_NN JOB_HISTORY EMPLOYEE_ID
HR JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY EMPLOYEE_ID 1
HR JHIST_EMP_FK JOB_HISTORY EMPLOYEE_ID 1
HR JHIST_START_DATE_NN JOB_HISTORY START_DATE
HR JHIST_DATE_INTERVAL JOB_HISTORY START_DATE
HR JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY START_DATE 2
HR JHIST_END_DATE_NN JOB_HISTORY END_DATE
HR JHIST_DATE_INTERVAL JOB_HISTORY END_DATE
HR JHIST_JOB_NN JOB_HISTORY JOB_ID
HR JHIST_JOB_FK JOB_HISTORY JOB_ID 1
HR JHIST_DEPT_FK JOB_HISTORY DEPARTMENT_ID 1
HR SYS_C007015 TBL_TEST1 COL1 1
*/
SELECT *
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'TBL_TEST1';
--==>> HR SYS_C007015 TBL_TEST1 COL1 1
--○ USER_CONSTRAINTS 와 USER_CONS_COLUMNS 를 대상으로
-- 제약조건이 설정된 내용에 대해서
-- 소유주, 제약조건명, 테이블명, 제약조건종류, 컬럼명 항목을 조회한다.
SELECT UC.OWNER"소유주", UC.CONSTRAINT_NAME"제약조건명", UC.TABLE_NAME"테이블명", UC.CONSTRAINT_TYPE"제약조건종류", UCC.COLUMN_NAME"컬럼명"
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'TBL_TEST1';
--==>> HR SYS_C007015 TBL_TEST1 P COL1
--○ PK 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST2
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST2_COL1_PK PRIMARY KEY(COL1)
);
--==>> Table TBL_TEST2이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(1, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(1, 'TEST');
--==>> 에러 발생
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(1, 'ABCD');
--==>> 에러 발생
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(2, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(3, NULL);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1) VALUES(4);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1) VALUES(4);
--==>> 에러 발생
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(5, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST2(COL1, COL2) VALUES(NULL, NULL);
--==>> 에러 발생
INSERT INTO TBL_TEST2(COL2) VALUES('KKKK');
--==>> 에러 발생
--○ 확인
SELECT *
FROM TBL_TEST2;
--==>>
/*
1 TEST
2 ABCD
3 (null)
4 (null)
5 ABCD
*/
--○ 커밋
COMMIT;
--==>> 커밋 완료.
--○ USER_CONSTRAINTS 와 USER_CONS_COLUMNS 를 대상으로
-- 제약조건이 설정된 내용에 대해서
-- 소유주, 제약조건명, 테이블명, 제약조건종류, 컬럼명 항목을 조회한다.
SELECT UC.OWNER"소유주", UC.CONSTRAINT_NAME"제약조건명", UC.TABLE_NAME"테이블명", UC.CONSTRAINT_TYPE"제약조건종류", UCC.COLUMN_NAME"컬럼명"
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'TBL_TEST2';
--==>> HR TEST2_COL1_PK TBL_TEST2 P COL1
--○ PK 지정 실습(③ 다중 컬럼 PK 지정)
-- 테이블 생성
CREATE TABLE TBL_TEST3
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST3_COL1_COL2_PK PRIMARY KEY(COL1, COL2)
);
--==>> Table TBL_TEST3이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'TEST');
--==>> 에러 발생
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(1, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(2, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(2, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(3, NULL);
--==>> 에러 발생
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(NULL, 'TEST');
--==>> 에러 발생
INSERT INTO TBL_TEST3(COL1, COL2) VALUES(NULL, NULL);
--==>> 에러 발생
--○ 확인
SELECT *
FROM TBL_TEST3;
--==>>
/*
1 ABCD
1 TEST
2 ABCD
2 TEST
*/
--○ 커밋
COMMIT;
--==>> 커밋 완료.
SELECT UC.OWNER"소유주", UC.CONSTRAINT_NAME"제약조건명", UC.TABLE_NAME"테이블명", UC.CONSTRAINT_TYPE"제약조건종류", UCC.COLUMN_NAME"컬럼명"
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'TBL_TEST3';
--==>>
/*
소유주 제약조건명 테이블명 제약조건종류 컬럼명
HR TEST3_COL1_COL2_PK TBL_TEST3 P COL1
HR TEST3_COL1_COL2_PK TBL_TEST3 P COL2
*/
--○ PK 지정 실습(④ 테이블 생성 이후 제약조건 추가 설정)
-- 테이블 생성
CREATE TABLE TBL_TEST4
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST4이(가) 생성되었습니다.
--※ 이미 생성된(만들어져 있는) 상태의 테이블에
-- 부여하려는 제약조건을 위반한 데이터가 포함되어 있을 경우
-- 해당 테이블에 제약조건을 추가하는 것은 불가능하다.
-- 제약조건 추가
ALTER TABLE TBL_TEST4
ADD CONSTRAINT TEST4_COL1_PK PRIMARY KEY(COL1);
--==>> Table TBL_TEST4이(가) 변경되었습니다.
SELECT UC.OWNER"소유주", UC.CONSTRAINT_NAME"제약조건명", UC.TABLE_NAME"테이블명", UC.CONSTRAINT_TYPE"제약조건종류", UCC.COLUMN_NAME"컬럼명"
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND UC.TABLE_NAME = 'TBL_TEST4';
--==>> HR TEST4_COL1_PK TBL_TEST4 P COL1
--※ 제약조건 확인 전용 뷰(VIEW) 생성
CREATE OR REPLACE VIEW VIEW_CONSTCHECK
AS
SELECT UC.OWNER "OWNER"
, UC.CONSTRAINT_NAME "CONSTRAINT_NAME"
, UC.TABLE_NAME "TABLE_NAME"
, UC.CONSTRAINT_TYPE "CONSTRAINT_TYPE"
, UCC.COLUMN_NAME "COLUMN_NAME"
, UC.SEARCH_CONDITION "SEARCH_CONDITION"
, UC.DELETE_RULE "DELETE_RULE"
FROM USER_CONSTRAINTS UC JOIN USER_CONS_COLUMNS UCC
ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME;
--==>> View VIEW_CONSTCHECK이(가) 생성되었습니다.
--○ 생성된 뷰(VIEW)를 통한 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST4';
--==>> HR TEST4_COL1_PK TBL_TEST4 P COL1
■ UNIQUE (UK : U)
1. 테이블에서 지정한 컬럼의 데이터가 중복되지 않고 유일할 수 있도록 설정하는 제약 조건
PRIMARY KEY 와 유사한 제약조건이지만 NULL을 허용한다는 차이점 있음!
내부적으로 PRIMARY KEY와 마찬가지로 UNIQUE INDEX가 자동 생성되며
하나의 테이블 내에서 UNIQUE 제약조건은 여러 번 설정하는 것이 가능하다.
즉, 하나의 테이블에 UNIQUE 제약 조건을 여러개 만드는 것은 가능하다는 것!
2. 형식 및 구조
① 컬럼 레벨의 형식
컬럼명 데이터 타입 [CONSTRAINT CONSTRAINT명] UNIQUE
② 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 UNIQUE(컬럼명, ...)
--○ UK 지정 실습(① 컬럼 레벨의 형식)
CREATE TABLE TBL_TEST5
( COL1 NUMBER(5) PRIMARY KEY
, COL2 VARCHAR2(30) UNIQUE
);
--==>> Table TBL_TEST5이(가) 생성되었습니다.
-- 제약조건 조회
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST5';
--==>>
/*
HR SYS_C007019 TBL_TEST5 P COL1
HR SYS_C007020 TBL_TEST5 U COL2
*/
-- 데이터 입력
INSERT INTO TBL_TEST5(COL1, COL2) VALUES(1, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST5(COL1, COL2) VALUES(1, 'TEST');
--==>> 에러 발생
INSERT INTO TBL_TEST5(COL1, COL2) VALUES(2, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST5(COL1, COL2) VALUES(3, 'ABCD');
--==>> 에러 발생 (UNIQUE 제약 조건 위반)
INSERT INTO TBL_TEST5(COL1, COL2) VALUES(3, NULL);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST5(COL1) VALUES(4);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST5(COL1, COL2) VALUES(5, 'ABCD');
--==>> 에러 발생 (UNIQUE 제약 조건 위반)
-- 확인
SELECT *
FROM TBL_TEST5;
--==>>
/*
1 TEST
2 ABCD
3 (null)
4 (null)
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
--○ UK 지정 실습(① 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST6
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST6_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST6_COL2_UK UNIQUE(COL2)
);
--==>> Table TBL_TEST6이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST6';
--==>>
/*
HR TEST6_COL1_PK TBL_TEST6 P COL1
HR TEST6_COL2_UK TBL_TEST6 U COL2
*/
--○ UK 지정 실습(③ 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_TEST7
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST7이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST7';
--==>> 조회 결과 없음
-- 제약 조건 추가
ALTER TABLE TBL_TEST7
ADD CONSTRAINT TEST7_COL1_PK PRIMARY KEY(COL1);
-- +
ALTER TABLE TBL_TEST7
ADD CONSTRAINT TEST7_COL2_UK UNIQUE(COL2);
-- ↓
ALTER TABLE TBL_TEST7
ADD (CONSTRAINT TEST7_COL1_PK PRIMARY KEY(COL1) -- 이렇게 ADD해주면 위처럼 따로따로 안해줘도 된다.
, CONSTRAINT TEST7_COL2_UK UNIQUE(COL2));
--==>> Table TBL_TEST7이(가) 변경되었습니다.
-- 제약조건 추가 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST7';
--==>>
/*
HR TEST7_COL1_PK TBL_TEST7 P COL1
HR TEST7_COL2_UK TBL_TEST7 U COL2
*/
(파일명 20231031_01_hr)
■ CHECK(CK:C)
1. 컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건
컬럼에 입력되는 데이터를 검사하여 조건에 맞는 데이터만 입력될 수 있도록 함!
또한, 컬럼에서 수정되는 데이터를 검사하여 조건에 맞는 데이터로 수정되는 것만 허용하는 기능도 수행하게 된다.
2. 형식 및 구조
① 컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] CHECK(컬럼조건)
② 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT 명 CHECK(컬럼 조건)
--○ CK 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST8
( COL1 NUMBER(5) PRIMARY KEY
, COL2 VARCHAR2(30)
, COL3 NUMBER(3) CHECK(COL3 BETWEEN 0 AND 100)
);
--==>> Table TBL_TEST8이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES(1, '박범구', 100);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES(1, '엄재용', 100);
--==>> 에러 발생 (PRIMARY KEY 제약 조건 위반)
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES(2, '엄재용', 101);
--==>> 에러 발생 (CHECK 제약 조건 위반)
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES(2, '엄재용', -1);
--==>> 에러 발생 (CHECK 제약 조건 위반)
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES(2, '엄재용', 80);
--==>> 1 행 이(가) 삽입되었습니다.
-- 확인
SELECT *
FROM TBL_TEST8;
--==>>
/*
1 박범구 100
2 엄재용 80
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST8';
--==>>
/*
HR SYS_C007025 TBL_TEST8 C COL3 COL3 BETWEEN 0 AND 100
HR SYS_C007026 TBL_TEST8 P COL1
*/
--○ CK 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST9
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)
, CONSTRAINT TEST9_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST9_COL3_CK CHECK(COL3 BETWEEN 0 AND 100)
);
--==>> Table TBL_TEST9이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES(1, '박범구', 100);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES(1, '엄재용', 100);
--==>> 에러 발생 (PRIMARY KEY 제약 조건 위반)
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES(2, '엄재용', 101);
--==>> 에러 발생 (CHECK 제약 조건 위반)
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES(2, '엄재용', -1);
--==>> 에러 발생 (CHECK 제약 조건 위반)
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES(2, '엄재용', 80);
--==>> 1 행 이(가) 삽입되었습니다.
-- 확인
SELECT *
FROM TBL_TEST9;
--==>>
/*
1 박범구 100
2 엄재용 80
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST9';
--==>>
/*
HR TEST9_COL3_CK TBL_TEST9 C COL3 COL3 BETWEEN 0 AND 100
HR TEST9_COL1_PK TBL_TEST9 P COL1
*/
--○ CK 지정 실습(③ 테이블 생성 후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_TEST10
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)
);
--==>> Table TBL_TEST10이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST10';
--==>> 조회 결과 없음
-- 제약조건 추가
ALTER TABLE TBL_TEST10
ADD ( CONSTRAINT TEST10_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST10_COL3_CK CHECK(COL3 BETWEEN 0 AND 100) );
--==>> Table TBL_TEST10이(가) 변경되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST10';
--==>>
/*
HR TEST10_COL1_PK TBL_TEST10 P COL1
HR TEST10_COL3_CK TBL_TEST10 C COL3 COL3 BETWEEN 0 AND 100
*/
-- 테이블 생성
CREATE TABLE TBL_TESTMEMBER
( SID NUMBER
, NAME VARCHAR2(30)
, SSN CHAR(14) -- 입력 형태 → 'YYMMDD-NNNNNNN'
, TEL VARCHAR2(40)
);
--==>> Table TBL_TESTMEMBER이(가) 생성되었습니다.
--○ TBL_TESTMEMBER 테이블의 SSN 컬럼(주민등록번호 컬럼)에서
-- 데이터 입력이나 수정 시, 성별이 유효한 데이터만 입력될 수 있도록
-- 체크 제약조건을 추가할 수 있도록 한다.
-- (→ 주민번호 특정 자리에 입력 가능한 데이터를 1, 2, 3, 4만 가능하도록 처리)
-- 또한, SID 컬럼에는 PRIMARY KEY 제약조건을 설정할 수 있도록 한다.
ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TESTMEMBER_SSN_CK CHECK (SUBSTR(SSN,8,1) IN ('1','2','3','4')) );
--==>> Table TBL_TESTMEMBER이(가) 변경되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TESTMEMBER';
-- 데이터 입력 테스트
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(1, '이윤수', '950106-1234567', '010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(2, '박나영', '990208-2234567', '010-2222-2222');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(3, '최혜인', '070811-4234567', '010-3333-3333');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(4, '길현욱', '090211-3234567', '010-4444-4444');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(5, '정현욱', '000220-5234567', '010-5555-5555');
--==>> 에러 발생 (CHECK 제약조건 위반)
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(5, '정현욱', '000220-6234567', '010-5555-5555');
--==>> 에러 발생 (CHECK 제약조건 위반)
-- 확인
SELECT *
FROM TBL_TESTMEMBER;
--==>>
/*
1 이윤수 950106-1234567 010-1111-1111
2 박나영 990208-2234567 010-2222-2222
3 최혜인 070811-4234567 010-3333-3333
4 길현욱 090211-3234567 010-4444-4444
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
■ FOREIGN KEY(FK:F:R)
1. 참조 키(R) 또는 외래 키 (FK:F)는 두 테이블의 데이터 간 연결을 설정하고 강제 적용시키는데 사용되는 열이다.
한 테이블의 기본 키 값이 있는 열을
다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다.
이 때, 두 번째 테이블에 추가되는 열이 외래키가 된다.
2. 부모 테이블(참조받는 컬럼이 포함된 테이블)이 먼저 생성된 후
자식 테이블(참조하는 컬럼이 포함된 테이블)이 생성되어야 한다.
이 때, 자식 테이블에 FOREIGN KEY 제약조건이 설정된다.
3. 형식 및 구조
① 컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT CONSTRINAT 명]
REGERENCES 참조테이블명 (참조컬럼명)
[ON DELETE CASCADE | ON DELETE SET NULL] → 추가 옵션
② 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 FOREIGN KEY(컬럼명)
REFERENCES 참조테이블명(참조컬럼명)
[ON DELETE CASCADE | ON DELETE SET NULL] → 추가 옵션
4. FOREIGN KEY 생성 시 주의사항
- 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
- 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 설정되어 있어야 한다.
- 테이블 사이에 PRIMARY KEY 와 FOREIGN KEY 가 정의되어 있으면
PRIMARY KEY 제약조건이 설정된 데이터 삭제 시
FOREIGN KEY 컬럼에 그 값이 입력되어 있는 경우 삭제되지 않는다.
(즉, 자식 테이블에 참조하는 레코드가 존재할 경우 부모 테이블의 참조받는 해당 레코드는 삭제할 수 없음)
- 단, FK 설정 과정에서 『ON DELETE CASCADE』나 『ON DELETE SET NULL』 옵션을 사용하여 설정한 경우에는
삭제가 가능하다.
- 또한, 부모 테이블을 제거하기 위해서는 자식 테이블을 먼저 제거해야 한다.
-- ※ FOREIGN KEY 제약조건을 설정하는 실습을 진행하기 위해서는
-- 부모 테이블의 생성 작업을 먼저 수행해야 한다.
-- 그리고 이 때, 부모 테이블에는 반드시 PK 또는 UK 제약조건이
-- 설정된 컬럼이 존재해야 한다.
-- 부모 테이블 생성
CREATE TABLE TBL_JOBS
( JIKWI_ID NUMBER
, JIKWI_NAME VARCHAR2(30)
, CONSTRAINT JOBS_ID_PK PRIMARY KEY(JIKWI_ID) -- 부모 테이블의 JIKWI_ID
);
--==>> Table TBL_JOBS이(가) 생성되었습니다.
-- 부모 테이블에 데이터 입력
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(1,'사원');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(2,'대리');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(3,'과장');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(4,'부장');
--==>> 1 행 이(가) 삽입되었습니다. * 4
-- 확인
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
4 부장
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
--○ FK 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_EMP1
( SID NUMBER PRIMARY KEY
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER REFERENCES TBL_JOBS(JIKWI_ID)
);
--==>> Table TBL_EMP1이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>>
/*
HR SYS_C007034 TBL_EMP1 P SID
HR SYS_C007035 TBL_EMP1 R JIKWI_ID NO ACTION
*/
-- 데이터 입력
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(1, '노은하', 1);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(2, '박가영', 2);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(3, '채다선', 3);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(4, '김수환', 4);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(5, '김다슬', 5);
--==>> 에러 발생 (부모 테이블 JIKWI_ID에 1~4까지의 값만 있어서)
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(5, '김다슬', 1);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_EMP1(SID, NAME) VALUES(6, '오수경');
--==>> 1 행 이(가) 삽입되었습니다.
-- 확인
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 노은하 1
2 박가영 2
3 채다선 3
4 김수환 4
5 김다슬 1
6 오수경 (null)
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
--○ FK 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_EMP2
( SID NUMBER
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER
, CONSTRAINT EMP2_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP2_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID) -- 자식 테이블 JIKWI_ID
REFERENCES TBL_JOBS(JIKWI_ID) -- 부모 테이블 JIKWI_ID
);
--==>> Table TBL_EMP2이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP2';
--==>>
/*
HR EMP2_SID_PK TBL_EMP2 P SID
HR EMP2_JIKWI_ID_FK TBL_EMP2 R JIKWI_ID NO ACTION
*/
--○ FK 지정 실습(③ 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_EMP3
( SID NUMBER
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER
);
--==>> Table TBL_EMP3이(가) 생성되었습니다.
-- 제약조건 추가
ALTER TABLE TBL_EMP3
ADD ( CONSTRAINT EMP3_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
REFERENCES TBL_JOBS(JIKWI_ID) );
--==>> Table TBL_EMP3이(가) 변경되었습니다.
-- 제약조건 제거
ALTER TABLE TBL_EMP3
DROP CONSTRAINT EMP3_JIKWI_ID_FK;
--==>> Table TBL_EMP3이(가) 변경되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>> HR EMP3_SID_PK TBL_EMP3 P SID
-- 다시 제약조건 추가
ALTER TABLE TBL_EMP3
ADD CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
REFERENCES TBL_JOBS(JIKWI_ID);
--==>> Table TBL_EMP3이(가) 변경되었습니다.
-- 제약조건 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>>
/*
HR EMP3_SID_PK TBL_EMP3 P SID
HR EMP3_JIKWI_ID_FK TBL_EMP3 R JIKWI_ID NO ACTION
*/
-- 4. FOREIGN KEY 생성 시 주의사항
-- 참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
-- 참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 설정되어 있어야 한다.
-- 테이블 사이에 PRIMARY KEY 와 FOREIGN KEY 가 정의되어 있으면
-- PRIMARY KEY 제약조건이 설정된 데이터 삭제 시
-- FOREIGN KEY 컬럼에 그 값이 입력되어 있는 경우 삭제되지 않는다.
-- (즉, 자식 테이블에 참조하는 레코드가 존재할 경우
-- 부모 테이블의 참조받는 해당 레코드는 삭제할 수 없다는 것이다.)
-- 단, FK 설정 과정에서 『ON DELETE CASCADE』나 『ON DELETE SET NULL』 옵션을
-- 사용하여 설정한 경우에는 삭제가 가능하다.
-- 또한, 부모 테이블을 제거하기 위해서는 자식 테이블을 먼저 제거해야 한다.
-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
4 부장
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 노은하 1
2 박가영 2
3 채다선 3
4 김수환 4
5 김다슬 1
6 오수경 (null)
*/
-- 부모 테이블 제거 시도
DROP TABLE TBL_JOBS;
--==>> 에러 발생
-- (ORA-02449: unique/primary keys in table referenced by foreign keys)
-- 부모 테이블의 부장 직위 데이터 삭제 시도
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID = 4;
--==>> 4 부장
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID = 4;
--==>> 에러 발생
-- (ORA-02292: integrity constraint (HR.SYS_C007035) violated - child record found)
-- 김수환 부장의 직위를 사원으로 변경
UPDATE TBL_EMP1
SET JIKWI_ID=1
WHERE SID=4;
--==>> 1 행 이(가) 업데이트되었습니다.
-- 확인
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 노은하 1
2 박가영 2
3 채다선 3
4 김수환 1
5 김다슬 1
6 오수경
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
-- 부모 테이블(TBL_JOBS)의 부장 데이터를 참조하고 있는
-- 자식 테이블(TBL_ENP1)의 데이터가 존재하지 않는 상황
-- 이와 같은 상황에서 부모 테이블(TBL_JOBS)의
-- 부장 데이터 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID = 4;
--==>> 1 행 이(가) 삭제되었습니다.
-- 확인
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
- 『ON DELETE CASCADE』 옵션 지정
--※ 부모 테이블의 데이터를 자유롭게(?) 삭제하기 위해서는
-- 『ON DELETE CASCADE』 옵션 지정이 필요하다.
-- TBL_EMP1 테이블(자식 테이블)에서 FK 제약조건을 제거한 후
-- CASCADE 옵션을 포함하여 다시 FK 제약조건을 설정한다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>>
/*
HR SYS_C007034 TBL_EMP1 P SID
HR SYS_C007035 TBL_EMP1 R JIKWI_ID NO ACTION ◀◀◀◀ 제거하려는 제약조건
*/
-- 제약조건 제거
ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007035;
--==>> Table TBL_EMP1이(가) 변경되었습니다.
-- 제약조건 제거 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>> HR SYS_C007034 TBL_EMP1 P SID
-- 『ON DELETE CASCADE』 옵션이 포함된 내용으로 제약조건 다시 지정
ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
REFERENCES TBL_JOBS(JIKWI_ID)
ON DELETE CASCADE;
--==>> Table TBL_EMP1이(가) 변경되었습니다.
-- 제약조건 생성 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>>
/*
HR SYS_C007034 TBL_EMP1 P SID
HR EMP1_JIKWI_ID_FK TBL_EMP1 R JIKWI_ID CASCADE
*/
--※ CASCADE 옵션을 지정한 후에는
-- 참조받고 있는 부모 테이블의 데이터를
-- 언제든지 자유롭게 삭제하는 것이 가능하다.
-- 단, 부모 테이블의 데이터가 삭제될 경우
-- 이를 참조하는 자식 테이블의 데이터도 모~~~~두 함께 삭제된다.
-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 노은하 1
2 박가영 2
3 채다선 3
4 김수환 1
5 김다슬 1
6 오수경 (null)
*/
-- 부모 테이블(TBL_JOBS)에서 과장 데이터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=3;
--==>> 3 과장
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=3;
--==>> 1 행 이(가) 삭제되었습니다.
-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 노은하 1
2 박가영 2
4 김수환 1
5 김다슬 1
6 오수경 (null)
*/
-- 부모 테이블(TBL_JOBS)에서 사원 데이터 삭제
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 1 행 이(가) 삭제되었습니다.
-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
2 대리
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
2 박가영 2
6 오수경 (null)
*/
DROP TABLE TBL_EMP2;
--==>> Table TBL_EMP2이(가) 삭제되었습니다.
DROP TABLE TBL_EMP3;
--==>> Table TBL_EMP3이(가) 삭제되었습니다.
DROP TABLE TBL_JOBS;
--==>> 에러 발생
-- (ORA-02449: unique/primary keys in table referenced by foreign keys)
DROP TABLE TBL_EMP1;
--==>> Table TBL_EMP1이(가) 삭제되었습니다.
DROP TABLE TBL_JOBS;
--==>> Table TBL_JOBS이(가) 삭제되었습니다.
■ NOT NULL(NN:CK:C)
1. 테이블에서 지정한 컬럼의 데이터가 NULL 인 상태를 갖지 못하도록 하는 제약조건
2. 형식 및 구조
① 컬럼 레벨의 형식 → 이걸 디폴트로 사용하자~
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] NOT NULL
② 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 CHECK(컬럼명 IS NOT NULL)
3. 기존에 생성되어 있는 테이블에 NOT NULL 제약조건을 추가할 경우 ADD 보다 MODIFY 절이 더 많이 사용된다.
ALTER TABLE 테이블명
MODIFY 컬럼명 데이터타입 NOT NULL;
4. 기존 테이블에 데이터가 이미 들어있지 않은 컬럼(→ NULL 인 상태)을
NOT NULL 제약조건을 갖도록 수정하는 경우에는 에러 발생한다.
--○ NULL NULL 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST11
( COL1 NUMBER(5) PRIMARY KEY
, COL2 VARCHAR2(30) NOT NULL
);
--==>> Table TBL_TEST11이(가) 생성되었습니다.
-- 데이터 입력
INSERT INTO TBL_TEST11(COL1, COL2) VALUES(1, 'TEST');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST11(COL1, COL2) VALUES(2, 'ABCD');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TEST11(COL1, COL2) VALUES(3, NULL);
--==>> 에러 발생
INSERT INTO TBL_TEST11(COL1) VALUES(4);
--==>> 에러 발생
-- 확인
SELECT *
FROM TBL_TEST11;
--==>>
/*
1 TEST
2 ABCD
*/
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST11';
--==>>
/*
HR SYS_C007042 TBL_TEST11 C COL2 "COL2" IS NOT NULL
HR SYS_C007043 TBL_TEST11 P COL1
*/
--○ NOT NULL 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST12
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST12_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST12_COL2_NN CHECK(COL2 IS NOT NULL)
);
--==>> Table TBL_TEST12이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST12';
--==>>
/*
HR TEST12_COL2_NN TBL_TEST12 C COL2 COL2 IS NOT NULL
HR TEST12_COL1_PK TBL_TEST12 P COL1
*/
--○ NOT NULL 지정 실습(③ 테이블 생성 이후 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_TEST13
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
);
--==>> Table TBL_TEST13이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST13';
--==>>> 조회 결과 없음
-- 제약조건 추가
ALTER TABLE TBL_TEST13
ADD ( CONSTRAINT TEST13_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST13_COL2_NN CHECK(COL2 IS NOT NULL) );
--==>> Table TBL_TEST13이(가) 변경되었습니다.
-- 제약조건 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST13';
--==>>
/*
HR TEST13_COL1_PK TBL_TEST13 P COL1
HR TEST13_COL2_NN TBL_TEST13 C COL2 COL2 IS NOT NULL
*/
--※ NOT NULL 제약조건만 TBL_TEST13 테이블의 COL2 에 추가하는 경우
-- 다음과 같은 방법을 사용하는 것도 가능하다.
ALTER TABLE TBL_TEST13
MODIFY COL2 NOT NULL;
--==>> Table TBL_TEST13이(가) 변경되었습니다.
-- 컬럼 레벨에서 NOT NULL 제약조건을 지정한 테이블(TBL_TEST11)
DESC TBL_TEST11;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 NOT NULL VARCHAR2(30)
*/
-- 테이블 레벨에서 NOT NULL 제약조건을 지정한 테이블(TBL_TEST12)
DESC TBL_TEST12
--==>>
-- 테이블 레벨에서 NOT NULL 부여하면 제약조건을 보여주는 쿼리문을 통해서만 NOT NULL인 것을 확인할 수 있어용~ 그래서 컬럼 레벨로 작성하라고 했어용~~
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 VARCHAR2(30)
*/
-- 테이블 생성 이후 ADD 를 통해 NOT NULL 제약조건을 추가하였으며
-- 여기에 더하여, MODIFY 절을 통해 NOT NULL 제약조건을 추가한 테이블(TBL_TEST13)
DESC TBL_TEST13;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 NOT NULL VARCHAR2(30)
*/
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME IN ('TBL_TEST11', 'TBL_TEST12', 'TBL_TEST13');
--==>>
/*
HR SYS_C007042 TBL_TEST11 C COL2 "COL2" IS NOT NULL → 컬럼 형태로 이름 붙여 만드는 걸 권장하겠다.
HR SYS_C007043 TBL_TEST11 P COL1
HR TEST12_COL2_NN TBL_TEST12 C COL2 COL2 IS NOT NULL
HR TEST12_COL1_PK TBL_TEST12 P COL1
HR TEST13_COL1_PK TBL_TEST13 P COL1
HR TEST13_COL2_NN TBL_TEST13 C COL2 COL2 IS NOT NULL
HR SYS_C007048 TBL_TEST13 C COL2 "COL2" IS NOT NULL → 모디파이를 통해 생성한 것
*/
■ DEFAULT 표현식
1. INSERT와 UPDATE 문에서 특정 값이 아닌 기본 값을 입력하도록 처리할 수 있다.
2. 형식 및 구조
컬럼명 데이터타입 DEFAULT 기본값
3. INSERT 명령 시 해당 컬럼에 입력될 값을 할당하지 않거나
DEFAULT 키워드를 활용하여 기본으로 설정된 값을 입력하도록 할 수 있다.
4. DEFAULT 키워드와 다른 제약(NOT NULL 등) 표기가 함께 사용되어야 하는 경우
DEFAULT 키워드를 먼저 표기(작성)할 것을 권장한다.
--○ DEFAULT 표현식 적용 실습
-- 테이블 실습
CREATE TABLE TBL_BBL -- 게시판 테이블 생성
( SID NUMBER PRIMARY KEY -- 게시물 번호 → 식별자 → 자동 증가
, NAME VARCHAR2(20) -- 게시물 작성자
, CONTENTS VARCHAR2(200) -- 게시물 내용
, WRITEDAY DATE DEFAULT SYSDATE -- 게시물 작성일
, COUNTS NUMBER DEFAULT 0 -- 게시물 조회수
, COMMENTS NUMBER DEFAULT 0 -- 게시물 댓글 갯수
);
--==>> Table TBL_BBL이(가) 생성되었습니다.
--※ SID 를 자동 증가 값으로 운영하려면 시퀀스 객체가 필요하다.
-- 자동으로 입력되는 컬럼은 사용자의 입력 항목ㅇ서 제외시킬 수 있다.
-- 시퀀스 생성
CREATE SEQUENCE SEQ_BBS
NOCACHE;
--==>> Sequence SEQ_BBS이(가) 생성되었습니다.
-- 날짜 관련 세션 설정
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.
-- 게시물 작성
INSERT INTO TBL_BBL(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '김다슬', '오라클 DEFAULT 표현식을 실습중입니다.'
, TO_DATE('2023-10-31 14:39:10', 'YYYY-MM-DD HH24:MI:SS'), 0, 0);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BBL(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '김다슬', '오라클 DEFAULT 표현식을 실습중입니다.', SYSDATE, 0, 0);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BBL(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '노은하', '계속 실습중입니다.', DEFAULT, 0, 0);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BBL(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '문정환', '열심히 실습중입니다.', DEFAULT, DEFAULT, DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_BBL(SID, NAME, CONTENTS)
VALUES(SEQ_BBS.NEXTVAL, '이윤수', '무진장 실습중입니다.');
--==>> 1 행 이(가) 삽입되었습니다.
-- 확인
SELECT *
FROM TBL_BBL;
--==>>
/*
1 김다슬 오라클 DEFAULT 표현식을 실습중입니다. 2023-10-31 14:39:10 0 0
2 김다슬 오라클 DEFAULT 표현식을 실습중입니다. 2023-10-31 14:44:57 0 0
3 노은하 계속 실습중입니다. 2023-10-31 14:46:14 0 0
4 문정환 열심히 실습중입니다. 2023-10-31 14:47:10 0 0
5 이윤수 무진장 실습중입니다. 2023-10-31 14:48:05 0 0
*/
--○ DEFAULT 표현식 조회(확인)
SELECT *
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TBL_BBL';
--==>>
/*
TBL_BBL SID NUMBER 22 N 1
TBL_BBL NAME VARCHAR2 20 Y 2
TBL_BBL CONTENTS VARCHAR2 200 Y 3
TBL_BBL WRITEDAY DATE 7 Y 4 8 "SYSDATE"
TBL_BBL COUNTS NUMBER 22 Y 5 2 "0"
TBL_BBL COMMENTS NUMBER 22 Y 6 2 "0"
*/
--○ 테이블 생성 이후 DEFAULT 표현식 추가 / 변경
ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT 기본값;
--○ 기존의 DEFAULT 표현식 제거
ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT NULL;
COMMIT;
--==>> 커밋 완료.