[PostgreSQL] 3.데이터 타입에 알맞은 테이블 만들기

테이터 타입에 알맞은 테이블 만들기
- 다양한 데이터 타입 이해 및 형 변환
- 데이터 무결성에 대한 이해
- 컬럼 값 제한 및 제약조건 적용

1. 데이터 타입이란
    1) 테이터 타입
        -데이터 타입을 선언함으로써 테이블의 각 컬럼 속에 있는 테이터의 성질을 정의할 수 있다
        -변수명은 소문자로 작성하고 변수명과 구분되게 데이터타입과 제약조건은 대문자로 보통 작성한다
        -숫자형,화폐형,문자형,날짜및시간,불리언형,배열형,제이슨형
        
        (1) 숫자형
            -테이블의 컬럼속 데이터들을 숫자의 형태로 저장하는 것
            -INTEGER : 소수점을 제외한 정수만 입력할 때, INTEGER대신 INT로 작성해도 된다
            -NUMERIC : 소수점도 정확하게 입력받아야 할 때
            -NUMERIC(p,q)형태로 작성하고 p는 전체자릿수, q는 소수점 자릿수를 입력한다
             예)NUMERIC(4,3) 전체길이가 4자리, 소수점이하 3자리 수 이다(0.001~90999)
             NUMERIC(p)형태는 일반적으로 입력받은 숫자의 길이를 제한할 때 사용한다. p자리 정수를 나타낸다
            -SERIAL : INSERT 기본 값으로 1씩 추가되며 값이 자동 생성, 프라이머리 키 데이터타입으로 주로 사용

        (2) 화폐형
            -금액을 저장하는 테이터타입 이며, 분수의 형태로 금액을 저장한다. 테이블에서는 소수점 두 자릿수까지 표현
            -일반적인 화폐형식을 포함하여 다양한 형식의 입력이 가능하다
            -로캘식별자를 이용해서 각 나랑 맞는 사용자 인터페이스로 변경해 사용한다
             
        (3) 문자형
            -문자형에는 VARCHAR(n), CHAR(n), TEXT 세가지가 있다
            -(n)은 양의 정수이며, 문자열의 길이를 의미한다
            -VARCHAR(n) : n이하의 문자의 길이 그대로 저장, 가변적이다
            -CHAR(n)    : "문자길이+공백" 형태로 n에 맞추어 저장
                            만약 CHAR(20)으로 저장하고 5글자만 적었다면 나머지15는 공백으로 채워버린다. 고정적이다
            -TEXT       : 길이에 상관없이 모든 문자열을 저장(=n을 쓰지않은 VARCHAR)
            -VARCHAR에 (n)을 지정하지 않는다면 임의의 길이의 모든 문자열을 허용한다
            -VARCHAR과 CHAR은 공백이 아닌 (n)을 초과하는 문자열을 입력하면 오류가 발생한다
            -게시판 같이 긴 문자열의 형태를 저장해야한다면, 길이를 제한하지 않는 TEXT를 사용한다

        (4) 날짜 및 시간
            -TIMESTAMP, DATE, TIME 세가지 타입이 있다
            -TIMESTAMP  : 현재 세계 표준시
            -TIMESTAMPZ : 세계 표준시 + 시간대 정보반영(한국은 GMT+9)
            -DATE       : 날짜 정보만 표시
            -TIME       : 시간 정보만 표시, 세계표준시
            
        (5) 불리언형
            -TRUE  : true, yes, on, 1 모두참
            -FALSE : false, no, off, 0 모두 거짓
            -NULL  : 알 수 없는 정보 또는 일부 불확실
            -불리언 테이터 타입은 보통 두가지 선택지를 가질 때 사용한다
              예를 들어 회원가입을 한 후 본인인증을 한 사람과 아닌 사람을 구분하는 컬럼을 넣는다면
              인증받은 사람은 true 아닌사람은 false로 구분한다

        (6) 배열형
            -배열(Array)은 여러 데이터를 하나의 집합으로 관리하기 위한 데이터타입이다
            -하나 이상의 여러 데이터를 저장할 수 있다
             1.배열 데이터타입은 Array[01012354, 01033235] 형태로 테이터를 입력할 수 있다
             2.작은따옴표 안에 중괄호로 배열 데이터타입을 입력할 수도 있다
               '{01012354,01033235}'

        (7) JSON형
            -JSON은 자바스크립트 객체 표기법의 약자이며 주로 서버와 웹애플리케이션 간에 데이터를 주고받을 때 사용
            -JSON은 키-벨류의 쌍으로 구성된 JSON오브젝트와/ 배열과 비슷한 구조를 갖는 JONS배열로 나뉜다
            -JSON오브젝트의 벨류에는 숫자, 문자열, JSON배열, JSON등이 올 수 있다
            -JSON은 JSON타입과 JSONB 데이터타입 두 가지 데이터타입을 가진다
            -두 데이터 타입 모두 동일한 값의 집합을 받아들이는 공통점이 있다
            -가장 큰 차이점은 JSON은 JSONB와 달리 입력 텍스트를 정확한 사본은 만들어서 저장한다는 것이고,
              이것을 불러와 처리할 때는 데이터를 재분석한 다음 실행한다
            -JSONB는 텍스트를 이진형태로 분해 후 저장해서 입력이 느리지만, 출력 시에는 재분석을 하지 않기 때문에
              JSON보다 처리속도가 빠르다. 일반적으로JSONB를 더 선호한다

            -JSON오브젝트는 키-밸류 한쌍의 형태로 이루어지는데, 다음구조는 밸류 속으로 또 다시 키-밸류 쌍, JSON오브젝트가 들어간 형태이다
              -> {"키 값":"벨류 값", "키 값":{"키 값":"벨류 값"}}

            -JSON 배열은 배열과 유사하며, JSON오브젝트가 차곡차곡 들어간 형태이다
              -> {{"키 값":"벨류 값"} , {"키 값":"벨류 값"}}


    2) 데이터 타입 변경하기
        (1) CAST 연산자
            -형변환을 할 수 있는 가장 기본적인 방법이다
            -CAST(표현식 AS 바꿀 데이터 타입)
            -숫자형으로 바꿀 수 없는 문자열이 포함되면 안된다

        (2) CAST형 연산자
            -CAST 연산자를 사용하지 않고 더 간단하게 데이터타입을 바꿀 수 있는 방법이다
             :: 연산자를 사용하다
             -> 값 :: 바꿀 데이터타입

2. 데이터의 값 제한하기
-빠르고 정확한 데이터베이스를 만들기 위해서는 정확성과 무결성이 필요하다

  1) 무결성 이란
    -무결성 : 데이터베이스 내에 정확하고 유효한 데이터만을 유지시키는 속성이다
    -불필요한 데이터는 최대한 제거하고, 합칠 수 있는 데이터는 최대한 합하는 것이다
    -생성, 조회, 수정, 삭제할 때 데이터 값을 일관되고 정확하게 유지하자는 특성이다

  2) 무결성의 제약조건
    (1) 개체 무결성(entity integrity)
      -모든 테이블이 primary key 를 가져야 하며 primary key로 선택된 컬럼은 고유하고 null 값을 허용하지 않아야 한다는 속성이다
      -주민번호처럼 개개인의 고유한 식별번화와 같이 데이터베이스에서도 개체를 식별하기 위한 정보가 필요하다
      -primary key는 데이터를 구분하는 고유한 정보이기 때문에 이 값이 비어있으면 안된다
      
    (2) 참조 무결성(referatial integrity)
      -외래키(foregin key) 값이 null값이거나 참조된 테이블의 기본 키 값과 동일해야 한다
      -데이터를 모델링 했을 때, 구 테이블간의 관계를 유효하게 하는 연결고리(외래키)가 있을 때
        두 테이블은 참조 관계를 갖는다고 한다
      
    (3) 범위 무결성(domain integrity)
      -사용자가 정의한 도메인 내에서 관계형 데이터베이스의 모든 열을 정의하도록 규정한다
      -도메인 형 : 기본 데이터타입을 기반으로 선택적으로 제약조건을 걸수 있는 사용자 정의 데이터타입이다
      -CREATE DOMAIN이라는 명령어로 도메인 데이터타입을 정의할 수 있다

  3) 컬럼 값 제한하기
    (1) NOT NULL 
      -NOT NULL 제약조건(constraint)은 null값을 허용하지 않는 조건이다
      -이 조건이 있으면 null값이 입력되면 오류가 발생한다
      -반대로 빈 값을 허용할 때는 null을 쓰거나 아무것도 쓰지 않아도 된다

    (2) UNIQUE
      -UNIQUE 제약조건은 말 그대로 유일한 값을 가져야 한다
      -UNIQUE 제약조건에 해당하는 컬럼 값은 테이블 내에서 유일한 값을 가져야 한다
      -예) cont_id NUMERIC(3) UNIQUE NOT NULL, (제약조건을 나열할 때 ,가 필요없음)
      -만일 UNIQUE제약조건이 여러 개 라면
        -> UNIQUE(cont_id, tel, email)

    (3) PRIMARY KEY 
      -primary key의 컬럼 값은 서로 달라야 하며, null값을 허용하지 않는다
      -UNIQUE 해야하며, NOT NULL해야 한다는 의미이다
      -주식별자, 주 키, 'PK' 라고 일컫는다
      -주 식별자 라는 말은 대상을 식별할 수 있는 대표 값이다
      -UNIQUE와 NOT NULL 제약조건을 가지면 해당 열의 정보를 대표할 수 있다
      -각 열을 대표하는 컬럼의 값을 primary key로 지정한다
      -보통 primary key의 데이터타입으로는 SERIAL을 사용한다
      -일반적으로 primary key는 테이블에 하나이지만 제약조건에 의해 여러 개일 경우,
        UNIQUE 제약조건과 유사하게 모든 컬럼들을 선언한 후 primary key를 설정한다
        -> PRIMARY KEY (book_id, admin_no)

    (4) FOREGIN KEY (외래 키)
      -자식이 부모의 행동을 참조하듯이, 참조관계에서 참조되는 테이블을 먼저 생성되어 있어야 하며,
       해당 테이블을 부모 테이블이라고 부른다
      
      -foregin key 제약조건
       1. 부모테이블이 자식테이블 보다 먼저 생성되어야 한다
       2. 부모테이블은 자식테이블과 같은 데이터타입을 가져야 한다
       3. 부모테이블에서 참조 된 컬럼의 값만 자식테이블에서 입력 가능하다
       4. 참조되는(부모테이블) 컬럼은 모두 primary key이거나 UNIQUE 제약조건 형식이여야 한다
       5. 부모테이블은 자식테이블보다 먼저 삭제 또는 수정할 수 없다

      -예) subj_code NUMERIC(5) REFERENCES subject(subj_id)
          (:subj_code는 자식테이블의 컬럼명, NUMERIC은 부모테이블과 동일, REFERENCES(참조), 
           subject는 부모테이블, subj_id는 참조되는 컬럼명)

      -만약 외래 여러 개 라면
       -> FOREGIN KEY (subj_code, subj_name) REFERENCES subject (subj_id, subj_name)

      -테이블을 지우는 상황을 ON DELETE, 테이블을 수정하는 상황을 ON UPDATE 라고 한다
      -ON DELETE의 다섯 가지 유형

        1. ON DELETE NO ACTION : 참조된 컬럼 값은 지울 수 없다
          -컬럼의 값을 삭제할 때는 -> DELETE FROM 컬럼명 WHERE 지울 데이터 조건
        
        2. ON DELETE RESTRICT : 참조된 컬럼을 지울 수 없다
          -NO ACTION과 RESTRICT의 차이는 트랜잭션이다??

        데이터베이스를 다루다보면 지울 수 없는 것을 지워야하는 예외적인 상황이 생긴다

        3. ON DELETE CASCADE : 부모컬럼 값이 지워지면서 그것을 참조하는 자식테이블의 '열'이 삭제된다. 즉 참조한 열을 모두 지운다

        4. ON DELETE SET NULL : 부모테이블에서 참조된 행이 삭제될 때 자식 테이블의 참조 행에서 해당 컬럼의 값을 자동으로 NULL로 세팅한다

        5. ON DELETE SET DEFAULT : 부모컬럼이 삭제되면서 자식 컬럼의 DEFAULT 값으로 대체한다
                                   즉, 자식테이블을 선언할 때 DEFAULT 값을 설정하면, 부모컬럼이 삭제될 때 설정된 DEFAULT값으로 변경된다 그외의 경우는 null값
          -> subj_id NUMERIC(5) DEFAULT 1 REFERENCES subject ON DELETE SET DEFAULT
          -조심해야할 점은 디폴트로 설정된 값도 외래 키 제약조건을 만족해야 한다는 것이다
           만약 디폴트값을 5로 줬는데 subject테이블에 1,3 밖에 없으면 참조되는 컬럼의 외래키 제약조건에 위배된다

    (5) CHECK
      -가장 일반적인 조건으로, CHECK 뒤에 나오는 식이 불리언타입의 TRUE를 만족해야 한다
      -예) 양수만을 입력받기로 제한하고 싶다면 CHECK 제약조건을 사용한다
          -> order_qty INTEGER CHECK (order_qty >0 )


3. Alter Table 
  1) 만들어진 테이블에 컬럼 추가하기
    -ADD COLUMN 속성을 사용한다
    -ADD COLUMN -> 컬럼이름 -> 테이터타입 -> 제약조건 

      ALTER TABLE 테이블이름
      ADD COLUMN 컬럼이름 테이터타입 제약조건;

    -ADD COLUMN를 통해서 새로운 컬럼이 추가한다면, 기존에 있던 열들을 모두 NULL 값을 갖는다
     만약 ADD COLUMN 뒤 제약조건에 NOT NULL 제약조건을 추가 한다면 오류발생
    -NOT NULL 조건을 추가하고 싶다면 
     1.NOT NULL 제약조건 없이 컬럼을 생성한다
     2.컬럼값을 수정한다 (UPDATE 이용해서 값을 주고)
     3.NOT NULL 제약조건을 추가한다 (ALTER COLUMN를 이용)
       -> ALTER TABLE 테이블명
          ALTER COLUMN 컬럼명 SET NOT NULL; (제약조건 추가된다)

  2) 만들어진 테이블에 컬럼 삭제하기
    -DROP COLUMN 속성을 사용한다

      ALTER TABLE 테이블이름
      DROP COLUMN 컬럼이름;

    -중요한 점은 다른컬럼이 지우려고 하는 컬럼을 참조하면 안된다는 것이다
     그러나 CASCADE를 사용하면 해결된다
    -예) 외래키 제약조건에 의해 book_name 를 참조하는 컬럼이 있다면 지울 수 없다
        그러나 ALTER TABLE 테이블이름 DROP COLUMN book_name CASCADE;
        CASCADE(참조한 열을 모두 지운다) 속성을 이용하면 지울 수 있다