20230329 SQL
<차례>
■ SAVEPOINT
■ 다중테이블 INSERT
1. 무조건 INSERT ALL
INSERT ALL
INTO VALUES
INTO VALUES
SELETE
FROM
- 조건 INSERT ALL
INSERT ALL
WHEN THEN
INTO VALUES
WHEN THEN
INTO VALUES
SELETE
FROM
- 조건 FIRST INSERT
INSERT FIRST
WHEN THEN
INTO VALUES
WHEN THEN
INTO VALUES
ELSE
INTO VALUES
SELETE
FROM
■ 기존 테이블에 컬럼을 추가하는 방법
■ MERGE
(예시)
MERGE INTO
USING
ON
WHEN MATCHED THEN
UPDATE SET
DELETE WHERE
WHEN NOT MATCHED THEN
INSERT
VALUES
■ 컬럼 추가
ALTER TABLE ADD
■ 컬럼 타입, 크기 수정
ALTER TABLE MODIFY
■ 컬럼 삭제
ALTER TABLE DROP COLUMN
■ 제약조건
❍ PRIMARY KEY
● 제약조건에 대한 정보 확인 방법
SELECTFROM user_constraints WHERE
SELECTFROM user_cons_columns WHERE
● INDEX에 대한 정보 확인 방법
SELECTFROM user_indexes WHERE
SELECTFROM user_ind_columns WHERE
● 제약조건을 추가하는 방법
- ALTER TABLE ADD CONSTRAINT
- ALTER TABLE ADD PRIMARY KEY
❍ FOREIGN KEY
ALTER TABLE ADD CONSTRAINT
FOREIGN KEY REFERENCES
● 제약조건을 삭제하는 방법
-
ALTER TABLE DROP CONSTRAINT
-
ALTER TABLE DROP PRIMARY KEY
[hr]SELECT * FROM tab; => 내가 가진 테이블 목록 확인
CREATE TABLE hr.emp_20
AS
SELECT employee_id, last_name
FROM hr.employees
WHERE department_id = 20;
hr schema => (업무에 따라) 테이블들을 전체 관리(분석가들은 객체 권한 받아서 수행함)
SELECT*FROM hr.emp_20;
SELECT*FROM user_tab_privs;
GRANT select on hr.emp_20 TO insa;
[insa]SELECTFROM session_privs;
SELECTFROM user_tab_privs;
SELECT*FROM hr.emp_20;
INSERT INTO hr.emp_20(employee_id, last_name)
VALUES(203, 'JAMES');
UPDATE hr.emp_20
SET last_name = 'JAMES'
WHERE employee_id = 202;
DELETE FROM hr.emp_20 WHERE employee_id = 202;
=> 셋 다 오류 : INSUFFICIENT PRIVILEGES ( 객체권한 받아야 함, insert, update, delete)
[hr]GRANT select, insert, update, delete on hr.emp_20 TO insa;
SELECT*FROM user_tab_privs;
▽▽▽(결과창)HR SYS DBMS_STATS SYS EXECUTE NO NO
INSA HR EMPLOYEES HR SELECT NO NO
INSA HR EMP_20 HR UPDATE NO NO
INSA HR EMP_20 HR INSERT NO NO
INSA HR EMP_20 HR DELETE NO NO
INSA HR EMP_20 HR SELECT NO NO
[insa]INSERT INTO hr.emp_20(employee_id, last_name)
VALUES(203, 'JAMES'); -- transaction 시작
UPDATE hr.emp_20
SET last_name = 'JAMES'
WHERE employee_id = 202;
DELETE FROM hr.emp_20 WHERE employee_id = 202;
SELECT*FROM hr.emp_20;
[run command line]
SQL> conn insa/oracle
Connected.
SQL> UPDATE hr.emp
2 SET employee_id = 300
3 WHERE employee_id = 202;
UPDATE hr.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> ed => 메모장 뜸
Wrote file afiedt.buf
1 UPDATE hr.emp_20
2 SET employee_id = 300
3* WHERE employee_id = 202
SQL> /
=> 여기서 커서 깜박이고 아무 반응 없음(waiting), 왜?
● lock 반응
다른 session(run command line, sql developer이 같은 대상에 대해 DML 할 수 없음)
언제까지? transaction 끝날 때까지 (commit or rollback
-
sql developer에서 rollback 하면 run command line의 쿼리문이 살아남
[insa]rollback; -- transaction 시작 지점까지 취소, transaction 종료, LOCK 해지
[run command line]1 row updated.
SQL>
■ SAVEPOINT
- DML 작업시에 ROLLBACK을 도와주는 “표시자”
SAVEPOINT 표시자(고유한 이름);
ROLLBACK TO 표시자; -- 표시자 밑에 있는 DML 전부 취소
[insa]INSERT INTO hr.emp_20(employee_id, last_name)
VALUES(500,'윤건');
SELECT*FROM hr.emp_20;
SAVEPOINT A;
UPDATE hr.emp_20
SET last_name = '나얼'
WHERE employee_id = 201;
SELECT*FROM hr.emp_20;
SAVEPOINT B;
DELETE FROM hr.emp_20 WHERE employee_id = 300;
SELECT * FROM hr.emp_20;
ROLLBACK TO B; => (insert, update 살아있고 delete만 롤백 됨)
savepoint B 밑에 있는 DML은 전부 취소
ROLLBACK TO A; => savepoint A 밑에 있는 DML은 전부 취소
SELECT*FROM hr.emp_20;
ROLLBACK; => 전부 취소
SELECT*FROM hr.emp_20;
■ 다중테이블 INSERT
- SOURCE TABLE에서 데이터를 추출해서 여러 개의 TARGET TABLE에 데이터를 로드(INSERT)하는 SQL문
- ETL(Extraction(추출) - select, Transformation(변형), Loading(적재) - insert, CTAS)
- insert subquery는 ETL의 한 예
- 무조건 INSERT ALL
(필요성 발견 위한 문제적 테이블 만들기)
(첫 번째 테이블 만들기)
CREATE TABLE hr.sal_history
AS
SELECT employee_id, hire_date, salary
FROM hr.employees
WHERE 1=2; -- 토대 만들기
SELECT*FROM hr.sal_history;
(두 번째 테이블 만들기)CREATE TABLE hr.mgr_history
AS
SELECT employee_id, manager_id, salary
FROM hr.employees
WHERE 1=2;
SELECT*FROM hr.mgr_history;
(첫, 두 번째 테이블에 데이터 insert)INSERT INTO hr.sal_history(employee_id, hire_date, salary)
SELECT employee_id, hire_date, salary
FROM hr.employees;
SELECT*FROM hr.sal_history;
INSERT INTO hr.mgr_history(employee_id, manager_id, salary)
SELECT employee_id, manager_id, salary
FROM hr.employees; -- 대용량 테이블 두 번 access(엄청난 부하)
SELECT*FROM hr.mgr_history;
rollback;
INSERT ALL
INTO VALUES
INTO VALUES
SELETE
FROM
INSERT ALL
INTO hr.sal_history(employee_id, hire_date, salary) VALUES(id, day, sal)
INTO hr.mgr_history(employee_id, manager_id, salary) VALUES(id, mgr, sal)
SELECT employee_id id, hire_date day, manager_id mgr, salary*1.1 sal
FROM hr.employees;
SELECTFROM hr.sal_history;
SELECTFROM hr.mgr_history;
rollback; -- transaction 일어나므로 꼭 선택해주기
- 조건 INSERT ALL
(타겟 테이블 두 개 만들기)
CREATE TABLE hr.emp_history
AS
SELECT employee_id, hire_date, salary
FROM hr.employees
WHERE 1=2;
SELECT*FROM hr.emp_history;
CREATE TABLE hr.emp_sal
AS
SELECT employee_id, commission_pct, salary
FROM hr.employees
WHERE 1=2;
SELECT*FROM hr.emp_sal;
INSERT ALL
WHEN THEN
INTO VALUES
WHEN THEN
INTO VALUES
SELETE
FROM
INSERT ALL
WHEN day < to_date('2005-01-01', 'yyyy-mm-dd') AND sal >= 5000 THEN
INTO hr.emp_history(employee_id,hire_date,salary) VALUES(id,day,sal)
WHEN comm IS NOT NULL THEN
INTO hr.emp_sal(employee_id,commission_pct,salary) VALUES(id,comm,sal)
SELECT employee_id id, hire_date day, salary sal, commission_pct comm
FROM employees;
SELECTFROM hr.emp_history;
SELECTFROM hr.emp_sal;
(두 테이블의 공통 사원번호 가진 사원 출력)
SELECT employee_id FROM hr.emp_history
INTERSECT
SELECT employee_id FROM hr.emp_sal; => 이거 아님, 정렬(sort) 발생, 부하가 큼
SELECT *
FROM hr.emp_history e
WHERE EXISTS (SELECT 'x'
FROM hr.emp_sal
WHERE employee_id = e.employee_id);
- 조건 FIRST INSERT
(테이블 세 개 만들기)
CREATE TABLE hr.sal_low
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;
CREATE TABLE hr.sal_mid
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;
CREATE TABLE hr.sal_high
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;
SELECTFROM hr.sal_low;
SELECTFROM hr.sal_mid;
SELECT*FROM hr.sal_high;
INSERT FIRST
WHEN THEN
INTO VALUES
WHEN THEN
INTO VALUES
ELSE
INTO VALUES
SELETE
FROM
INSERT FIRST
WHEN sal < 5000 THEN
INTO hr.sal_low(employee_id, last_name, salary) VALUES(id,name,sal)
WHEN sal BETWEEN 5000 AND 10000 THEN
INTO hr.sal_mid(employee_id, last_name, salary) VALUES(id,name,sal)
ELSE
INTO hr.sal_high(employee_id, last_name, salary) VALUES(id,name,sal)
SELECT employee_id id, last_name name, salary sal
FROM hr.employees;
[문제97] 사원들 중에 근무연수가 15년 이상 이면서 급여는 10000이상 급여를 받는 사원들은 emp_1 테이블에 사번, 이름(last_name), 입사일, 근무연수, 급여 정보를 입력하고 근무연수가 15년 이상 이면서 급여는 10000미만 급여를 받는 사원들은 emp_2 테이블에 사번, 이름(last_name), 입사일, 근무연수, 급여 정보를 입력하세요.
CREATE TABLE emp_1(id number, name varchar2(30), day date, years number, sal number);
CREATE TABLE emp_2(id number, name varchar2(30), day date, years number, sal number);
INSERT ALL
WHEN (to_char(sysdate,'yyyy')-to_char(hire_date,'yyyy')) >= 15 AND salary >= 10000 THEN
INTO emp_1(employee_id, last_name, hire_date, (to_char(sysdate,'yyyy')-to_char(hire_date,'yyyy')), salary)
VALUES(id, name, day, years, sal)
WHEN (to_char(sysdate,'yyyy') - to_char(hire_date, 'yyyy')) >= 15 AND salary < 10000 THEN
INTO emp_2(employee_id, last_name, hire_date, (to_char(sysdate,'yyyy')-to_char(hire_date,'yyyy')), salary)
VALUES(id, name, day, years, sal)
SELECT employee_id id, last_name name, hire_date day, (to_char(sysdate,'yyyy')-to_char(hire_date,'yyyy')) years, salary sal
FROM employees; => 이거 아님
(풀이 과정)
1) 근무연수 파악
SELECT employee_id, last_name, hire_date, months_between(sysdate,hire_date)/12, salary
FROM hr.employees
WHERE months_between(sysdate,hire_date)/12 >= 15;
2) 소수점 절삭, 열 별칭 부여
SELECT employee_id id, last_name name, hire_date day, trunc(months_between(sysdate,hire_date)/12) years, salary sal
FROM hr.employees
WHERE months_between(sysdate,hire_date)/12 >= 15;
3) 조건 insert first 이용 SQL문 만들기
INSERT FIRST
WHEN sal > 10000 THEN
INTO emp_1(id,name,day,years,sal) VALUES(id,name,day,years,sal)
ELSE
INTO emp_2 (id,name,day,years,sal) VALUES(id,name,day,years,sal)
SELECT employee_id id, last_name name, hire_date day, trunc(months_between(sysdate,hire_date)/12) years, salary sal
FROM hr.employees
WHERE months_between(sysdate,hire_date)/12 >= 15;
=> 사원들은 양쪽 테이블 중 하나에만 소속될 것
■ 기존 테이블에 컬럼을 추가하는 방법
ALTER TABLE ADD
(샘플 테이블 두 개 생성)
CREATE TABLE hr.oltp_emp
AS
SELECT employee_id, last_name, department_id
FROM hr.employees;
SELECT*FROM hr.oltp_emp;
CREATE TABLE hr.dw_emp
AS
SELECT employee_id, last_name, department_id
FROM hr.employees
WHERE department_id = 20;
SELECT*FROM hr.dw_emp;
ALTER TABLE hr.oltp_emp ADD flag char(1);
desc hr.oltp_emp
SELECT*FROM hr.oltp_emp;
=> 추가한 테이블의 데이터는 모두 NULL 값으로 출력됨
(salary 컬럼 만드는거 깜빡해서 다시;)
DROP TABLE hr.oltp_emp PURGE;
DROP TABLE hr.dw_emp PURGE;
CREATE TABLE hr.oltp_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees;
SELECT*FROM hr.oltp_emp;
CREATE TABLE hr.dw_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees
WHERE department_id = 20;
SELECT*FROM hr.dw_emp;
desc hr.oltp_emp;
ALTER TABLE hr.oltp_emp ADD flag char(1);
desc hr.oltp_emp
SELECTFROM hr.oltp_emp WHERE employee_id IN (201, 202);
SELECTFROM hr.dw_emp;
UPDATE hr.oltp_emp
SET flag = 'd'
WHERE employee_id = 202;
SELECT*FROM hr.oltp_emp;
UPDATE hr.oltp_emp
SET salary = 20000
WHERE employee_id = 201;
commit;
SELECT*FROM hr.oltp_emp WHERE employee_id IN (201, 202);
▽▽▽(결과창)
201 Hartstein 20000 20 (NULL)
202 Fay 6000 20 d
[문제98] hr.oltp_emp에 있는 사원들 중에 hr.dw_emp 테이블에 존재하는 사원의 정보를 출력해주세요.
SELECT *
FROM hr.oltp_emp o
WHERE EXISTS (SELECT 'x'
FROM hr.dw_emp
WHERE employee_id = o.employee_id);
;문제99] dw_emp에 있는 사원들 중에 oltp_emp에 존재하는 사원들은 oltp_emp의 급여를 기준으로 10% 인상해주세요. (상호관련 update 사용)
SELECT employee_id, last_name, salary, salary*1.1
FROM hr.dw_emp O
WHERE EXISTS (SELECT 'x'
FROM hr.oltp_emp
WHERE employee_id = o.employee_id); => 이거 아님, 그냥 확인용
UPDATE hr.oltp_emp e
SET salary = (SELECT salary*1.1
FROM hr.dw_emp
WHERE employee_id = e.employee_id);
=> 이거 아님, 201, 202 제외 sal null 됨
SELECT*FROM hr.oltp_emp;
SELECT *
FROM hr.dw_emp O
WHERE EXISTS (SELECT 'x'
FROM hr.oltp_emp
WHERE employee_id = o.employee_id) => 2명 있는 것 확인;
SELECT*FROM hr.oltp_emp WHERE employee_id IN (201, 202);
UPDATE hr.dw_emp d
SET salary = (SELECT salary*1.1
FROM hr.oltp_emp
WHERE employee_id = d.employee_id);
SELECT*FROM hr.dw_emp;
[문제100] hr.dw_emp에 있는 사원 중에 hr.oltp_emp에 존재하는 사원의 flag컬럼 값이 ‘d’인 사원을 삭제해주세요. (상호관련 delete 사용)
SELECT *
FROM hr.dw_emp d
WHERE EXISTS (SELECT 'x'
FROM hr.oltp_emp
WHERE employee_id = d.employee_id
AND flag = 'd');
DELETE FROM hr.dw_emp e
WHERE EXISTS (SELECT 'x'
FROM hr.oltp_emp
WHERE employee_id = e.employee_id
AND flag = 'd');
SELECT*FROM hr.dw_emp;
[문제101] hr.oltp_emp 테이블에 있는 데이터 중 hr.dw_emp테이블에 없는 데이터들을 hr.dw_emp테이블에 로드해주세요. (sync 맞추는 작업)
SELECTFROM hr.oltp_emp;
SELECTFROM hr.dw_emp;
SELECT *
FROM hr.oltp_emp o
WHERE NOT EXISTS (SELECT 'x'
FROM hr.dw_emp
WHERE employee_id = o.employee_id)
ORDER BY 1;
INSERT INTO hr.dw_emp(employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM hr.dw_emp o
WHERE NOT EXISTS (SELECT 'x'
FROM hr.oltp_emp
WHERE employee_id = o.employee_id);
■ MERGE => 파이썬에서는 MERGE가 조인임
- INSERT, UPDATE, DELETE문을 한꺼번에 수행하는 SQL문
MERGE INTO
USING
ON
WHEN MATCHED THEN
UPDATE SET
DELETE WHERE
WHEN NOT MATCHED THEN
INSERT
VALUES
SELECTFROM hr.dw_emp; -- target table(실제 insert, update, delete)
SELECTFROM hr.oltp_emp; -- source table
MERGE INTO hr.dw_emp d
USING hr.oltp_emp o
ON (d.employee_id = o.employee_id) -- 조인조건
WHEN MATCHED THEN
UPDATE SET
d.salary = o.salary*1.1
DELETE WHERE o.flag = 'd'
WHEN NOT MATCHED THEN
INSERT(d.employee_id, d.last_name, d.salary, d.department_id)
VALUES(o.employee_id, o.last_name, o.salary, o.department_id);
107개 행 이(가) 병합되었습니다.
SELECT*FROM hr.dw_emp
ORDER BY 1; => 106개 행 출력됨
● target table update, delete, insert 권한 있어야 함
● source table에 대한 select 권한 있어야 함
((merge 연습용 테이블 생성))
CREATE TABLE hr.copy_emp
AS
SELECT*FROM hr.employees;
ALTER TABLE hr.copy_emp ADD department_name varchar2(30);
desc hr.copy_emp;
SELECT*FROM hr.copy_emp;
[문제102] hr.copy_emp 테이블에 있는 department_name 값은 departments 테이블에 있는 department_name값을 이용해서 수정해주세요.
1) UPDATE 문을 이용해 수행
UPDATE hr.copy_emp c
SET department_name = (SELECT department_name
FROM hr.departments
WHERE c.department_id = department_id);
2) MERGE 문을 이용해 수행
MERGE INTO hr.copy_emp c
USING hr.departments d
ON (c.department_id = d.department_id)
WHEN MATCHED THEN
UPDATE SET c.department_name = d.department_name;
SELECT*FROM hr.copy_emp;
rollback;
======== ========
insert into
update set
delete from
merge into
======== ======== => 암기중
■ 컬럼 추가
CREATE TABLE hr.emp(id number, name varchar2(30), day date);
desc hr.emp;
DROP TABLE hr.emp; => 이미 hr.emp테이블이 있숑
CREATE TABLE hr.emp(id number, name varchar2(30), day date);
ALTER TABLE hr.emp ADD job_id varchar2(30);
desc hr.emp;
SELECTFROM user_tables WHERE table_name = 'EMP') -> 이거 왜 안됑(괄호)
SELECTFROM user_tab_columns WHERE table_name = 'EMP'); -> 이거 왜 안됑(괄호)
■ 컬럼 타입, 크기 수정
ALTER TABLE MODIFY
desc hr.emp;
ALTER TABLE hr.emp MODIFY job_id varchar2(20); => 크기 수정
SELECT*FROM user_tab_columns WHERE table_name = 'EMP';
ALTER TABLE hr.emp MODIFY job_id number; => 타입 수정
desc hr.emp;
SELECT*FROM user_tab_columns WHERE table_name = 'EMP';
=> 입력되어있는 데이터가 있으면 문자 타입을 숫자 타입으로 바꿀 수 없음(오류 뜸)
=> 지금은 아무 데이터 없어서(or NULL) 수정된 것임, 크기도 마찬가지
■ 컬럼 삭제
ALTER TABLE DROP COLUMN
desc hr.emp;
ALTER TABLE hr.emp DROP COLUMN job_id;
=> 백업본이 있지 않은 한 복원 안되니 삭제에 주의할 것!
■ 제약조건
- 테이블의 데이터에 대한 규칙을 만듦
- 데이터에 대한 품질을 향상시키기 위해서 만듦
desc hr.emp;
INSERT INTO hr.emp(id,name,day) VALUES(1, '홍길동', sysdate);
INSERT INTO hr.emp(id,name,day) VALUES(1, '박찬호', sysdate);
INSERT INTO hr.emp(id,name,day) VALUES(NULL, '홍길동', sysdate);
SELECT*FROM hr.emp;
▽▽▽(결과창)ID NAME DAY
1 홍길동 23/03/29
1 박찬호 23/03/29
(NULL) 홍길동 23/03/29
❍ PRIMARY KEY
- 테이블의 대표키
- UNIQUE(유일한 값), NULL값은 허용할 수 없음
- 테이블당 하나만 생성함
- 자동으로 UNIQUE INDEX 생성
● 제약조건에 대한 정보 확인 방법
SELECTFROM user_constraints WHERE table_name = 'EMPLOYEES';
SELECTFROM user_cons_columns WHERE table_name = 'EMPLOYEES';
● INDEX에 대한 정보 확인 방법
SELECTFROM user_indexes WHERE table_name = 'EMPLOYEES';
SELECTFROM user_ind_columns WHERE table_name = 'EMPLOYEES';
● 제약조건을 추가하는 방법
- CONSTRAINT_NAME은 고유한 이름으로 만들어야 함(중복 불허용)
ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(id);
또는
ALTER TABLE hr.emp ADD PRIMARY KEY(id);
--둘 중 아래 쿼리문의 경우 제약조건 이름은 오라클에서 자동으로 생성, sys_c숫자
(이름 쓰는 게 좋음)
desc hr.emp;
INSERT INTO hr.emp(id,name,day) VALUES(1, '홍길동', sysdate);
SELECT*FROM hr.emp;
INSERT INTO hr.emp(id,name,day) VALUES(1, '박찬호', sysdate); -- 오류 발생
primary key 제약조건은 unique 한 값을 체크하기 때문에
INSERT INTO hr.emp(id,name,day) VALUES(NULL, '홍길동', sysdate); -- 오류 발생
primary key 제약조건은 not null 값을 체크하기 때문에
((연습))
CREATE TABLE hr.dept(dept_id number, dept_name varchar2(30));
ALTER TABLE hr.dept ADD PRIMARY KEY(dept_id);
SELECTFROM user_constraints WHERE table_name = 'DEPT';
HR SYS_C007048 P DEPT ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 23/03/29 HR SYS_C007048
SELECTFROM user_cons_columns WHERE table_name = 'DEPT';
HR SYS_C007048 DEPT DEPT_ID 1
SELECTFROM user_indexes WHERE table_name = 'DEPT';
SELECTFROM user_ind_columns WHERE table_name = 'DEPT';
❍ FOREIGN KEY
- 외래 키, 참조무결성 제약조건
- 동일한 테이블이나 다른 테이블의 PRIMARY KEY, UNIQUE KEY 제약조건을 참조함
- 데이터 품질 위해
- 중복값 허용, NULL값 허용
- 종속되는 행(child record)을 삭제할 수 없음, 참조를 당하는 테이블의 삭제가 불허임
INSERT INTO hr.dept(dept_id, dept_name) VALUES(10, '총무부');
INSERT INTO hr.dept(dept_id, dept_name) VALUES(20, '분석팀');
SELECT*FROM hr.dept;
COMMIT;
ALTER TABLE hr.emp ADD dept_id number;
SELECT*FROM hr.emp;
INSERT INTO hr.emp(id,name,day,dept_id) VALUES(1,'홍길동',sysdate,10);
INSERT INTO hr.emp(id,name,day,dept_id) VALUES(2,'나얼',sysdate,30);
COMMIT;
SELECT e., d.
FROM hr.emp e, hr.dept d
WHERE e.dept_id = d.dept_id; => 홍길동만 나옴, 나얼 30번 부서 없기 때문
SELECT e., d.
FROM hr.emp e, hr.dept d
WHERE e.dept_id = d.dept_id(+);
DELETE FROM hr.emp;
COMMIT;
SELECTFROM hr.emp;
SELECTFROM hr.dept;
ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY(dept_id) REFERENCES hr.dept(dept_id);
Table HR.EMP이(가) 변경되었습니다.
SELECT*FROM user_constraints WHERE table_name IN ('EMP', 'DEPT');
▽▽▽OWNER DONSTRAINT_NAME TYPE TABLE_NAME
HR EMP_DEPT_ID_FK R EMP
SELECT*FROM user_cons_columns WHERE table_name IN ('EMP', 'DEPT');
SELECT*FROM hr.emp;
INSERT INTO hr.emp(id,name,day,dept_id) VALUES(1,'홍길동',sysdate,10);
INSERT INTO hr.emp(id,name,day,dept_id) VALUES(2,'나얼',sysdate,30); -- 오류 발생
primary key에 없는 킷값이 입력되면 암됨
DELETE FROM hr.dept WHERE dept_id = 10;
-- 오류; child record found (foreign key제약조건이 걸려 있기 때문에 참조하고 있는 자식 데이터가 있어서 삭제를 불허함)
DELETE FROM hr.dept WHERE dept_id = 20;
-- 수행됨, foreign key제약조건 걸려 있지만 참조하고 있는 자식 데이터가 없으므로 삭제 허
DROP TABLE hr.dept;
-- 오류 보고 -
ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 - "unique/primary keys in table referenced by foreign keys“
● 제약조건을 삭제하는 방법
SELECT*FROM user_constraints WHERE table_name IN ('EMP', 'DEPT');
ALTER TABLE hr.emp DROP CONSTRAINT EMP_DEPT_ID_FK;
ALTER TABLE hr.dept DROP PRIMARY KEY;
