sqldeveloper-11

-- ○ (무결성) 제약조건
-- 1. 데이터 무결성 : 완전성, 정확성, 일관성을 보장하는 특성. ==> 트랜잭션 ACID 
-- ※ 트랜잭션 : 데이터베이스의 논리적 실행 단위 ==> DML (데이터 입력,수정,삭제) + COMMIT
-- Atomicity : 원자성, 트랜잭션이 모두 실행되거나 하나도 실행되지 않게 
-- Consistency : 일관성, 트랜잭션 처리 이후 데이터의 모순이 없는 상태 (잔고가 없는 상태의 트랜잭션은 중단)
-- Isolation : 독립성, 트랜잭션 처리도중 다른 트랜잭션이 개입하지 못하는 상태
-- Durability : 지속성, 성공적인 트랜잭션 처리 이후 영원히 반영되는 상태(이체: A계좌 잔액감소, B계좌는 증가)

-- 2. 제약조건 : 데이터의 무결성을 지키기 위해 제한된(한) 조건을 말한다
-- 2-1. Primary Key : 주식별자, PK  ==> 중복되지 않는, NULL 허용하지 않는 특성(=NOT NULL) 
--      └ 사원ID는 중복되지 않는, NULL 허용하지 않는 유일한 식별자

-- 2-2. Unique Key : 유일키, UK ==> 중복되지 않는, NULL 허용하는 특성
--      └ 게시물 TITLE은 중복을 허용하지 않도록하는 유일키
--
-- 2-3. NOT NULL : NN ==> 빈값(NULL) 허용하지 않는 특성
--      └ 게시물의 TITLE은 빈값을 허용하지 않게
--
-- 2-4. CHECK : 체크, CK ==> 특정 값의 범위인지에 따라서 트랜잭션을 실행하는 특성
--      └ 사원정보를 등록할때 salary를 0으로 입력되지 않게 ==> 최저급여? 최저시급을 준수하게~
--
-- 2-5. Foreign Key : 외래키, FK ==> 다른 테이블의 PK를 참조하는 특성 (
--      └ 사원정보중 부서정보를 입력할때 없는 부서ID를 등록하지 못하게 REFERENCES~
--
-- 2-6. DEFAULT : 기본값O, 제약조건X
--      └ 게시물 등록시, 입력일자를 오늘날짜/시간으로 기본값으로 선택하게

-- 3.제약조건 실습
-- 3-1. 제약조건 확인
SELECT  constraint_name, constraint_type, table_name
FROM    user_constraints;
-- SYS_C008370 : 시스템이 자동으로 부여하는 제약조건명 SYS_CXXXXX 형태로 넘버링
-- ※ 제약조건명을 관례상 테이블명_컬럼_제약조건약어 형태로 작성한다.
-- Constraint Type / 약어
-- P : Primary Key ==> PK
-- U : Unique Key  ==> UK
-- C : Check       ==> CK
-- R : Reference   ==> FK
--     Not NULL    ==> NN

CREATE TABLE members (
id VARCHAR2(20) PRIMARY KEY, -- 컬럼 레벨: SYS_CXXXXX
name VARCHAR2(20) UNIQUE NOT NULL,
email VARCHAR2(50)    
);

CREATE TABLE members (
id VARCHAR2(20) CONSTRAINT member_id_pk PRIMARY KEY, -- 컬럼 레벨: member_id_pk
name VARCHAR2(20) UNIQUE NOT NULL,
email VARCHAR2(50)    
);
--Table MEMBERS이(가) 생성되었습니다.

-- 1) Not NULL (NULL) : NULL을 허용하지 않음.
CREATE TABLE null_test (
--    컬럼명 자료형(길이) 제약조건
    id VARCHAR2(10) NOT NULL,
    pw VARCHAR2(20)
);
--Table NULL_TEST이(가) 생성되었습니다.
-- NULL : '' 또는 NULL(or null) 명시하면 빈값으로 처리
INSERT INTO null_test
VALUES ('test1',null);
--1 행 이(가) 삽입되었습니다.
INSERT INTO null_test
VALUES ('test2','');
--1 행 이(가) 삽입되었습니다.
INSERT INTO null_test 
VALUES ('','pwd3');
--SQL 오류: ORA-01400: NULL을 ("HANUL"."NULL_TEST"."ID") 안에 삽입할 수 없습니다
--01400. 00000 -  "cannot insert NULL into (%s)"

UPDATE null_test
SET id=''
WHERE id='테스트1';
--SQL 오류: ORA-01407: NULL로 ("HANUL"."NULL_TEST"."ID")을 업데이트할 수 없습니다
--01407. 00000 -  "cannot update (%s) to NULL"

SELECT  constraint_name, constraint_type, table_name
FROM    user_constraints
WHERE   table_name='CHECK_TEST';

-- 2) CHECK 제약조건
-- NUMBER : 최대 4000byte 이내
-- NUMBER(10) : 10byte 이내
-- NUMBER(8,2) : 전체 8자리 숫자에서 6자리 정수, 2자리 소수
CREATE TABLE check_test (
    id  NUMBER(10),
    name VARCHAR2(20),
    salary NUMBER(8) CHECK (salary > 3000)
);
--Table CHECK_TEST이(가) 생성되었습니다.

INSERT INTO check_test
VALUES (1, '홍길동', 2000);
--ORA-02290: 체크 제약조건(HANUL.SYS_C008375)이 위배되었습니다
INSERT INTO check_test
VALUES (1, '홍길동', 3500);
--1 행 이(가) 삽입되었습니다.

CREATE TABLE check_test2 (
    id  NUMBER(10),
    name VARCHAR2(20),
    gender CHAR(1) CHECK (gender IN ('M','F'))
);
--Table CHECK_TEST2이(가) 생성되었습니다.
INSERT INTO check_test2
VALUES (1, '홍길동', 'A');
--ORA-02290: 체크 제약조건(HANUL.SYS_C008376)이 위배되었습니다

-- 3) UNIQUE 제약조건
-- 유일한 값, 중복은 허용     vs   PRIMARY KEY : 유일한 값, 중복 허용x (NOT NULL + UNIQUE)
-- ※ 복합키 생성가능 ==> UNIQUE (id, name)

CREATE TABLE unique_test (
    id NUMBER(10) NOT NULL,
    title VARCHAR(30) UNIQUE NOT NULL,
    content VARCHAR(50)
);
--Table UNIQUE_TEST이(가) 생성되었습니다.

INSERT INTO unique_test 
VALUES (1, 'Hello WOrld!','System.out.print("Hello World")');
--1 행 이(가) 삽입되었습니다.
INSERT INTO unique_test 
VALUES (2, 'Hello WOrld!','System.out.print("Hello World")');
-- ORA-00001: 무결성 제약 조건(HANUL.SYS_C008379)에 위배됩니다

ALTER TABLE unique_test
DROP CONSTRAINT SYS_C008379;
--Table UNIQUE_TEST이(가) 변경되었습니다. ==> 제약조건 삭제

ALTER TABLE unique_test
ADD CONSTRAINT unique_test_title_uk UNIQUE(title);
--ORA-02299: 제약 (HANUL.UNIQUE_TEST_TITLE_UK)을 사용 가능하게 할 수 없음 - 중복 키가 있습니다

TRUNCATE TABLE unique_test;
--Table UNIQUE_TEST이(가) 잘렸습니다.

INSERT INTO unique_test 
VALUES (2, 'Hello WOrld!','System.out.print("Hello World")');
-- 1 행 이(가) 삽입되었습니다

SELECT *
FROM    user_constraints
WHERE   table_name = 'PK_TEST';


-- 4) PRIMARY KEY 제약조건
-- 유일한 값, 중복 허용x 특성
-- ※ 복합키 생성가능 ==> PRIMARY KEY (id, name)

CREATE TABLE pk_test (
    id NUMBER PRIMARY KEY,
    title VARCHAR2(30) UNIQUE    
);
--Table PK_TEST이(가) 생성되었습니다.

ALTER TABLE pk_test
DROP CONSTRAINT SYS_C008382;
-- Table PK_TEST이(가) 변경되었습니다.   :  시스템이 명명한 제약조건 삭제

ALTER TABLE pk_test
ADD CONSTRAINT pktest_id_pk PRIMARY KEY(id);
-- Table PK_TEST이(가) 변경되었습니다.    : 사용자가 명명한 제약조건 추가 (단일키)

ALTER TABLE pk_test
DROP CONSTRAINT PKTEST_ID_PK;
-- Table PK_TEST이(가) 변경되었습니다.    : 사용자가 명명한 제약조건 삭제

ALTER TABLE pk_test
ADD CONSTRAINT pktest_id_title_pk PRIMARY KEY(id, title);
-- Table PK_TEST이(가) 변경되었습니다.    : 사용자가 명명한 제약조건 추가 (복합키)

 

-- 5) FOREIGN KEY 제약조건
-- 외래키, 참조키, FK
-- ※ 부모 테이블의 특정 컬럼을 자식 테이블에서 참조
-- 복합키 설정 가능, NULL 허용
-- ex> employees 테이블의 department_id 컬럼이, departments 테이블의 department_id
-- 컬럼을 참조하여, 사원 데이터(row) 입력시 존재하는 부서 번호를 입력하도록 제약함.

-- CTAS : 다른 테이블의 1)구조만 복사하거나 2) 구조 + 데이터 복사

-- =============================================================================
-- 오라클 데이터베이스 이모지 : NVARCHAR2 로 설정해야, 정상적으로 저장된다.
-- MariaDB 이모지 : utf8mb4_general_ci 타입등으로 COLLATE를 mb(multi byte)로 지정해야 함.
-- =============================================================================

-- 부모 테이블
CREATE TABLE "dept_test" (
    "dept_id" NUMBER,
    "dept_name" VARCHAR2(20) NOT NULL UNIQUE,
    CONSTRAINT dept_id_pk PRIMARY KEY (dept_id)
);
-- Table DEPT_TEST이(가) 생성되었습니다.
/* MariaDB CREATE코드 : ` (백틱) - 테이블명, 컬럼명 구분 기호, ' - 문자열 , " - 공백
CREATE TABLE `dept_test` (
`dept_id` INT NOT NULL,
`dept_name` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`dept_id`)
)
COLLATE='utf8mb4_general_ci'
;*/

-- 자식 테이블
CREATE TABLE emp_test (
    emp_id NUMBER,
    last_name VARCHAR2(20) NOT NULL,
    job_id VARCHAR2(10),
    emp_dept NUMBER,
    CONSTRAINT emp_id_pk PRIMARY KEY (emp_id), -- 테이블 레벨에서 정의
    CONSTRAINT dept_test_id_fk FOREIGN KEY (emp_dept) REFERENCES dept_test (dept_id)
);
--Table EMP_TEST이(가) 생성되었습니다.

DESC dept_test;
INSERT INTO dept_test
VALUES (10, '개발부');
INSERT INTO dept_test
VALUES (20, '기획부');
INSERT INTO dept_test
VALUES (30, '설계부');

DESC emp_test;
INSERT INTO emp_test (emp_id, last_name, job_id)
VALUES (1, 'Lee', 'Backend');
INSERT INTO emp_test (emp_id, last_name, job_id, emp_dept)
VALUES (2, 'Choi', 'FrontEnd', 100);
--ORA-02291: 무결성 제약조건(HANUL.DEPT_TEST_ID_FK)이 위배되었습니다- 부모 키가 없습니다
INSERT INTO emp_test (emp_id, last_name, job_id, emp_dept)
VALUES (2, 'Choi', 'FrontEnd', 20);
--1 행 이(가) 삽입되었습니다.