#10 DBMS DCL,KEY修正中

oracleDB XE11g R2, DBeaver 21.2.3 wihdow10

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(컬럼명);

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

-- 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 ('여자');