무결성 - DBMS에서 데이터를 입력할 때, 잘못 입력하게 되면 데이터 하나만 문제가 되는 것이 아니라 관계 테이블 더 나아가 DB 자체의 신뢰를 잃어버리기 때문에 오라클에서 제약조건들을 통해서 데이터를 잘못 입력하지 못하게 도와주는 것
--■■■ 무결성(Integrity) ■■■--
/*
1. 무결성에는 개체 무결성(Entitiy Intergrity)
참조 무결성(Relational Integrity)
도메인 무결성(Domain Integrity) 이 있다.
2. 개체 무결성 // 중복된 행을 못들어가게끔하는 제약조건
개체 무결성은 릴레이션에서 저장되는 튜플(tuple)의
유일성을 보장하기 위한 제약조건이다
3. 참조 무결성 // DEPT부서번호 10~40인데, 70번인 사원이 있다? - 사원문제? 부서 문제?
참조 무결성은 릴레이션 간의 데이터 일관성을
보장하기 위한 제약조건이다.
4. 도메인 무결성 // 기본적 : 데이터 타입, 데이터 길이, 숫자일때 범위 등
도메인 무결성은 허용 가능한 값의 범위를
지정하기 위한 제약조건이다.
5. 제약조건의 종류 // (줄임말) 필요에의해서 해당 제약 조건들을 구성해서 만드는 것
// 자동으로 만들어지는 것이 아니다
- PRIMARY KEY(PK:P) → 부모 테이블의 참조받는 컬럼 → 기본키, 식별자
해당 컬럼의 값은 반드시 존재해야 하며, 유일해야 한다.
(UNIQUE 와 NOT NULL 이 결합된 상태)
// 테이블을 나누는 기준 : 기준에 의해 식별자를 포함하게 나눈다
- FOREIGN KEY(FK:F:R) → 자식 테이블의 참조하는 컬럼 → 외래키, 외부키, 참조키
해당 컬럼의 값은 참조되는 테이블의 컬럼데이터들 중 하나와
일치하거나 NULL 을 가진다.
// 자식테이블 내에서 확인해볼 수 없고 바깥 테이블에서 확인해야하기 때문에 외래키, 외부키 라고 함
// R 은 레퍼런스의 R
// NULL = 인턴 ! 아직 부서번호를 가지지 못함
- UNIQUE(UK:U)
테이블 내에서 해당 컬럼의 값은 항상 유일해야 한다.
// 고유한 값 ex) 사원번호, 주민등록번호
// PRIMARY KEY 로 사원번호가 지정 되어있을 때, PRIMARY KEY 는 최대 1개 이므로,
// 주민번호의 경우에도 유일해야하기에 UNIQUE 라는 제약조건을 사용한다
- NOT NULL(NN:CK:C)
해당 컬럼은 NULL을 포함할 수 없다. (비어있는 상태이면 안된다.)
// 내부적으로는 CK 의 일환이기도 하여 같은 축약어를 사용하기도 한다.
- CHECK(CK:C)
해당 컬럼에서 저장 가능한 데이터의 값의 범위나 조건을 지정한다.
// 성별에는 남 또는 여 밖에 들어갈 수 없다.
// 성적에는 0부터 100까지만 넣을 수 있다
// -> 데이터 타입으로 지정하지 못하는 세부적인 제약 조건
*/
PRIMARY KEY
---■■■ PRIMARY KEY ■■■--
-- 1. 테이블에 대한 기본 키를 생성한다.
-- 2. 테이블에서 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다.
-- // EMP 테이블에서) 이름도 같고, 입사일도 같고, 급여도 같을 수 있으나 사원번호(PK)는 같을 수 없다!
-- 기본 키는 테이블 당 최대 하나만 존재한다.
-- // 없을 수 있다고는 하나, 관리적 속도적인 면에서도 유리하고 편하기 때문에 만들어 두는 것이 좋다.
-- 그러나 반드시 하나의 컬럼으로만 구성되는 것은 아니다.
-- NULL 일 수 없고, 이미 테이블에 존재하고 있는 데이터를
-- 다시 입력할 수 없도록 처리한다
-- UNIQUE INDEX 가 자동으로 생성된다. (오라클이 자체적으로 만든다.)
-- // 데이터가 많아지면 많아질 수록, UNIQUE INDEX 를 자동으로 생성해준다는것에서 더 만들어야하는것이 된다 (색인 자동 생성)
-- 3. 형식 및 구조
-- //제약조건을 지정하는 방법 2가지 -> 기본적으로 테이블레벨로 구성하는 것이 좋다
-- //코드레벨에서 친절한 편이고, 테이블레벨 배워두면 컬럼레벨로 하는건 쉽다
-- ① 컬럼 레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] PRIMARY KEY[(컬럼명, ...)]
-- ② 테이블 레벨의 형식
-- 컬럼명 데이터타입,
-- 컬럼명 데이터타입,
-- CONSTRAINT CONSTRAINT명 PRIMARY KEY(컬럼명[,...])
-- 4. CONSTRAINT 추가 시 CONSTRAINT 명을 생략하면
-- 오라클 서버가 자동적으로 CONSTRAINT 명을 부여하게 된다.
--// 중요하기 때문에 오라클이 자체적으로 붙여주는 것이나, 직접 붙여주는 것이 좋다.
-- 일반적으로 CONSTRAINT 명은 『테이블명_컬럼명_CONSTRAINT약어』
-- 형식으로 기술한다.
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_C007057 P TBL_TEST1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2023-04-04 HR SYS_C007057
---------- 오라클이 자동으로 붙여준 이름
*/
--// 해당되는 테이블의 제약조건만 확인하는 구문
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TBL_TEST1';
--==>>
/*
HR SYS_C007057 P TBL_TEST1 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2023-04-04 HR SYS_C007057
*/
--○ 제약조건이 지정된 컬럼 확인(조회)
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_C007057 TBL_TEST1 COL1 1
*/
--// 해당되는 컬럼의 제약조건만 확인하는 구문
SELECT *
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'TBL_TEST1';
--==>> HR SYS_C007057 TBL_TEST1 COL1 1
DESC USER_CONSTRAINTS;
DESC USER_CONS_COLUMNS;
OWNER VARCHAR2(120) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(120) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14)
OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER
- 두 테이블 사이에 공통 컬럼(항목) : OWNER, CONSTRAINT_NAME, TABLE_NAME → OWNER
- 이 중에서 조인 시에 사용하는 컬럼 : CONSTRAINT_NAME → TABLE_NAME 사용하면 제약조건이 여러개일 CROSS JOIN 형태로 나와서 정확한 제약조건을 확인할 수 없다.
--○ PK 지정 실습 (③ 다중 컬럼 PK 지정 → 복합 프라이머리 키)
-- 테이블 생성
CREATE TABLE TBL_TEST3
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINTS TEST3_COL1_COL2_PK PRIMARY KEY(COL1,COL2) --// 하나로 묶어서 구성하겠다 하고 전달
);
--===>> Table TBL_TEST3이(가) 생성되었습니다.
/*
CREATE TABLE TBL_TEST3
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINTS TEST3_COL1_PK PRIMARY KEY(COL1) ┐(X)
, CONSTRAINTS TEST3_COL2_PK PRIMARY KEY(COL2) ┘
); --// 이렇게 되면 PK를 두 개 만들겠다는 논리적인 구성이 되기 때문에 하면 안된다!
*/
-- 데이터 입력
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(1,'TEST');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(2,'ABCD');
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(3,NULL); --> 에러 발생
INSERT INTO TBL_TEST3(COL1) VALUES(4); --> 에러 발생 // 위와 동일한 구문
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(2,'ABCD'); --> 에러 발생
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(3,'ABCD'); --// 주의할 것. 복합 프라이머리키는 두가지 합쳐서 겹치는지 안겹치는지 확인
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(1,'ABCD'); --// 상동
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(2,'KKKK'); --// 상동
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(5,'ABCD'); --// 상동
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(NULL,NULL); --> 에러 발생
INSERT INTO TBL_TEST3(COL1,COL2) VALUES(NULL,'STUDY');--> 에러 발생
INSERT INTO TBL_TEST3(COL2) VALUES('STUDY'); --> 에러 발생
COMMIT;
--==>> 커밋 완료.
④ PK 지정실습 ( 테이블 생성 이후 제약조건 추가 → PK 지정) - 이미 만들어져있는 테이블에 제약조건을 추가할 때, 기존 데이터가 제약조건에 위반되는 사항이 있는지 확인되어야 함 (위반 데이터들을 변경,삭제 해주지 않는 이상에는 만들어지지 않는다)
--○ PK 지정실습 (④ 테이블 생성 이후 제약조건 추가 → 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이(가) 변경되었습니다.
-----------------------------------------------------------------------------------------
--※ 제약조건 확인용 전용 뷰(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)
--■■■ UNIQUE(UK:U) ■■■--
-- 1. 테이블에서 지정한 컬럼의 데이터가 중복되지 않고
-- 테이블 내에서 유일할 수 있도록 설정하는 제약조건.
-- PRIMARY KEY 와 유사한 제약조건이지만, NULL 을 허용한다는 차이가 있다. // PK 와 차이점 1 NOT NULL!
-- 내부적으로 PRIMARY KEY 와 마찬가지로 UNIQUE INDEX 가 자동 생성된다.
-- 하나의 테이블 내에서 UNIQUE 제약조건은 여러 번 설정하는 것이 가능하다. // PK 와 차이점 2 여러개 가능!
-- 즉, 하나의 테이블에 UNIQUE 제약조건을 여러 개 만드는 것이 가능하다는 것이다.
-- 2. 형식 및 구조
-- ① 컬럼레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINTS 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_C007061 TBL_TEST5 P COL1 //PK
HR SYS_C007062 TBL_TEST5 U COL2 //UNIQUE
*/
-- 데이터 입력
INSERT INTO TBL_TEST5(COL1,COL2) VALUES (1,'TEST');
INSERT INTO TBL_TEST5(COL1,COL2) VALUES (2,'ABCD');
INSERT INTO TBL_TEST5(COL1,COL2) VALUES (3, NULL);
INSERT INTO TBL_TEST5(COL1) VALUES (4);
INSERT INTO TBL_TEST5(COL1,COL2) VALUES (5,'ABCD'); --> 에러 발생 UNIQUE
INSERT INTO TBL_TEST5(COL1,COL2) VALUES (6, NULL);
INSERT INTO TBL_TEST5(COL1,COL2) VALUES (7, NULL);
SELECT *
FROM TBL_TEST5;
--// NULL 값은 고유한 값이 아님을 확인 가능. (NULL 중복 입력 됨)
COMMIT;
--==>> 커밋 완료.
--○ UK 지정 실습 (③ 테이블 생성 이후 제약조건 추가 → 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)
, 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
*/
CHECK (CK:C)
--■■■ CHECK(CK:C) ■■■--
--// 어떤 테이블을 대상으로 특정 컬럼에 특정 데이터를 추가/변경 하려고 할 때,
--// 데이터를 받아 들일지 말지 결정하는게 어떤 파트?
--// ex) EMP 테이블에 EMPNO 입력하려고 할 때 -> '안녕' 입력 불가
--// -> 즉, 컬럼이 만들어질 때 데이터타입에서 먼저 걸러짐 : 숫자/문자 + 길이 제약
--// 점수 입력시 3자리만 입력하게 제한 -> NUMBER(3) : -999 ~ + 999 까지 입력이 가능
--// 0 ~ 100 까지로 제한하기 위해서는 CHECK 가 필요하다
------------------------------------------------------------------------------------------
-- ※ NUMBER(38) 까지 입력 가능 // 별도로 기억하지 않아도 크기 입력시
-- CHAR(2000) 까지 입력 가능 // 범위를 넘어가게 되면 오라클이 알려준다
-- VARCHAR2(4000) 까지 입력 가능
-- NCHAR(1000) 까지 입력 가능
-- NVARCHAR2(2000) 까지 입력 가능
------------------------------------------------------------------------------------------
-- ※ COL1 NUMBER → NUMBER(38)
-- ※ COL2 CHAR → CHAR(1)
------------------------------------------------------------------------------------------
-- 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);
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(2,'연수',101); --> 에러 발생
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(3,'지혜',-1); --> 에러 발생
INSERT INTO TBL_TEST8(COL1,COL2,COL3) VALUES(4,'아현',80);
COMMIT;
--==>> 커밋 완료.
--○ 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST8';
--==>>
/*
HR SYS_C007067 TBL_TEST8 C COL3 COL3 BETWEEN 0 AND 100
HR SYS_C007068 TBL_TEST8 P COL1
*/
---------------------- SEARCH_CONDITION 생겨난 것을 확인
--○ CK 지정 실습 (③테이블 생성 이후 제약조건 추가 → 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 TBL_TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TBL_TESTMEMBER_SSN_CK CHECK(TO_NUMBER(SUBSTR(SSN,8,1)) BETWEEN 1 AND 4) );
ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TBL_TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TBL_TESTMEMBER_SSN_CK CHECK(SUBSTR(SSN,8,1) IN('1','2','3','4')) );
-- 제약조건 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
--○ 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TESTMEMBER';
※ ROLLBACK; 의 대상은 DML 구문 (ADD, INSERT, UPDATE) ++ DDL 구문은 실행시에 자동 COMMIT 되기 때문에, DML 구문 이후에 DDL 구문을 생성한 후 ROLL BACK을 해도 ROLLBACK 되지 않는다. (DDL : CREATE, ALTER, DROP, RENAME, TRUNCATE)
--생성된 기존의 테이블에 제약조건 추가
ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TESTMEMBER_SSN_CK CHECK(조건) );
ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TESTMEMBER_SSN_CK CHECK(주민번호 8번째자리 1개가 1 또는 2 또는 3 또는 4) );
ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TESTMEMBER_SSN_CK CHECK(주민번호 8번째자리 1개가 '1' 또는 '2' 또는 '3' 또는 '4') );
ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
, CONSTRAINT TESTMEMBER_SSN_CK CHECK( SUBSTR(SSN,8,1) 가 '1' 또는 '2' 또는 '3' 또는 '4') );
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';
--==>>
/*
HR TBL_TESTMEMBER_SID_PK TBL_TESTMEMBER P SID
HR TBL_TESTMEMBER_SSN_CK TBL_TESTMEMBER C SSN TO_NUMBER(SUBSTR(SSN,8,1)) BETWEEN 1 AND 4
*/
-- 데이터 입력 테스트
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES (1,'수현양','990709-2234567','010-1111-1111');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES (2,'준복리','960716-1234567','010-2222-2222');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES (3,'희정퀀','030326-4234567','010-3333-3333');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES (4,'하경최','080603-3234567','010-4444-4444');
--==>> 1 행 이(가) 삽입되었습니다.
INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES (5,'기배리','980626-8234567','010-5555-5555');
--==>> 에러 발생
/*
ORA-02290: check constraint (HR.TBL_TESTMEMBER_SSN_CK) violated
*/
SELECT *
FROM TBL_TESTMEMBER;
--==>>
/*
1 수현양 990709-2234567 010-1111-1111
2 준복리 960716-1234567 010-2222-2222
3 희정퀀 030326-4234567 010-3333-3333
4 하경최 080603-3234567 010-4444-4444
*/
COMMIT;
--==>> 커밋 완료.
FOREIGN KEY (FK:F:R)
--■■■ FOREIGN KEY(FK:F:R) ■■■--
-- 1. 참조 키 또는 외래 키(FK)는
-- 두 테이블의 데이터 간 연결을 설정하고
-- 강제 적용시키는데 사용되는 열이다.
-- 한 테이블의 기본 키 값이 있는 열을
-- 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다.
-- 이 때, 두 번째 테이블에 추가되는 열이 외래 키가 된다.
-- 2. 부모 테이블(참조받는 컬럼이 포함된 테이블)이 먼저 생성된 후
-- 자식 테이블(참조하는 컬럼이 포함된 테이블)이 생성되어야 한다.
-- 이 때, 자식 테이블에 FOREIGN KEY 제약조건이 설정된다.
-- 3. 형식 및 구조
--① 컬럼 레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명]
-- REFERENCER 참조테이블명(참조컬럼명)
-- [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 CASE』나
-- 『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)
);
--==>> 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 지정 실습 (③테이블 생성 이후 제약조건 추가 → FK 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_EMP3
( SID NUMBER
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER
);
--==>> Table TBL_EMP3이(가) 생성되었습니다.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>> 조회 결과 없음
-- 제약조건 추가
ALTER TABLE TBL_EMP3
ADD ( CONSTRAINT EMP3_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP3_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_ID_FK TBL_EMP3 R JIKWI_ID NO ACTION
*/
-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
4 부장
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 수인주 1
2 별희강 2
3 재혁킴 3
4 서영유 4
5 주연진 1
6 기민킴
7 혜서팍
*/
-- 서영유 부장의 직위를 사원으로 변경
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 기민킴
7 혜서팍
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
-- 부모 테이블(TBL_JOBS)의 부장 데이터를 참조하고 있는
-- 자식 테이블(TBL_EMP1)의 데이터가 존재하지 않는 상황
-- 이와 같은 상황에서 부모 테이블(TBL_JOBS)의
-- 부장 데이터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=4;
--==>> 4 부장
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=4;
--==>> 1 행 이(가) 삭제되었습니다.
-- 확인
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
*/
-- 커밋
COMMIT;
--==>> 커밋 완료.
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 수인주 1
2 별희강 2
3 재혁킴 3
4 서영유 1
5 주연진 1
6 기민킴
7 혜서팍
*/
--------------------------------------------------------------------------------
-- 부모 테이블(TBL_JOBS)의 사원 데이터를 참조하고 있는
-- 자식 테이블(TBL_EMP1)의 데이터가 3건 존재하는 상황
-- 이와 같은 상황에서 부모 테이블(TBL_JOBS)의
-- 사원데이터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 1 사원
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 에러발생
/*
ORA-02292: integrity constraint (HR.SYS_C007078) violated - child record found
*/
--------------------------------------------------------------------------------
DROP TABLE TBL_JOBS;
--==>> 에러 발생
/*
ORA-02449: unique/primary keys in table referenced by foreign keys
*/
- 옵션 //(왠만하면 사용하지 않는 위험한 옵션) ▷ ON DELETE CASCADE ▷ ON DELETE SET NULL
--※ 참조하고 있는 자식 테이블의 레코드가 존재하는 상황임에도 불구하고
-- 부모 테이블의 데이터를 자유롭게 삭제할 수 있도록 설정하기 위해서는
-- 『ON DELETE CASCADE』옵션 지정이 필요하다.
--// 사용하게 되면
--// 부모 테이블을 삭제하겠다고 하면, 자식테이블에 있는 모든 레코드가 삭제 된 후, 부모테이블이 삭제됨
--// CASCADE (폭포) // 폭포같이 연속적으로 반응이 일어나는 것
------------------------------------------------------------------------------------------------
-- TBL_EMP1 테이블(자식 테이블)에서 FK 제약조건을 제거한 후
-- CASCADE 옵션을 포함하여 다시 FK 제약조건을 설정한다.
--○ 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
--==>>
/*
HR SYS_C007077 TBL_EMP1 P SID
HR SYS_C007078 TBL_EMP1 R JIKWI_ID NO ACTION
*/
--○ 제약조건 제거
ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007078;
--==>> Table TBL_EMP1이(가) 변경되었습니다.
--○ 제약조건 제거 이후 다시 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_EMP1';
--==>> HR SYS_C007077 TBL_EMP1 P SID
------------------------------------------------------------------------------------------------
--○ 『ON DELETE CASCADE 』 옵션이 포함된 내용으로 제약조건을 다시 지정
ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWIID_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_C007077 TBL_EMP1 P SID
HR EMP1_JIKWIID_FK TBL_EMP1 R JIKWI_ID CASCADE
*/
------------------------------------------------------------------------------------------------
--※ CASCADE 옵션을 지정한 후에는
-- 참조받고 있는 부모 테이블의 데이터를
-- 언제든지 자유롭게 삭제하는 것이 가능하다.
-- 단, ... ... ... ... 부모 테이블의 데이터가 삭제될 경우
-- 이를 참조하는 자식테이블의 데이터도 모~~~~~~~~~~~두 함께 삭제된다.
-- CHECK
-- 부모 테이블
SELECT *
FROM TBL_JOBS;
--==>>
/*
1 사원
2 대리
3 과장
*/
-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
1 수인주 1
2 별희강 2
3 재혁킴 3
4 서영유 1
5 주연진 1
6 기민킴
7 혜서팍
*/
--○ TBL_JBOS(부모 테이블)의 사원 데이터 삭제
SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 1 사원
DELETE
FROM TBL_JOBS
WHERE JIKWI_ID=1;
--==>> 1 행 이(가) 삭제되었습니다.
-- 부모 테이블 확인
SELECT *
FROM TBL_JOBS;
--==>>
/*
2 대리
3 과장
*/
-- 자식 테이블 확인
SELECT *
FROM TBL_EMP1;
--==>>
/*
2 별희강 2
3 재혁킴 3
6 기민킴
7 혜서팍
*/
--■■■ NOT NULL(NN:CK:C) ■■■--
-- 1. 테이블에서 지정한 컬럼의 데이터가 NULL 을 갖지 못하도록 하는 제약조건.
-- 2. 형식 및 구조
--// 여타 제약조건들은 가급적이면 테이블 레벨의 형식을 사용하는 것을 권장하나
--// NOT NULL 의 형식은 컬럼레벨의 형식을 사용하는 편
-- ① 컬럼 레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] NOT NULL
-- ② 테이블 레벨의 형식
-- 컬럼명 데이터타입,
-- 컬럼명 데이터타입,
-- CONSTRAINT CONSTRAINT명 CHECK(컬럼명 IS NOT NULL)
--// CHECK 제약 조건의 형태를 통해서 지정하기 때문에 CHECK 제약 조건의 일부로 보는 견해가 있다.
--// 실무적으로 체크할 별도의 사항이 있어 따로 빼서 본다.
-- 3. 기존에 생성되어 있는 테이블에 NOT NULL 제약조건만 추가할 경우
-- ADD 보다 MODIFY 절을 더 많이 사용한다.
-- ALTER TABLE 테이블명
-- MODIFY 컬럼명 데이터타입 NOT NULL;
--// 또 하나의 확인할 사항 MODIFY 절~
-- 4. 기존 테이블에 데이터가 이미 들어있지 않은 컬럼(→ NULL인 상태)을
-- NOT NULL 제약조건을 갖게끔 수정하는 경우에는 에러 발생한다.
-- ○ NOT 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');
INSERT INTO TBL_TEST11(COL1,COL2) VALUES(2,'ABCD');
INSERT INTO TBL_TEST11(COL1,COL2) VALUES(3,'NULL');
INSERT INTO TBL_TEST11(COL1,COL2) VALUES(4,NULL); --> 에러 발생
INSERT INTO TBL_TEST11(COL1) VALUES(5); --> 에러 발생
SELECT *
FROM TBL_TEST11;
--==>>
/*
1 TEST
2 ABCD
3 NULL ← 문자열
*/
COMMIT;
--==>> 커밋 완료.
-- 제약조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME='TBL_TEST11';
--==>>
/*
HR SYS_C007084 TBL_TEST11 C COL2 "COL2" IS NOT NULL
HR SYS_C007085 TBL_TEST11 P COL1
*/
--○ NOT NULL 지정 실습 (③ 테이블 생성 이후 제약조건 추가 → NN 제약조건 추가)
-- 테이블 생성
CREATE TABLE TBL_TEST13
( COL1 NUMBER(5)
, COL2 VARCHAR(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 제약 조건만 추가하는 경우
-- 다음과 같은 방법도 가능하다.
-- 테이블 생성
CREATE TABLE TBL_TEST14
( COL1 NUMBER(5)
, COL2 VARCHAR(30)
, CONSTRAINT TEST14_COL1_PK PRIMARY KEY(COL1)
);
--==>> Table TBL_TEST14이(가) 생성되었습니다.
-- NOT NULL 제약조건 추가
ALTER TABLE TBL_TEST14
MODIFY COL2 NOT NULL;
--==>> Table TBL_TEST14이(가) 변경되었습니다.
※ 컬럼 레벨에서 적용한 NN 제약조건 과 테이블 레벨에서 적용한 NN 제약조건 비교 -> 컬럼레벨에서 사용하는 것을 권장하는 이유
-- NOT NULL 제약조건 추가
ALTER TABLE TBL_TEST14
MODIFY COL2 NOT NULL;
--==>> Table TBL_TEST14이(가) 변경되었습니다.
--※ 컬럼 레벨에서 NOT NULL 제약조건을 지정한 테이블
DESC TBL_TEST11;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5) --//PK 로 인해 지정
COL2 NOT NULL VARCHAR2(30) --//NN 조건으로 인해 지정
*/
--> DESC 를 통해 COL2 컬럼이 NOT NULL 인 정보가 확인되는 상황
-- (COL1 은 PK 제약 조건으로 인해 NOT NULL)
--※ 테이블 레벨에서 NOT NULL 제약 조건을 지정한 테이블
DESC TBL_TEST12;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 VARCHAR2(30)
*/
--> DESC 를 통해 COL2 컬럼이 NOT NULL 인 정보가 확인되지 않는 상황
--// 만약 실무에서 DESC 에서 NOT NULL 이 아닌데
--// 데이터가 NULL 이 입력되지 않는 경우라면 테이블 레벨 제약조건임을 인지하자!
※ 테이블 생성 이후, NOT NULL 을 ADD 를 통해 추가 하는 경우와 MODIFY 절을 통해서 추가하는 경우 비교 -> MODIFY 절을 통해 더 많이 생성하는 이유
--※ 테이블 생성 이후 ADD 를 통해 NOT NULL 제약조건을 추가한 테이블
DESC TBL_TEST13;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 VARCHAR2(30)
*/
--※ 테이블 생성 이후 MODIFY 절을 통해 NOT NULL 제약조건을 추가한 테이블
DESC TBL_TEST14;
--==>>
/*
이름 널? 유형
---- -------- ------------
COL1 NOT NULL NUMBER(5)
COL2 NOT NULL VARCHAR2(30)
*/
--■■■ 팀별 실습 과제 ■■■--
--HR 샘플 스키마 ERD 를 이용한 테이블 재구성~!!!
-- 팀별로... HR 스키마에 있는 기본 테이블(7개)
-- COUNTRIES / DEPARTMENTS / EMPLOYEES / JOBS / JOB_HISTORY / LOCATIONS / REGIONS
-- 이 테이블들을... 똑같이 새로 구성한다
-- 단, 생성하는 테이블 이름은 『테이블명+팀번호』
-- 1팀을 예로 들면...
-- COUNTRIES1 / DEPARTMENTS1 / EMPLOYEES1 / JOBS1 / JOB_HISTORY1 / LODCATIONS1 / REGIONS1
-- 과 같이 구성한다.
-- // 제약 구조 , 컬럼 구조 등 ... 다 완전히 싹 똑같이!!!!! 해야함
-- // 부모테이블 -> 자식테이블 관계 생성
-----------------------------------------------------
-- 1. 기존 테이블의 정보 수집
-- 2. 테이블 생성(컬럼 이름, 자료형, ....)
-- 제약조건 설정(PK, UK, FK, CK, ... NN)
-- 3. 작성 후 데이터 입력
-----------------------------------------------------
-- 4. 제츨 항목
-- - 20230404_02_hr_팀별실습과제_1조.sql
-- - 후기_1조.txt (개인별후기)
-- 5. 제출 기한
-- - 명일 오후 12:50 까지 (점심시간 전까지)
-----------------------------------------------------
