[ Oracle DB ] scott 스키마 설치 | 제약 조건 설정 | 제약조건 스키마 | 제약 조건 확인

 scott 스키마 설치 

아래는 scott.sql 파일의 내용이다.

--
-- Copyright (c) Oracle Corporation 1999. All Rights Reserved.
--
-- NAME
-- demobld_scott.sql
--
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables.
--
-- USAGE
-- SQL> @demobld_scott.sql
--
-- 
-- scott/tiger 계정 생성

-- system 계정으로 접속한다.
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 999
SET ECHO OFF
SET CONCAT '.'
SET SHOWMODE OFF

-- PROMPT 
-- PROMPT specify password for SYSTEM as parameter 1:
-- DEFINE password_system     = &1

-- CONNECT system/&&password_system
-- 본스크립트는 system 계정에서 실행시켜야한다.

DROP USER scott CASCADE;

create user scott identified by tiger default tablespace users temporary tablespace temp profile default;
grant connect, resource to scott;
alter user scott account unlock;

-- 여기서 부터는 scott 계정으로 접속한다.
conn scott/tiger;

SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP (
	EMPNO 		NUMBER(4) NOT NULL,
	ENAME 		VARCHAR2(10),
	JOB 		VARCHAR2(9),
	MGR 		NUMBER(4),
	HIREDATE 	DATE,
	SAL 		NUMBER(7, 2),
	COMM 		NUMBER(7, 2),
	DEPTNO 		NUMBER(2)
	);

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

CREATE TABLE DEPT(
	DEPTNO 		NUMBER(2),
	DNAME 		VARCHAR2(14),
	LOC 		VARCHAR2(13) 
	);

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');

CREATE TABLE BONUS(
	ENAME 		VARCHAR2(10),
	JOB 		VARCHAR2(9),
	SAL 		NUMBER,
	COMM 		NUMBER
	);

CREATE TABLE SALGRADE(
	GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER
	);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY (DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.
EXIT

 

위 소스를 sql 확장자로 저장해준다.

---

 

oracle sql developer  에서 파일 > 열기를 이용해 sql 파일을 불러온 후,

그 파일을 ctrl + enter 키를 이용해 실행시킨다.

실행시키면 아래와 같이 커밋이 완료된다.

이전 페이지를 확인해보면, 

SCOTT와 HR 둘다 출력되는 것을 확인할 수 있다.

이제 scott_user 계정을 만들어 보자.

---

 scott_user 만들기 

좌측 초록색 + 버튼을 눌러 scott_user 를 추가해준다.

 

위 화면과 같이 설정해주고 테스트 해서 상태가 성공이면 저장한다.

 

비밀번호는 "tiger" 이다.

---

 설치 확인 

만들어진 scott_user를 더블 클릭해, 워크시트를 열고 show user; 를 실행해보자.

위 사진과 같이, scott 스키마 설치가 성공적으로 되었음을 확인할 수 있다.

 

SCOTT 을 활성화 시켜보자.

alter user scott identified by tiger account unlock;

이제 마지막 확인만 해보자

select username, account_status from dba_users where username in ('HR','SCOTT');

 

위 select 문은 sys_user 에서 실행해야 한다.

이는 아래 그림과 같다.

HR 은 다른 스키마이라서 상관 하지 말고, SCOTT 의 상태가 OPEN 이면 된다.

---

 SCOTT 스키마 확인하기 

자주 사용하는 것이니, 알아 두면 좋다.

user_objects user_tables user_constraints  user_tab_columns user_cons_columns

그래서 위 두개를 join해서 사용해서 유추하여 쓰면 된다.

스키마 구조 확인은 desc 를 이용한다.

---

 각각의 키들 

 1)primary key(pk)

 2)foreign key(fk) -> 자식한테 설정해주면 된다.
   primary key 만 참조하는 것으로 알고 있었지만, oracle 에서는 unique 키도 외래키로 참조 가능하다.
   (unique index도 가능)

 3)unique key(uk)
   학교에서 학번을 pk로 주고, uk는 주민번호라고 생각하면 쉽다.

 4)check(ck)
   제한두기 (점수는 0~100점까지), not null 도 check constraint 에 포함된다.

 5)default(df)

   오라클은 default를 제약조건으로 안본다.

---

 제약조건 확인하기 

우선 어떤 제약조건이 있는지 확인해보자.

desc user_constraints;

select owner, constraint_name,constraint_type, table_name, search_condition, r_constraint_name
from user_constraints order by 4;

 

not null 이라는 제약조건이 결과로 도출 되는 것을 확인할 수 있다.

---

 제약조건 만들기 

-- dept 라는 테이블에 deptno 를 pk 로 설정하는 제약조건 생성
alter table dept
add constraint pk_dept_deptno primary key(deptno);

-- emp 라는 테이블에 empno 를 pk 로 설정하는 제약조건 생성
alter table emp
add constraint pk_emp_empno
primary key(empno);

-- deptno 를 외래키로 하는 제약조건 생성
alter table emp
add constraint fk_emp_deptno foreign key(deptno) references dept(deptno);

-- 제약조건 생성 확인
select owner, constraint_name,constraint_type, table_name, search_condition, r_constraint_name
from user_constraints
order by 4;

 

마지막 select 문을 실행하면, 결과는 아래와 같이 출력된다.

 

user_cons_columns 스키마와 user_constraints 스키마를 join하여, 제약조건이 어떤 컬럼에 걸려있는지

확인할 수 있다.

select a.table_name, a.column_name,a.constraint_name,b.constraint_type 
from user_cons_columns a join user_constraints b 
on a.constraint_name=b.constraint_name;

---

 쓰레기 통 비우기 

show recyclebin --sql+명령어 
purge recyclebin; -- 휴지통:영구적으로 버린다는 것.sql명령어

이상한 쓰레기 값이 출력될 경우 휴지통을 한번 비워준다.

 

본 작성자는 sql생성하는 것을 여러번 실행하였더니, 쓰레기가 발생하였던 경험이 있다...ㅎ,ㅎ