데이터 유형과 일치하는 테이블 만들기
– 다양한 데이터 유형 이해 및 변환
– 데이터 무결성에 대한 이해
– 열 값 제약 조건 및 제약 조건 적용
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) 화폐의 종류
– 금액을 저장하는 데이터 타입으로 금액은 분수의 형태로 저장됩니다. 표에는 소수점 이하 두 자리까지 표시됩니다.
– 일반 화폐 종류를 포함하여 다양한 종류의 입력이 가능합니다.
– 로케일 ID를 사용하여 각 사용자의 UI를 사용자 정의하십시오.
(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: 참, 예, an, 1 모두 참
-FALSE: 거짓, 아니오, 꺼짐, 0 모두 거짓
-NULL: 알 수 없는 정보 또는 불확실성
부울 데이터 유형은 일반적으로 두 가지 옵션이 있을 때 사용됩니다.
예를 들어 회원가입을 한 사람과 인증하지 않은 사람을 구분하는 컬럼을 포함하면
인증된 사람은 참으로 분류되고 인증되지 않은 사람은 거짓으로 분류됩니다.
(6) 배열 유형
배열은 여러 데이터를 집합으로 관리하기 위한 데이터 유형입니다.
– 하나 이상의 데이터를 저장할 수 있습니다.
1. 배열 데이터 타입은 배열 형태로 데이터 입력 가능 (01012354, 01033235)
2. 작은따옴표 안에 중괄호 안에 배열 데이터 유형을 입력할 수도 있습니다.
‘{01012354,01033235}’
(7) JSON 유형
-JSON은 JavaScript Object Notation의 약자로 주로 서버와 웹 애플리케이션 간의 데이터 교환에 사용됩니다.
-JSON은 배열과 유사한 구조로 키-값 쌍/JONS 배열로 구성된 JSON 객체로 나뉩니다.
– JSON 객체의 값은 숫자, 문자열, JSON 배열 및 JSON을 포함할 수 있습니다.
-JSON에는 JSON 유형과 JSONB 데이터 유형의 두 가지 데이터 유형이 있습니다.
– 두 데이터 유형 모두 동일한 값 집합을 허용한다는 공통점이 있습니다.
-주요 차이점은 JSONB와 달리 JSON은 입력 텍스트의 정확한 복사본을 생성하고 저장한다는 것입니다.
로드 및 처리 시 데이터를 다시 분석한 다음 실행합니다.
-JSONB는 텍스트를 이진 형식으로 분해하고 저장하므로 입력은 느리지만 출력 중에 다시 구문 분석되지 않습니다.
JSON보다 처리 속도가 빠릅니다. 일반적으로 JSONB 선호
-JSON 객체는 키-값 쌍으로 구성됩니다.
-> {“키 값”:”값 값”, “키 값”:{“키 값”:”값 값”}}
-JSON 배열은 배열과 유사하며 하나씩 삽입되는 JSON 개체의 형태를 취합니다.
-> {{“키 값”:”값 값”} , {“키 값”:”값 값”}}
2) 데이터 타입 변경
(1) CAST 연산자
– 가장 기본적인 형태의 변신입니다.
-CAST(AS 데이터 유형으로 대체될 표현식)
– 숫자 유형으로 변환할 수 없는 문자열을 포함할 수 없습니다.
(2) CAST형 연산자
이것은 CAST 연산자를 사용하지 않고 데이터 유형을 변경하는 더 쉬운 방법입니다.
:: 연산자 사용
-> Value :: 변경할 데이터 타입
2. 데이터 값 제한
빠르고 정확한 데이터베이스를 생성하려면 정확성과 무결성이 필요합니다.
1) 무결성이란 무엇입니까?
무결성: 데이터베이스에 정확하고 유효한 데이터만 유지하는 속성입니다.
– 불필요한 데이터는 최대한 제거하고 최대한 많은 데이터를 결합
– 데이터 생성, 조회, 수정, 삭제 시 일관되고 정확한 데이터 값을 유지하는 기능
2) 무결성 제약
(1) 실체 무결성
모든 테이블에는 기본 키가 있어야 하며 기본 키로 선택된 열은 고유하고 null이 허용되지 않아야 합니다.
-데이터베이스에서 객체를 식별하는 정보, 예. 사람의 고유 식별 번호가 필요합니다. 나. 주민등록번호
– 기본 키는 데이터를 구분하는 고유한 정보이므로 이 값은 비워 둘 수 없습니다.
(2) 참조 무결성
외래 키 값은 null이거나 참조된 테이블의 기본 키 값과 같아야 합니다.
– 기존 테이블 간의 관계를 유효하게 해주는 링크(외래 키)가 있을 때 데이터를 모델링할 때
두 테이블이 참조 관계에 있다고 합니다.
(3) 도메인 무결성
– 관계형 데이터베이스의 모든 열이 사용자 정의 도메인 내에서 정의되도록 지정
– 도메인 타입: 기본 데이터 타입에 따라 선택적으로 제한할 수 있는 사용자 정의 데이터 타입입니다.
-CREATE DOMAIN 명령을 사용하여 도메인 데이터 유형을 정의할 수 있습니다.
3) 열 값 제한
(1) 0이 아님
NOT NULL 제약 조건은 null 값을 허용하지 않는 조건입니다.
이 조건이 참일 때 null 값을 입력하면 오류가 발생합니다.
반대로 빈 값을 허용하려면 null 또는 아무것도 작성할 필요가 없습니다.
(2) 유니크
– UNIQUE 제약 조건은 문자 그대로 고유한 값을 가져야 합니다.
– UNIQUE 제약 조건과 일치하는 열 값은 테이블에서 고유한 값을 가져야 합니다.
-eg) cont_id NUMERIC(3) UNIQUE NOT NULL, (제약을 나열할 때 , 필요 없음)
-UNIQUE 제약 조건이 여러 개인 경우
-> 고유(cont_id, 전화, 이메일)
(3) 기본 키
– 기본 키 열 값은 서로 달라야 하며 null 값은 허용되지 않습니다.
-고유해야 합니다. 즉, 0이 아님을 의미합니다.
– “PK”로 표시되는 자산 식별자, 기본 키
– 단어주식별자는 객체를 식별할 수 있는 대표값이다.
-UNIQUE 및 NOT NULL 제약 조건이 있는 경우 열에 정보를 나타낼 수 있습니다.
– 각 행을 나타내는 열의 값을 기본 키로 설정
– 보통 SERIAL을 기본 키의 데이터 유형으로 사용합니다.
– 일반적으로 하나의 테이블에는 하나의 기본키만 존재하지만 by-constraint가 여러 개일 경우
UNIQUE 제약 조건과 유사하게 모든 열을 선언한 후 기본 키를 설정합니다.
-> 기본 키(book_id, admin_no)
(4) 외래 키
– 자식이 부모의 행동과 관련되듯이 참조 관계에서 참조하는 테이블이 먼저 생성되어야 하며,
이 테이블을 상위 테이블이라고 합니다.
-외래 키 제약
1. 상위 테이블은 하위 테이블보다 먼저 생성되어야 합니다.
2. 상위 테이블은 하위 테이블과 동일한 데이터 유형을 가져야 합니다.
3. 자식 테이블에는 부모 테이블에서 참조하는 열 값만 입력할 수 있습니다.
4. 참조된 열(상위 테이블)은 모두 기본 키이거나 UNIQUE 제약 조건의 형식이어야 합니다.
5. 상위 테이블은 하위 테이블보다 먼저 삭제하거나 수정할 수 없습니다.
-예) subj_code NUMERIC(5) 참조 주제(subj_id)
(:subj_code는 하위 테이블의 열 이름, NUMERIC은 상위 테이블과 동일, REFERENCES(참조),
주제는 상위 테이블이고 subj_id는 참조된 열 이름입니다.
-외래환자가 여럿인 경우
-> 외래 키(subj_code, subj_name) 참조 주제(subj_id, subj_name)
테이블을 삭제하는 상황을 ON DELETE라고 하고 테이블을 변경하는 상황을 ON UPDATE라고 합니다.
– ON DELETE 5종
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 참조 제목 ON DELETE SET DEFAULT
– 기본적으로 설정된 값은 외래 키 제약 조건도 충족해야 합니다.
기본값이 5로 설정되어 있지만 대상 테이블에 1과 3만 존재하는 경우 참조되는 컬럼의 외래 키 제약 조건을 위반한 것입니다.
(5) 확인
-가장 일반적인 조건으로 CHECK 다음의 표현식은 boolean 타입의 TRUE여야 합니다.
-예) 양수만 입력하도록 제한하려면 CHECK 제약 조건을 사용합니다.
-> 주문수량 INTEGER 확인 (주문수량 >0 )
3. 테이블 변경
1) 생성된 테이블에 컬럼 추가
-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 COLUMN 열 이름 SET NOT NULL; (제한 사항이 추가됩니다)
2) 생성된 테이블에서 컬럼 삭제
-DROP COLUMN 속성 사용
ALTER TABLE 테이블 이름
DROP COLUMN 열 이름;
중요한 점은 삭제하려는 열을 참조하는 다른 열이 없다는 것입니다.
그러나 CASCADE를 사용하면 해결됩니다.
-예) 외래키 제약으로 book_name을 참조하는 컬럼이 있으면 drop이 불가능하다.
그러나 ALTER TABLE 테이블 이름 DROP COLUMN 책 이름 CASCADE;
CASCADE(참조된 모든 열 지우기) 특성을 사용하여 이 작업을 수행할 수 있습니다.