[DB][SQL][국비교육] Day 28

➰ 제약사항의 종류

• CHECK : 컬럼에 들어갈 수 있는 데이터의 범위를 조건문으로 지정 👉🏻 WHERE절에서 사용할 수 있는 조건문을 제약조건으로 적용
  • SEARCH_CONDITION 항목으로 적용됨
    ✔ IS NOT NULL 조건을 적용한 CHECK 제약사항과 같음

CREATE TABLE cons_06 (
    data NUMBER CHECK (data>=1 AND data<=100)
    data NUMBER CHECK (data BETWEEN 1 AND 100) 
);

CREATE TABLE cons_07(
    data VARCHAR2(1) CHECK ( data IN ('Y', 'N'))
);

• DEFAULT : 데이터를 입력하지 않고 INSERT할 때 자동으로 들어가는 NULL값을 대체할 기본값을 지정
  • 무조건 null을 대체하는 것이 아님 👉🏻 지정하여 null값을 넣은 경우 null 대입 가능
  • 제약사항 객체 항목으로 추가되지 않음 ➡ user_constraints (제약사항 자료사전)으로 확인할 수 없음
  • 컬럼이 추가정보로 등록됨 ➡ user_tab_columns (테이블 컬럼 자료사전)으로 확인해야 함
    💡 NOT NULL보다 DEFAULT를 컬럼레벨로 걸어주는 것이 좋음

CREATE TABLE cons_08 (
    data NUMBER DEFAULT 123
    , data2 DATE DEFAULT sysdate
    , data3 VARCHAR2(10) DEFAULT 'HI' NOT NULL  --NOT NULL은 CHECK 제약조건으로 들어감
);

INSERT INTO cons_08 VALUES (null, null, 'ABCDE');
INSERT INTO cons_08 (data3) VALUES ('Apple');

• PRIMARY KEY, PK, 기본키, 주키 : 테이블을 대표하는 컬럼에 부여하는 키 제약사항 👉🏻 각 행들을 서로 구분하기위해 부여
  • NOT NULL, UNIQUE 제약 사항이 자동으로 부여됨 👉🏻 PRIMARY KEY 객체가 그런 속성을 갖고있을 뿐 제약사항 객체 항목이 별도로 생성되는 것은 아님
    가 두 속성을 가지고 있음
  • 인덱스가 자동으로 생성됨
  • 외래키(FK)가 참조할 수 있는 자격이 생김

CREATE TABLE cons_09 (
    no NUMBER CONSTRAINT pk_no PRIMARY KEY
    , data VARCHAR2(10) NOT NULL
);

--  두 컬럼을 같이 PK로 지정
CREATE TABLE cons_13 (
    no NUMBER
    , data VARCHAR2(10)
    
    , CONSTRAINT pk_cons13 PRIMARY KEY(no, data)    
);

--  에러, PK는 NULL값 불가 -> 둘 중 한 컬럼에 NULL이 설정되어 있음
INSERT INTO cons_13 VALUES (NULL, 'A');
INSERT INTO cons_13 VALUES (111, NULL);

--  두 컬럼의 값이 동시에 중복될 때 UNIQUE가 적용됨
INSERT INTO cons_13 VALUES (1, 'Apple');
INSERT INTO cons_13 VALUES (2, 'Banana');
INSERT INTO cons_13 VALUES (1, 'Banana');

• FOREIGN KEY, FK, 외래키, 참조키, 보조키 : 기본키(PK)를 참조하는 컬럼 👉🏻 참조하고 있는 컬럼과 데이터타입이 일치해야 함
  💡 테이블 레벨로 설정해야함

  CREATE TABLE cons_14 (
    num NUMBER
    , data VARCHAR2(20)
    
    , CONSTRAINT fk_num         
        FOREIGN KEY(num)        --FK 설정 / 자신 테이블의 컬럼 설정
        REFERENCES cons_12(no)   --참조할 PK 설정 / 참조할 테이블과 컬럼을 설정
);

💡 PRIMARY KEY와 FOREIGN KEY

• 식별 관계 : FK가 PK역할을 동시에 수행하는 경우

  • PK-FK로 묶인 컬럼이 중복값을 가지지 못함
  • FK를 가진 테이블에서 PK당 1개의 행만 가질 수 있음
  • PK를 분리하는 추가작업이 필요한 경우가 많음

• 비식별 관계 : FK컬럼이 PK역할을 수행하지 않는 행태

  • 한 테이블에서 FK와 PK가 서로 직접적인 연관이 없는 관계
  • 식별관계에서 FK와 별개의 컬럼을 추가하여 PK로 지정하면 비식별 관계가 됨 👉🏻 PK, FK분리하기

• 참조되고 있는 pk의 값은 함부로 지울 수 없음

  • ON DELETE CASCADE 추가 구문 : 참조하고 있는 PK값이 삭제될 때 FK를 가진 행도 같이 삭제되도록 설정
  • ON DELETE SET NULL 추가구문 : 참조하고 있는 PK값이 삭제되면 FK 컬럼의 값만 NULL로 바꾸도록 설정 ➡ 참조무결성 유지

--  값 삭제해보기
SELECT * FROM pk_test
--DELETE pk_test        --에러, fk키가 참조하고 있는 pk는 삭제할 수 없음 (child record found)
WHERE no = 1;

--  fk가 참조하고 있지 않은 pk데이터는 삭제 가능
SELECT * FROM pk_test
--DELETE pk_test
WHERE no = 3;

--  에러, fk로 참조되고 있는 pk를 가진 테이블은 삭제할 수 없음
DROP TABLE pk_test;

--  DELETE CASECADE 테스트
SELECT * FROM pk_test
--DELETE pk_test    --삭제됨    
WHERE no = 1;

--  DELETE SET NULL 테스트
SELECT * FROM pk_test
--DELETE pk_test
WHERE no = 1;

---

🔍 ERD, Entity Relation Digram, 개체관계도

DB 설계 문서 / ERD Cloud
개체(Entity)들의 속성(Attribute)과 개체들 간의 관계(Relation)를 도식화하여 표현한 것
👉🏻 [ 테이블들의 컬럼정보 / 테이블의 구조 / 테이블들 간의 연결 (pk, fk) ]을 하나의 그림으로 표현한 것

➰ 데이터베이스 설계에서 사용되는 용어

• 엔티티, Entity 🟥
  개체 / 정보가 저장되는 단위 / 테이블

• 속성, Attribute 🟡
  엔티티의 특성을 나타내는 세부 항목 / 컬럼

• 관계, Relation 🔷
  엔티티들 사이의 연관성 / PK-FK의 연결 (식별관계 비식별관계)

• 인스턴스, Instance
  엔티티의 실 데이터 / 행(row)

• 식별자, key, 키
  특정 인스턴스를 구분하기 위해 대표로 설정한 속성

  • 주 식별자 / Primary key : 엔티티의 각 인스턴스를 구분하는 기준이 되는 속성
  • 보조식별자, 보조키 / Foreign key
  • 외래식별자, 외래키 / Foreign key : 엔티티 간의 관계를 맺어주는 역할을 하는 속성
  • 복합식별자 / 슈퍼키 : 2개 이상의 컬럼을 이용한 키

---

📑여기까지 DDL(DB객체 스키마 SQL) - CREATE TABLE