주관적인 실무 SQL

FOREIGN KEY ( FK )

 

Foreign Key 설정

 

CREATE TABLE 테이블 (

           CONSTRAINT 제약_조건_이름 FOREIGN KEY (컬럼)

           REFERENCES 참조할_테이블 (참조할_컬럼)

           [ON DELETE CASCADE | ON DELETE SET NULL]

);

 

CREATE TABLE 테이블 (

컬럼 데이터타입 CONSTRAINT 제약_조건_이름 FOREIGN KEY

                                  REFERENCES 참조할_테이블 (참조할_컬럼)

                                  [ON DELETE CASCADE | ON DELETE SET NLL]

…

 

1.     테이블을 생성할 때 FK를 정의한다.

2.     FK가 정의된 테이블이 자식 테이블이다.

3.     참조되는 테이블을 부모 테이블이라고 한다.

4.     부모 테이블은 미리 생성되어 있어야 한다.

5.     부모 테이블의 참조되는 컬럼에 존재하는 값만을 입력 할 수 있다.

6.     부모 테이블은 FK로 인해 삭제가 불가능하다

7.     REFERENCES : 참조할 부모 테이블과 부모 테이블에 있는 컬럼을 정의한다.

8.     ON DELETE CASCADE : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.

è  부모 테이블의 행이 지워지면 자식 테이블의 행도 같이 지워진다.

 

9.     ON DELETE SET NULL : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.

è  부모 테이블의 행이 지워지면 자식 테이블의 행은 NULL 값으로 설정된다

 

10.  데이터 타입이 반드시 일치해야 한다.

11.  참조되는 컬럼은 PK이거나 UK(Unique key)만 가능하다.

12.  외부키 , 참조키 , 외부 식별자 등으로 불린다.

 

13.  자식 테이블에 데이터를 삽입할 때 부모 테이블의 참조하는 컬럼에 없는 값을 삽입하면 오류가 발생한다.

 

14.  일반적으로 데이터베이스를 설계할 때 자식 테이블의 FK컬럼은 부모 테이블의 참조하는 컬럼과 같은 이름을 사용한다. 다른 이름을 사용해도 무관하지만 되도록이면 같은 이름을 사용하는 것이 테이블 간의 관계를 한 번에 알아볼 수 있기 때문에 좋다.

 

CREATE TABLE emp (

           Eno varchar2(4) constraint emp_eno_pk primary key,

           Ename varchar2(10),

           Job varchar2(6),

           Mgr varchar2(4),

           Hdate date,

           sal number,

           dno varchar2(2) constraint emp_dno_fk references dept (dno)

);

 

Drop table emp cascade constraint;

è  Table emp이(가) 삭제되었습니다.

 

 

 

제약 조건 조회

 

오라클에서만 해당되는 내용이다. DB종류마다 다르므로 오라클을 사용할 경우만 참조하자.

 

SELECT c.table_name , c.constraint_name, c.constraint_type, s.column_name

FROM user_constraints c , user_cons_columns s

WHERE c.constraint_name = s.constraint_name

AND c.table_name in (검색_대상_테이블_목록)

ORDER BY c.table_name;

 

-      테이블에 정의된 제약 조건을 검색한다.

-      Constraint_name : 제약 조건 이름

-      Constraint_type : 제약 조건 타입

-      P(PK) , R(FK) , U(UK), C(NOT NULL, CHECK)

 

 

서브 쿼리를 사용한 INSERT 문 & 다이렉트 로드 ( Direct load )

 

기본 구조

 

INSERT INTO [/*+APPEND */] 테이블 [NOLOGGING] [(컬럼,컬럼,…)] SELECT 문장

 

 

 

 

 

한 번에 여러 행을 입력할 수 있다.

서브 쿼리에 검색된 행을 입력 값으로 사용한다.

컬럼 목록과 SELECT 문의 컬럼이 1:1 대응돼야 한다.

/++APPEND */ : 다이렉트 로드 , 대량의 입력 작업을 더 빠르게 수행한다.

[NOLOGGING] : 로그 정보를 남기지 않음으로 입력 작업이 빨라진다 ( 장애 발생시 복구 불가능 )

 

INSERT 문장을 실행할 때는 항상 테이블의 구조를 파악하고 작업해야 한다.

데이터 타입은 같아야 한다. 데이터 타입은 같아야 하지만 데이터의 크기는 달라도 상관없다. 주의할 것은 입력되는 데이터 크기보다 입력될 컬럼의 크기는 같거나 더 커야한다.

서브 쿼리에서 WHERE 절을 생략하면 그 테이블의 모든 데이터를 삽입한다.

 

다이렉트 로드 ( Direct load )

DML작업은 모두 메모리에서 이루어진다.오라클의 경우 SGA라는 메모리 영역 내에 데이터베이스 버퍼 캐시 (Database Buffer Cache)라는 영역을 작업 공간으로 이용하여 이를 통해 디스크 접근 횟수와 양을 줄여 성능을 향상시킨다. 쉽게 말해서 DML작업은 메모리에서 먼저 이루어진 후 트랜잭션이 종료된 이후에 실제 디스크에 저장시킨다 ( 메모리의 속도가 더 빠르기 때문에 성능 향상을 위해 ) 그러나 대량의 DML 작업을 할 경우 오히려 이 방식이 성능을 저하시킬 때가 있다.

메모리를 통하지 않고 디스크에 직접 작업을 수행하는 것을 다이렉트 로드라 한다. DML작업을 다이렉트 로드하기 위해서는 ‘/* + APPEND */’ 를 문장에 삽입한다. 그리고 디스크에 직접 저장시킨다고 해서 트랜잭션이 자동으로 종료되는 것이 아니기 때문에 INSERT 작업이 끝나면 반드시 트랜잭션을 종료(COMMIT)하면 된다.

 

 

 

 

 

 

 

서브 쿼리를 사용한 UPDATE 문

 

기본 구조

UPDATE 테이블

SET 컬럼명 = (SELECT 문장),

           컬럼명 = (SELECT 문장), ….

[WHERE 조건];

 

서브 쿼리의 결과 행은 반드시 하나여야 한다.

서브 쿼리에서 검색되는 데이터는 반드시 수정되는 컬럼과 1:1 대응돼야 한다.

[Ex] 사원번호 115사원의 직업과 급여를 205사원의 직업 , 124 사원의 급여와 동일하게 수정한다.

1.

Update employees

Set job_id = (select job_id from employees where employee_id = 205),

           Salary = (select salary from employees where employee_id=124)

Where employee_id = 115;

 

2.

Select employee_id , first_name , job_id , salary

From employees

Where employee_id in (115,124,205);

 

3.

Rollback;

 

 

[Ex2]

사원번호 115사원의 직업과 급여를 205 사원의 직업,급여와 동일하게 수정한다.

 

Update employees

Set job_id = (select job_id from employees where employee_id = 205) ,

           Salary = (select salary from employees where employee_id = 205)

Where employee_id = 115;

 

서브 쿼리를 사용한 DELETE문

 

DELETE FROM 테이블 WHERE 컬럼명 = (select문장);

 

[Ex1]

 

delete from copy_departments

where department_id = (select department_id from employees where first_name=’name’);

 

 

관계형 데이터베이스

 

RDB ( Relational Database )

관계형 데이터베이스는 키(key)와 값(value)의 간단한 관계를 2차원 표 형식으로 나타낸 데이터베이스 이다.

 

RDBMS ( Relational Database Management System )

RDBMS는 관계형 데이터베이스를 생성 , 갱신 , 관리하기 위한 시스템이다.

레코드 단위로 데이터 쓰기/읽기가 이루어진다.

RDBMS는 일반적으로 클라이언트가 요청을 보내면 서버가 처리해주는 C/S 구조로 되어있다.

 

개념 데이터 모델링

 

관계(Relationship)란?

-      관계란 엔터티와 엔터티 사이의 관계를 말한다. 즉 , 관리하고자 하는 업무 영역 내의 특정한 두 개의 엔터티 사이에 존재하는 많은 관계 중 특별히 관리하고자 하는 직접적인 관계(업무적 연관성)를 의미한다.

 

-      관계도 집합이다 : 관계는 두 엔터티 사이에 그 목적과 내용이 다른 여러 개의 관계가 동시에 존재할 수 있다. 마치 교통량이 너무 많거나 좀 더 나은 이동을 위해 특수 목적을 가진 더 세분화된 교량을 추가할 수도 있고, 작은 교량을 없애고 커다란 교량으로 통합할 수도 있듯이 관계 또한 크게 묶을 수도 있고 , 구체적으로 분할시킬 수도 있다.

 

-      직접 관계를 관계라고 한다

 

 

제약 조건

관계 모델이 정의되기 위해서는 몇 가지의 제약 조건이 있습니다.

 

1)    무결성 제약 조건 ( Integrity constraints )

한 객체에 저장되는 데이터를 제한하는 조건을 말합니다.

 

ㄱ.   도메인 제약 조건 : 각 튜플의 애트리뷰트는 도메인에 속하는 값이어야 합니다.

즉 , 도메인이 Integer면 Integer 값만 저장될 수 있습니다.

 

ㄴ.   엔티티 무결성 제약 조건

기본 키 값은 NULL이 될 수 없습니다.

 

ㄷ.   참조 무결성 제약 조건

어떤 릴레이션 A의 튜플이 다른 릴레이션 B의 튜플을 참조하려면 , 참조하려는 그 튜플은 B 릴레이션 내에 존재해야 합니다.

외래 키 ( Foreign key ) 는 참조 무결성 제약조건을 만족해야 합니다.

 

 

2)    키 제약 조건

서로 다른 튜플은 동일한 키 애트리뷰트를 갖지 않아야 한다는 조건을 말합니다.