7주차 테이블관리 , 테이블생성(제약조건포함),테이블의변경

테이블 생성 (제약조건 포함) (24쪽)

제약조건의 종류

NOT NULL
UNIQUE
PRIMARY KEY (NOT NULL + UNIQUE 이며 테이블 당 한번만 정의)
FOREIGN KEY
CHECK

지금까지 사용했던 EMP, DEPT테이블의 문제 점을 보자
INSERT INTO DEPT VALUES (10, 'MIS', 'LA');
-----> 무결성 제약 조건(LHK7083.SYS_C0012361)에 위배됩니다

INSERT INTO EMP VALUES (8000, 'LEE', 'CLERK', 7902, '22-12-17', 800, NULL, 60);

7.txt

0.04MB

----> 무결성 제약조건(LHK7083.SYS_C0012364)이 위배되었습니다- 부모 키가 없습니다

문제점
*** 제약조건 이름으로 오류 내용을 파악하기 힘들다

CREATE TABLE DEPT
(DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
CREATE TABLE EMP
(EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) NOT NULL REFERENCES DEPT(DEPTNO));


DROP TABLE EMP;
DROP TABLE DEPT;

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT DEPT_PK PRIMARY KEY (DEPTNO));


INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');


INSERT INTO DEPT VALUES (10, 'MIS', 'LA');
---> 무결성 제약 조건(LHK7083.DEPT_PK)에 위배됩니다

CREATE TABLE EMP
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2) NOT NULL,
CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
CONSTRAINT EMP_DEPT_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '80-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '81-02-20', 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '81-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '81-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '81-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '81-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '81-09-09', 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '82-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '81-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '81-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '83-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '81-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '81-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '82-01-23', 1300, NULL, 10);


INSERT INTO EMP VALUES (8000, 'LEE', 'CLERK', 7902, '22-12-17', 800, NULL, 60);

---

*
1행에 오류:
ORA-02291: 무결성 제약조건(TAEHO.EMP_DEPT_FK)이 위배되었습니다- 부모 키가 없습니다

---

==============
테이블의 변경
==============
컬럼 추가
ALTER TABLE dept ADD (EMPLOYEES NUMBER(3));

컬럼 삭제
ALTER TABLE dept DROP COLUMN EMPLOYEES;

Unique 제약조건을 추가해보자
먼저

INSERT INTO DEPT VALUES (50, 'MIS', 'BOSTON');
----> 오류 없음

rollback;
ALTER TABLE dept ADD CONSTRAINT DEPT_UK UNIQUE(LOC);
INSERT INTO DEPT VALUES (50, 'MIS', 'BOSTON');


제약조건 삭제
ALTER TABLE dept DROP CONSTRAINT DEPT_UK;

INSERT INTO DEPT VALUES (50, 'MIS', 'BOSTON');


=============
제약조건 보기
============
SELECT * FROM USER_CONSTRAINTS;

---

TAEHO>SELECT * FROM USER_CONSTRAINTS;

OWNER CONSTRAINT_NAME C
------------------------------------------------------------ ------------------------------ -
TABLE_NAME SEARCH_CONDITION
------------------------------ --------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHA INDEX_OWNER
-------------- --------- ------------- -------------- --- ---- -------- ------------------------------
INDEX_NAME INVALID VIEW_RELATED
------------------------------ ------- --------------
TAEHO BIN$bGnWdnt1TTKffFsOhGiGYg==$0 C
BIN$x/E2+UnJRgCTClU9Uk7uGg==$0 "DEPTNO" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 22/09/06


TAEHO SYS_C0012131 C
P "P#" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06


TAEHO SYS_C0012133 C
SP QTY>10
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06


TAEHO SYS_C0012553 C
EMP "DEPTNO" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/10/11


TAEHO EMP_DEPT_FK R
EMP
TAEHO DEPT_PK NO ACTION ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 22/10/11


TAEHO SYS_C0012136 R
SP
TAEHO SYS_C0012132 NO ACTION ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06


TAEHO SYS_C0012135 R
SP
TAEHO SYS_C0012130 NO ACTION ENABLED

OWNER CONSTRAINT_NAME C
------------------------------------------------------------ ------------------------------ -
TABLE_NAME SEARCH_CONDITION
------------------------------ --------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHA INDEX_OWNER
-------------- --------- ------------- -------------- --- ---- -------- ------------------------------
INDEX_NAME INVALID VIEW_RELATED
------------------------------ ------- --------------
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06


TAEHO BIN$HWVm3HlySravnzAmdyQTyg==$0 P
BIN$8AvROObuRLSRW4AMqsF6/Q==$0
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06 TAEHO
BIN$jzLQyx+0Q7GaKYWwV7wY8w==$0

TAEHO BIN$bJSfZiTsTCeasR+9IT4m8Q==$0 P
BIN$x/E2+UnJRgCTClU9Uk7uGg==$0
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 22/09/06 TAEHO
BIN$liwB3priRkifKPG+gJm/dA==$0

TAEHO DEPT_PK P
DEPT
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 22/10/11 TAEHO
DEPT_PK

TAEHO EMP_PK P
EMP
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 22/10/11 TAEHO
EMP_PK

TAEHO SYS_C0012130 P
S
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06 TAEHO
SYS_C0012130

TAEHO SYS_C0012132 P
P
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06 TAEHO
SYS_C0012132


OWNER CONSTRAINT_NAME C
------------------------------------------------------------ ------------------------------ -
TABLE_NAME SEARCH_CONDITION
------------------------------ --------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHA INDEX_OWNER
-------------- --------- ------------- -------------- --- ---- -------- ------------------------------
INDEX_NAME INVALID VIEW_RELATED
------------------------------ ------- --------------
TAEHO SYS_C0012134 P
SP
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/09/06 TAEHO
SYS_C0012134

TAEHO SYS_C0012317 P
MANAGER
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/10/04 TAEHO
SYS_C0012317

TAEHO SYS_C0012318 P
EMP_SUMMARY
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 22/10/04 TAEHO
SYS_C0012318


16 개의 행이 선택되었습니다.

---

SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME ='EMP';

---

TAEHO>SELECT CONSTRAINT_NAME, COLUMN_NAME
2 FROM USER_CONS_COLUMNS
3 WHERE TABLE_NAME ='EMP';

CONSTRAINT_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
SYS_C0012553
DEPTNO

EMP_PK
EMPNO

EMP_DEPT_FK
DEPTNO


TAEHO>

---

SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME ='DEPT';

---

TAEHO>SELECT CONSTRAINT_NAME, COLUMN_NAME
2 FROM USER_CONS_COLUMNS
3 WHERE TABLE_NAME ='DEPT';

CONSTRAINT_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
DEPT_PK
DEPTNO

---

============================================================
테이블의 삭제 : 데이터, 구조, 인덱스, 제약조건 모두 삭제됨
롤백 불가

DROP TABLE ...

데이터만 지울 때

DELETE FROM ... ROLLBACK 가능

TRINCATE TABLE ... ROLLBACK 불가능


개체(테이블, 뷰 등) 이름 변경

RENAME DEPT TO DEPARTMENT;

---

TAEHO>RENAME DEPT TO DEPARTMENT;

테이블 이름이 변경되었습니다.

---

테이블에 주석문 달기

COMMENT ON TABLE EMP IS '사원정보';

---

TAEHO>COMMENT ON TABLE EMP IS '사원정보';

주석이 생성되었습니다.

---

주석문 보기
select * from user_tab_comments;

---

TAEHO>select * from user_tab_comments;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
COMMENTS
------------------------------------------------------------------------------------------------------------------------
주문 TABLE


SP TABLE


SALGRADE TABLE


S TABLE


P TABLE


MANAGER TABLE


EMP_SUMMARY TABLE


EMP TABLE
사원정보

DUMMY TABLE


DEPARTMENT TABLE


BONUS TABLE



11 개의 행이 선택되었습니다.

TAEHO>

---

===========================================================

제약조건의 일시적 비활성화

ALTER TABLE DEPT
DISABLE CONSTRAINT DEPT_PK CASCADE;
---->CASCADE는 종속된 모든 제약조건을 비활성화 함

제약조건의 활성화
ALTER TABLE DEPT
ENABLE CONSTRAINT DEPT_PK;

---

TAEHO>
TAEHO>ALTER TABLE DEPT
2 DISABLE CONSTRAINT DEPT_PK CASCADE;

테이블이 변경되었습니다.

TAEHO>
TAEHO>ALTER TABLE DEPT
2 ENABLE CONSTRAINT DEPT_PK;

테이블이 변경되었습니다.

TAEHO>

---

연습
======================================================
1 .DEPT에 사원수 컬럼 추가해보자.

ALTER TABLE dept ADD (EMPLOYEES NUMBER(3));

**** 이제 EMPLOYEES 값을 해당 부서에 근무하는 사원수로 갱신해보자(상관관계 질의식 이용)

---

TAEHO>UPDATE DEPT d
2 SET EMPLOYEES = ( select count(deptno) from emp e where d.deptno = e.deptno group by deptno);
>> 있는거 안에 넣는거면 update 사용하기

5 행이 갱신되었습니다.

TAEHO>select * from dept;

DEPTNO DNAME LOC EMPLOYEES
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 3
20 RESEARCH DALLAS 5
30 SALES CHICAGO 6
40 OPERATIONS BOSTON
50 MIS BOSTON

---

2. 다음주까지의 과제
**** 다음 시스템을 분석하여 개념설계, 논리 설계를 완성한 뒤 테이블을 생성하라
**** 성성 후 각 테이블에 최소 5개 이상의 튜플을 삽입하시오
**** 처리 업머에 대한 질의처리 실습은 다음주에 실시 함

학사운영규칙
=========
SCH대학의 학사관리를 위한 DB시스템을 구축하고자 한다.
학사 운영의 기본단위는 학과이며 학과는 학과이름으로 식별하며 학과 사무실을 가지고 있다.
한 학과당 여러명의 교수가 근무하고 있으며, 한 교수는 여러면의 학생을 지도한다.
학생은 한학과에만 속할 수 있으며, 학번으로 식별되고 이름이 입력되어야 한다.
교수는 교수번호로 식별하며 교수이름을 가기고 있어야 한다
학과에서는 과목을 개설하여 운영하며, 각 과목에 대한 담당 교수가 1명 지정된다.
각 과목은 한 학과에서만 개설할 수 있다. 이를테면 '데이터베이스'는 여러 학과에서 개설할 수 없다.
과목은 과목이름으로 식별되며, ('교양','학초', '전공') 중의 하나인 과목구분이 있다.
교수는 여러개의 교과목을 담당할 수 있으며 한 과목은 한명의 교수만 담당할 수 있다.
학생은 여러 과목을 수강할 수 있으며 한 과목으 여러명의 수강생이 있을 수 있다.

처리 업무
=======
특정학과에 속하는 교수명단을 출력할 수 있어야 한다.
특정교수의 지도학생 명단을 출력할 수 있어야 한다.
특정학과의 교과목을 개설하고 담당교수를 입력할 수 있어야 한다
특정학과에서 개설한 과목을을 출력할 수 있어야 한다.
학생이 수강할 수 있는 과목명과 담당교수이름을 검색할 수 있고 수강신청할 수 있어야 한다.