#10 DBMS _DCL, KEY_수정중

정진형·2021년 11월 11일
0

DBMS

목록 보기
10/11

oracleDB XE11g R2, DBeaver 21.2.3 wihdow10

  1. COMMIT
  • 모든 작업을 정상적으로 처리하겠다고 확장하는 명령어이다
  • 트랙젝션의 처리 과정을 데이터베이스에 반영하가 위해서, 변경된 내용을 모두 영구 저장한다
  • commit을 수행하며느 하나의 트랜젝션 과정을 종료하게 된다
  • 트랜젝션(insert, update, delete) 작업 내용을 실제 DB에 저장한다
  • 이전 데이터가 완전히 UPDATE된다
  • 모든 사용자가 변경한 데이터의 결과를 볼 수 있다
  1. ROLLBACK
  • 작업 중 문제가 발생했을 때, 트랜젝션의 처리 과정에서 발생한 변경 사항을 취소하고, 트랜젝션 과정을 종료시킨다
  • 트랜잭션으로 인한 하나의 문제가 발생하면 , 모든 작업을 취소해야 하기 때문의 하나의 논리 적인 단위로 구성해 놓아야 한다
  • 문제가 발생하면, 논리적인 작업의 단위를 모두 취소해 버리면 되기 때문이다
  1. Commit과 Rollback 명령어의 장점
  • 데이터 무결성을 지킬수 있다
  • 영구적으로 변경하기 전에 데이터의 변경사항을 확인할 수 있다
  • 논리적으로 연관된 작업을 그룹화 할 수 있다
  1. 자동 Rollback되는 경우
  • 비정상적인 종료

5.자동으로 Commit 되는 경우

  • DDL문(create, alter, drop, treuncte)
  • DCL문(grant, revoke)
  • insert, update, delete 작업 후 Commit하지 않고 오라클 정상 종료시에 Commit 명령어 입력하지 않아도 정상 Commit 후 오라클 종료
  1. 컬럼속성(무결성 제약조건)
  • not null : 널값이 입력되지 못하게 하는 조건
  • unique : 중보된 값이 입력되지 못하게 하는 조건
  • primart key : not nll + unique + index 의 기능을 가지고 있다
  • foreign key : 다른 테이블 필드(컬럼)를 참조해서 무결성을 검사하는 조건
  1. Primary Key(기본키)
  • 기본키 역시 기본적인 제약조건들은 테이블 생성할때 같이 정의한다
  • 테이블당 하나만 정의 가능하다(두개 이상의 PK는 조합기/복합키 라고 불린다, 묶어서 하나로 본다)
  • PK는 not null + unique +index 의 기능을 가지고 있다
  • 자동 index가 생성되는데 이는 검색 키로서 검색 속도를 향상시킨다

7-1. 기본키(Primary Key) 선언방법

CREATE TABLE PKTEST (
	COL1	VARCHAR2(20)	PRIMART KEY
);

-- 1. 컬럼명 옆에 주키를 선언
CREATE TABLE PKTEST (
	COL1	VARCHAR2(20) CONSTRAINTS CON_PK PRIMART KEY
);

-- 2. 해당 컬럼에 CONSTRAINTS [제약조건명] PRIMAEY KEY
CREATE TABLE PKTEST (
	COL1	VARCHAR(20),
    COL2	VARCHAR(20),
    CONTRAINTS CON PRIMARY KEY ( COL1, COL2)
);

-- 3. CONSTRAINTS CON_PK PRIMARY KEY(COL1, COL2, ...)
CREATE TABLE PKTEST (
	COL1	VARCHAR(20),
    COL2	VARCHAR(20),
);
ALTER TABLE PKTEST ADD CONTRAINTS CON PRIMARY KEY ( COL1, COL2);

4. ALTER TABLE [테이블명] ADD CONSTRAINTS [제약조건명] PRIMARY KEY(컬럼명);
  1. Foreign Key(외래키)
  • 외부키, 외래키, 참조키, 외부 식별자, FK라고도 볼린다
  • FK가 정의된 테이블을 자식테이블이라 칭한다
  • 참조되는 테이블을 부모 테이블이라고 한다
  • 부모테이블 컬럼에 존재하는 데이터만 자식테이블에 입력할 수 있다
  • 부모테이블은 자식의 테이터나 테이블이 삭제된다고 영향을 받지 않는다
  • 참조하는 데이터 컬럼과 데이터 타입이 반듯이 일치해야 한다
  • 참조할 수 있는 컬럼은 기본키(PK)이거나 Unique만 가능하다(보통 PK랑 엮는다)

8-1. 외래키 설정

CREATE TABLE 테이블명 (
	컬럼	데이터타입 CONSTRATIONS 제약조건이름 FORENIGN KEY
    		REFERENCES 참좌할테이블(참조할컬럼)
            	[ON DELETE CASCADE | ON DELETE SET NULL]
);

CREATE TABLE 테이블명 (
	컬럼	데이터타입
    	CONSTRATIONS 제약조건이름 FORENIGN KEY
    		REFERENCES 참좌할테이블(참조할컬럼)
            	[ON DELETE CASCADE | ON DELETE SET NULL]
);

8-2. 왜래키 삭제 옵션

  • ON DELETE CASCADE
    참조되는 부모 테이블의 행에 대한 DELETE를 허용한다 즉, 참조되는 부모테이블 값이 삭제되면 연쇄적으로 자식 테이블 값 역시 삭제된다
  • ON DELETE SET NULL
    참조되는 부모 테이블의 행에 대한 DELETE를 허용한다 이건 CASCADE와 다른데 부모 테이블 값이 삭제되면 해당 참조하는 자식테이블의 값들은 NULL값으로 설정된다
  1. CHECK
  • 주어진 값만 허용하는 조건
-- Auot Commit -> Manual Commit
-- COMMIT
CREATE TABLE SAMPLE (
	DEPTNO		NUMBER(20) ,
    	DEPTNAME	VARCHAR2(15) ,
        DEPLOC		VARCHAR2(15) ,
        DEPMANAGER	VARCHAR2(10)
);

INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (10, '기획실', '서울', '홍길동') ;

SELECT * FROM SAMPLE s ;

COMMIT ;

INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (10, '기획실', '서울', '홍길동') ;
INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (10, '기획실', '서울', '가나다') ;

COMMIT ;
ROLLBACK ;

INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (40, '기획실', '서울', '홍길동') ;
INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (40, '기획실', '서울', '가나다') ;

-- 커밋을 안하고 롤백을 하면 마지막 커밋 시점으로 돌아간다
ROLLBACK;

INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (40, '기획실', '서울', '홍길동') ;
INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (40, '기획실', '서울', '가나다') ;

-- 커밋을 해주면 실제 DB가 수정 된다
COMMIT ;

-- 추가 수정과 커밋이 없어서 변동 사항이 없다
ROLLBACK ;

INSERT INTO SAMPLE (DEPTNO, DEPTNAME, DEPLOC, DEPAMANAGER)
VALUES (40, '기획실', '부산', '가나다') ;

-- 커밋을 하지 않아서 실제 DB에 업데이트가 안되있다
UPDATE SAMPLE SET DEPTNAME = 'IT' WHERE DEPTNO = 40 ;

ROLLBACK;

-- DDL문 DCL문 자동 커밋
DROP TABLE SAMPLE ;

-- NOT NULL
-- 기본값 NULL 허용
CREATE TABLE NULL_TEST (
	COL1	VARCHAR2(20) NOT NULL ,
    	COL2	VARCHAR2(20) NULL ,
        COL3	VARCHAR2(20)
);

INSERT INTO NULL_TEST (COL1, COL2)
VALUES ('aa', 'bb');

-- 외래키
-- DADDY 테이블 MID 컬럼에 PK (부모 테이블)
-- DAUGHTER 테이블 MID 컬럼에 FK(자식 테이블
CREATE TABLE DADY (
	IDX	NUMBER (10),
    	MID	NUMBER (10) PRIMARY KEY	
);

CREATE TABLE DAUGHTER (
	IDX	NUMBER (10),
    	MID	NUMBER (10) PRIMARY KEY	
        CONSTRAINTS fk_da FOREIGN KEY (MID)
        REFERENCES DADDY (MID)
);

INSERT INTO DADDY VALUES (1, 20);
INSERT INTO DADDY VALUES (2, 20);

INSERT INTO DAUGHTER VALUES (100, 10);
INSERT INTO DAUGHTER VALUES (101, 10);
INSERT INTO DAUGHTER VALUES (102, 10);
INSERT INTO DAUGHTER VALUES (200, 10);
INSERT INTO DAUGHTER VALUES (201, 20);
-- 외래키로 물려있는 부모 테이블에 없는 컬럼 데이터로 인서트 해줄 수 없다
-- INSERT INTO DAUGHTER VALUES (300, 30);

DELETE FROM DAUGHTER d WHERE IDX = 100;
-- 왜래키로 몰려있는 부모 테이블에 있는 컬럼은 자식 컬럼에 같은 데이터가 있으면 삭제할 수 없다
-- DELETE FROM DADDY d WHERE IDX = 1;
-- DROP TABLE DADDY;

-- 먼저 자식 테이블의 중복되는 데이터를 삭제 한 후 부모 테이블을 삭제할 수 있다
DELETE FROM DAUGHTER d WHERE MID = 10;
DELETE FROM DAUGHTER d WHERE MID = 20;
DELETE FROM DADDY d WHERE IDX = 1;

DELETE FROM DAUGHTER d ;
DELETE FROM DADDY d ;
DROP FROM DAUGHTER d ;
DROP FROM DADDY d ;

-- ON DELETE CASCADE
CREATE TABLE DADY (
	IDX	NUMBER (10),
    	MID	NUMBER (10) PRIMARY KEY	
);

CREATE TABLE DAUGHTER (
	IDX	NUMBER (10),
    	MID	NUMBER (10) PRIMARY KEY	
        CONSTRAINTS fk_da FOREIGN KEY (MID)
        REFERENCES DADDY (MID) ON DELETE CASCADE
);

INSERT INTO DADDY VALUES (1, 20);
INSERT INTO DADDY VALUES (2, 20);

INSERT INTO DAUGHTER VALUES (100, 10);
INSERT INTO DAUGHTER VALUES (101, 10);
INSERT INTO DAUGHTER VALUES (102, 10);
INSERT INTO DAUGHTER VALUES (200, 10);
INSERT INTO DAUGHTER VALUES (201, 20);

DELETE FROM DADDY d WHERE IDX = 1;
DROP TABLE DAUGHTER;
DROP TABLE DADDY;

-- ON DELETE SET NULL
CREATE TABLE DADY (
	IDX	NUMBER (10),
    	MID	NUMBER (10) PRIMARY KEY	
);

CREATE TABLE DAUGHTER (
	IDX	NUMBER (10),
    	MID	NUMBER (10) PRIMARY KEY	
        CONSTRAINTS fk_da FOREIGN KEY (MID)
        REFERENCES DADDY (MID) ON DELETE SET NULL
);

INSERT INTO DADDY VALUES (1, 20);
INSERT INTO DADDY VALUES (2, 20);

INSERT INTO DAUGHTER VALUES (100, 10);
INSERT INTO DAUGHTER VALUES (101, 10);
INSERT INTO DAUGHTER VALUES (102, 10);
INSERT INTO DAUGHTER VALUES (200, 10);
INSERT INTO DAUGHTER VALUES (201, 20);

DELETE FROM DADDY d WHERE IDX = 1;

-- CHECK
CREATE TABLE CHECK_TEST (
	GENDER	VARCHAR2(10)
    		CONSTRAINS CHECK_GENDER CHECK (GENDER IN ('M', 'F')
);

INSERT INTO CHECK_TEST VALUES ('M');
INSERT INTO CHECK_TEST VALUES ('F');

-- 체크문에서 설정해준 이 외에 값은 입력이 안된다
INSERT INTO CHECK_TEST VALUES ('남자');
INSERT INTO CHECK_TEST VALUES ('여자');

0개의 댓글