-
010. 무결성, 제약 조건(PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK) 추가/ 변경/ 삭제SsY/Class 2023. 4. 4. 18:56728x90
2023.04.04
HR 계정 실습
- 무결성
- 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약어』 -- 형식으로 기술한다.① PK 지정실습 (컬럼 레벨의 형식)
더보기--○ PK 지정 실습(① 컬럼 레벨의 형식) -- 테이블 생성 CREATE TABLE TBL_TEST1 ( COL1 NUMBER(5) PRIMARY KEY , COL2 VARCHAR2(30) ); --==> Table TBL_TEST1이(가) 생성되었습니다. --// PRIMARY KEY 키워드를 붙여서 작성하는 것 제외하고 , 두번째 ',' 가 나오기 전에만 PRIMARY KEY를 입력하면 됨. -- 데이터 입력 // 정상으로 입력된 구문 이외의 것만 표기 INSERT INTO TBL_TEST1(COL1, COL2) VALUES(1,'TEST'); INSERT INTO TBL_TEST1(COL1, COL2) VALUES(2,'ABCD'); INSERT INTO TBL_TEST1(COL1, COL2) VALUES(3,NULL); INSERT INTO TBL_TEST1(COL1) VALUES (4);--// 위의 구문과 동일한 구문 INSERT INTO TBL_TEST1(COL1, COL2) VALUES(2,'ABCD'); --> 에러발생 INSERT INTO TBL_TEST1(COL1, COL2) VALUES(2,'KKKK'); --> 에러발생 INSERT INTO TBL_TEST1(COL1, COL2) VALUES(5,'ABCD'); INSERT INTO TBL_TEST1(COL1, COL2) VALUES(NULL,NULL); --> 에러발생 INSERT INTO TBL_TEST1(COL1, COL2) VALUES(NULL,'STUDY'); --> 에러발생 INSERT INTO TBL_TEST1(COL2) VALUES ('STUDY'); --> 에러발생 //위의 구문과 동일한 구문 COMMIT; --==>> 커밋 완료. SELECT * FROM TBL_TEST1; --==>> /* 1 TEST 2 ABCD 3 4 5 ABCD */- 제약 조건 (컬럼) 확인(검색) 구문
더보기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 1DESC 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 형태로 나와서 정확한 제약조건을 확인할 수 없다.더보기--○ 제약조건이 설정된 소유주, 제약명, 테이블명, 제약종류, 컬럼명 항목 조회 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 = '테이블명'; 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_C007057 TBL_TEST1 P COL1 ----------- → 오라클이 자동으로 붙여준 제약조건 이름 */② PK 지정 실습 (테이블 레벨의 형식)
더보기--○ 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'); INSERT INTO TBL_TEST2(COL1,COL2) VALUES(2,'ABCD'); INSERT INTO TBL_TEST2(COL1,COL2) VALUES(3,NULL); INSERT INTO TBL_TEST2(COL1) VALUES(4); --// 위와 같은 구문 INSERT INTO TBL_TEST2(COL1,COL2) VALUES(2,'ABCD'); --> 에러발생 INSERT INTO TBL_TEST2(COL1,COL2) VALUES(2,'KKKK'); --> 에러발생 INSERT INTO TBL_TEST2(COL1,COL2) VALUES(5,'ABCD'); INSERT INTO TBL_TEST2(COL1,COL2) VALUES(NULL, NULL); --> 에러발생 INSERT INTO TBL_TEST2(COL1,COL2) VALUES(NULL, 'STUDY'); --> 에러발생 INSERT INTO TBL_TEST2(COL2) VALUES('STUDY'); --> 에러발생 --// 위와 같은 구문 COMMIT; --==>> 커밋 완료. SELECT * FROM TBL_TEST2; --==>> /* 1 TEST 2 ABCD 3 4 5 ABCD */- 제약 조건 (컬럼) 확인(검색) 구문
더보기--○ 제약조건이 설정된 소유주, 제약명, 테이블명, 제약종류, 컬럼명 항목 조회 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 */ --//++ 테이블 하나에 제약조건이 여러개일 수 있어서 테이블 명이 아닌 CONSTRAINT_NAME 으로 확인하게 됨③ PK 지정 실습 (복합 프라이머리 키)
더보기--○ 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 지정 실습
- 데이터 입력 관찰더보기--○ 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_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 지정 실습 (테이블 생성 이후 제약조건 추가 → UK 제약조건 추가)
더보기--○ 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 지정 실습 (컬럼 레벨의 형식)
더보기--○ 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_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); INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(2,'연수',101); --> 에러 발생 INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(3,'지혜',-1); --> 에러 발생 INSERT INTO TBL_TEST9(COL1,COL2,COL3) VALUES(4,'아현',80); COMMIT; --==>> 커밋 완료. SELECT * FROM TBL_TEST9; --==>> /* 1 건우 100 4 아현 80 */ -- 제약조건 확인(조회) 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 지정 실습 (테이블 생성 이후 제약조건 추가 → CK 제약조건 추가)
더보기--○ 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_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_C007077 TBL_EMP1 P SID HR SYS_C007078 TBL_EMP1 R JIKWI_ID NO ACTION */ --------- DELETE_RULE 생성 됨 -- 자식 테이블에 데이터 입력 INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(1,'수인주',1); INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(2,'별희강',2); INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(3,'재혁킴',3); INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(4,'서영유',4); INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(5,'주연진',5); --==> 에러 발생 --- /* ORA-02291: integrity constraint (HR.SYS_C007078) violated - parent key not found */ INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(5,'주연진',1); INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES(6,'기민킴',NULL); INSERT INTO TBL_EMP1(SID, NAME) VALUES(7,'혜서팍'); SELECT * FROM TBL_EMP1; --==>> /* 1 수인주 1 2 별희강 2 3 재혁킴 3 4 서영유 4 5 주연진 1 6 기민킴 7 혜서팍 */ COMMIT; --==>> 커밋 완료.② FK 지정 실습 (테이블 레벨의 형식)
더보기--○ FK 지정 실습 (② 테이블 레벨의 형식) -- 테이블 생성 CREATE TABLE TBL_EMP2 ( SID NUMBER , NAME VARCHAR2(30) , JIKWI_ID NUMBER , CONSTRAINT EMP2_SID_PK PRIMARY KEY(SID) , CONSTRAINT EMP2_ID_FK FOREIGN KEY(JIKWI_ID) REFERENCES TBL_JOBS(JIKWI_ID) ); --==>> Table TBL_EMP2이(가) 생성되었습니다. -- 제약조건 확인 SELECT * FROM VIEW_CONSTCHECK WHERE TABLE_NAME = 'TBL_EMP2'; --==>> /* HR EMP2_SID_PK TBL_EMP2 P SID HR EMP2_ID_FK TBL_EMP2 R JIKWI_ID NO ACTION */③ FK 지정 실습 (테이블 생성 이후 제약조건 추가 → FK 제약조건 추가)
더보기--○ 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 혜서팍 */SEARCH_CONDITION : CHECK 제약조건의 상세 조건등을 나타냄
DELETE_RULE : CASCADE 옵션이 적용되었는지 여부 확인
- NOT NULL (NN:CK:C)
--■■■ 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 지정 실습 (컬럼 레벨의 형식)
더보기-- ○ 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지정 실습 (테이블 레벨의 형식)
더보기-- ○ 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지정 실습 (테이블 생성 이후 제약조건 추가 → NN 제약조건 추가)
- ADD 절 사용
- MODIFY 절 사용더보기--○ 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 계정 실습
- 팀작업 수행을 위한 원격 접속
※ 원격 접속하기
호스트이름에 접속할 해당 pc의 IP
포트에 아까 열어둔 port 번호 입력--■■■ 팀별 실습 과제 ■■■-- --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 까지 (점심시간 전까지) -----------------------------------------------------728x90'SsY > Class' 카테고리의 다른 글
012. UPDATE 예제, DELETE, 뷰(VIEW), [PL/SQL ] IF 문, ACCEPT(&외부변수), 기본 반복문(LOOP), WHILE LOOP , FOR LOOP (0) 2023.04.06 011. DEFAULT 표현식, UPDATE (0) 2023.04.05 009. 정규화(Normalization), Primary Key, 관계 (0) 2023.04.03 008. UNION / UNION ALL, INTERSECT / MINUS, NATURAL JOIN, USING (0) 2023.03.31 007. SEQUENCE 와 ROW_NUMBER, JOIN(SQL 1992/1999 CODE), UNION / UNION ALL (0) 2023.03.30 - 무결성