[20110713] Oracle DATATYPE & UNIQUE & FOREIGN KEY

-- Oracle DataType
-- 1. 글자형
-- 1) CHAR : 고정길이 문자열 2000Byte 까지
-- 표준 : CHARACTER(n), CHAR(n)
dname CHAR(10);
INSERT INTO aaa(dname)
VALUES('ABCDE')
-- 3) VARCHAR2 : 가변길이 문자열 4000 Byte 까지,
-- VARCHAR2 는 가변형 이다.
-- 표준 : VARCHAR(n)
dname VARCHAR2(10);
INSERT INTO aaa(dname)
VALUES('ABCDE')
-- 위와 같은 쿼리문일 경우 이름이 5글자 이면 자동으로 5글자 크기로 맞춰준다.

-- 2. 숫자형
-- 1) 정수형 : NUMBER(n) -38승 ~ +38승
deptno NUMBER(10)
INSERT INTO AAAI(deptno)
VALUES(12345)
-- NUMBER 의 MAX 가 10이라면 VALUES 의 값은 0000012345 로 된다.
-- 2) 실수형 : NUMBER(P,s) P는 (전체 자리수), S는 (소수점 이하 자리수)
number(8,2)
123456.12 --> 
--> NUMERIC(P,s), DECIMAL(P,s), INT(n), INTEGER(n)

-- 3. 날자형
-- DATE
2011년 7월 13일 --> '13-JUL-11'

DESC user_tables;
SELECT table_name FROM user_tables;

CREATE TABLE dept1
(
	deptno	NUMBER(3),
    dname	CHAR(10),
    loc 	VARCHAR2(20),
    hiredate	DATE
);

INERT INTO DEPT1
VALUES (1, 'MARKETTING', 'SEOUL', '13-JUL-11');
INERST INTO DEPT1
VALUES (2, 'SALES', 'PUSAN', '2011-06-30');

-- Column 의 옵션
-- 1)DEFAULT
-- 2)Constraint

-- 1.DEFAULT 옵션
-- 1)INSERT 할 때 NULL 방지할 목적
-- 2)DEFAULT 로 지정할 컬럼의 데이타 타입이 일치해야 한다.


CREATE TABLE DEPT2
(
	deptno     NUMBER(2),
    hiredate    DATE DEFAULT SYSDATE
);

INSERT INTO DEPT2
VALUES (10, default);
SELECT * FROM DEPT2;

CREATE TABLE Jusorok
(
	bunho	NUMBER(2),
    gender  CHAR(4) DEFAULT '남자'
);

INSERT INTO Jusorok
VALUES (1, '여자');

INSERT INTO Jusorok
VALUES (2, default);

SELECT * FROM jusorok;

-- 2. Constraint
-- 1) 여러가지가 있지만 4가지가 가장 중요하고 많이 사용 된다.
-- 2) PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE
-- 3) column-level constraint, table-level constraint
-- column-level
-- column_name		column_datatype		[CONSTRATINT]
-- constraint_name PRIMARY KEY,
-- constraint_name 의 권장사항
-- 테이블 이름_컬럼 이름_제약 조건 약어
-- ex) testpk_deptno_pk
CREATE TABLE testpk
(
	deptno	NUMBER(2) 
    CONSTRAINT testpk_deptno_pk PRIMARY KEY,
    dname VARCHAR2(10)
    
);
INSERT INTO testpk
VALUES(1, 'aaa');

INSERT INTO testpk
VALUES(1, 'bbb');
-- PRIMARY KEY 는 중복 될수 없다.

-- table-level constraint

CREATE TABLE testpk1
(
	deptno NUMBER(2),
    dname VARCHAR2(10)
    CONSTRAINT testpk1_deptno_pk PRIMARY KEY(deptno)
);

DESC user_constraints;
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'TESTPK';

CREATE TABLE nonpk
(
	deptno NUMBER(2),
    dname VARCHAR2(10)
);

ALTER TABLE nonpk
ADD CONSTRAINT nonpk_deptno_pk PRIMARY KEY(deptno)

-- 2. NOT NULL
-- 1) 열에 NULL 값 허용하지 않는다.
-- 2) INSERT, UPDATE 시 적용된다.
-- 3) 각 컬럼의 기본값은 NULL 허용이다.
-- 4) NOT NULL 제약조건은 반다싀 column-level 제약 조건이다.
-- 5) PK 와 UNIQUE는 기본적으로 NOT NULL 이다.

CREATE TABLE NULLDemo
(
	deptno NUMBER(2)
    dname VARCHAR2(10),
    CONSTRAINT nulldemo_dname_nn NOT NULL,
    loc VARCHAR2(10),
    CONSTRAINT nulldemo_deptno_pk PRIMARY KEY
);
INSERT INTO nulldemo
VALUES (1, 'aa', '');

INSERT INTO nulldemo
VALUES (2, 'bbb', null);

-- 아래 INSERT 문은 ERROR 발생 한다. DNAME 을 NOT NULL 로 했기 때문이다.
INSERT INTO nulldemo
VALUES (3, NULL, 'seoul');

-- 3. UNIQUE
-- 1) 열 값의 유일성 보장
-- 2) 값이 중복이 되는 행이 없음을 보장
-- 3) PK 와 유사하지만, NULL 허용가능
-- 4) column-level, table-level constraint 모두 가능

CREATE tabke uniquedemo
(
	deptno,	NUMBER(2) PRIMARY KEY,
    dname	VARCHAR2(10) 
    	CONSTRAINT uniquedemo_dname_nn NOT NULL,
    loc,	VARCHAR2(10)	UNIQUE
    	CONSTRAINT uniquedemo_deptno_pk PRIMARY KEY(deptno),
    	CONSTRAINT uniquedemo_loc_uk	unque(loc)
);

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = UPPER('uniquedemo');

INSERT INTO uniquedemo
VALUES (1, 'aaa', 'SEOUL');

INSERT INTO uniquedemo
VALUES (2, 'bbb', '');

-- 4. FOREIGN KEY(외래키)
-- 1) 다른 테이블의 특정 칼럼을 참조하기 위한 키
-- 2) 참조당하는 테이블을 부모테이블(Main table) 이라 하고,
--    참조하는 테이블을 자식테이블(detail table)이라 한다.
-- 3) 참조하는 칼럼은 참조당하는 컬럼의 데이타타입이 일치해야 한다.
-- 4) 참조당하는 칼럼은 PK 이거나 Unique 이어야 한다.

CREATE table Bumo
(
	deptno NUMBER(2)
    CONSTRAINT bumo_deptno_pk PRIMARY KEY,
    dname VARCHAR2(10)
    CONSTRAINT bumo_dname_uk UNIQUE
);

-- 참조하는 Jasik 은 참조를당하는 Bumo 와 타입이 같아야 한다.
CREATE TABLE Jasik
(
	empno NUMBER(4)
    CONSTRAINT jasik_empno_pk PRIMARY KEY,
    deptno NUMBER(2)
    CONSTRAINT jasik_deptno_fk
    REFERENCES bumo(deptno)
    -- bumo의 deptno 를 참조한다.
);