[DB] 테이블 & 제약조건

테이블

• 데이터베이스의 기본적인 데이터 저장 단위.
• 테이블은 사용자가 접근 가능한 모든 데이터를 보유함. 레코드& 컬럼으로 구성.
• 시스템 내에서 독립적으로 사용되길 원하는 엔티티를 표현할 수 있다.
  ex) 회사에서의 고용자, 제품에 대한 주문
• 두 엔티티간의 관계를 표현할 수 있다.
  ex) 회사에서의 고용자 & 그들의 숙련도 / 제품 & 주문의 관계

생성시 주의할 점

• 테이블 이름 & 컬럼명 : 항상 알파벳 문자로 시작. 0-9, A-Z, $, #, _ 사용 가능.
• 컬럼이름은 30자 초과 불가능, 예약어 사용 불가능
• 한 테이블 안에서 컬럼 이름은 같을 수 없으며, 다른 테이블과의 컬럼 이름은 같을 수 있음.

---

테이블의 제약조건

제약조건이란?
=> 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러가지 규칙을 적용해 놓는 것이라 생각하면 됨.
간단하게 말하면 테이블 안에서 데이터의 성격을 정의하는 것.

• 제약조건은 데이터의 무결성 유지 를 위해서 사용자가 지정할 수 있는 성질
• 모든 제약조건은 데이터 사전(Data Dictionary) 에 저장된다.
• 의미있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있다.
• 제약조건은 테이블 생성 당시 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER) 명령어를 통해서도 추가 가능하다.
• NOT NULL 제약조건은 반드시 컬럼 레벨에서만 정의 가능하다.

---

1. NOT NULL 조건

• 컬럼을 필수 필드화 시킬 때 사용.
• NOT NULL 제약조건 설정 시 해당 컬럼에는 꼭 데이터를 입력해야 함.

-- emp3라는 테이블을 만들고, ename 컬럼의 제약조건명을 emp_nm_ename 으로 하여 NOT NULL 제약조건을 설정하자.
SQL> CREATE TABLE emp3(
	ename VARCHAR2(30) CONSTRAINT emp_nm_enmae NOT NULL
	);

-- 제약조건을 확인할 때는 USER_CONSTRAINTS 뷰를 통해서 확인할 수 있다.
SQL> SELECT CONSTRAINT_NAME
     FROM USER_CONSTRAINTS
     WHERE TABLE_NAME = 'EMP3';
     
CONSTRAINT_NAME
-----------------------
emp_nn_ename 

2. UNIQUE 조건

• 데이터의 유일성을 보장(=> 중복되는 데이터가 존재할 수 없음)하고, 자동으로 인덱스가 생성.

-- EMP2 테이블을 생성한 뒤, ALTER 를 이용해서 제약조건을 추가해준다. (제약조건명 : EMP2_UK_DEPTNO)
ALTER TABLE EMP2
ADD CONSTRAINT EMP2_UK_DEPTNO UNIQUE(deptno);

-- 제약조건을 삭제한다.
ALTER TABLE EMP2
DROP CONSTRAINT EMP2_UK_DEPTNO;

✅ 테이블 생성 후 제약조건 설정시

제약조건의 추가 & 삭제시 둘다 ALTER를 이용 !
추가는 ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명
삭제는 ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명

3. CHECK 조건

• 컬럼의 값을 어떤 특정 범위로 제한

-- EMP2 테이블의 comm 컬럼이 1~100 까지의 값만 가질 수 있도록 체크 제약조건 생성.
ALTER TABLE EMP2
ADD CONSTRAINT EMP2_CK_COMM CHECK (comm >= 1 AND comm <= 100);

-- 제약조건 삭제
ALTER TABLE EMP2
DROP CONSTRAINT EMP2_CK_COMM;

-- 10000, 20000, 30000, 40000, 50000 의 값만을 가질 수 있는 체크 제약조건 생성.
ALTER TABLE EMP2
ADD CONSTRAINT EMP2_CK_COMM2 CHECK comm IN (10000,20000,30000,40000,50000);

4. DEFAULT (컬럼 기본값) 지정

• 데이터를 입력하지 않아도 지정된 값이 기본으로 입력된다.

-- hiredate 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다.
SQL> CREATE TABLE emp4(
     ... (컬럼생략) ...,
     hiredate DATE DEFAULT SYSDATE ); 

5. PRIMARY KEY 지정

• 기본키 : UNIQUE + NOT NULL 의 결합과 같음.
• 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. => 참조 무결성
• UNIQUE 제약조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성, 그 이름은 기본 키 제약조건의 이름과 같다.

  ✅ INDEX
  : 검색 키로서 검색 속도를 향상시킨다.
  ( UNIQUE, PRIMARY KEY 생성시 자동적으로 생긴다.)

-- PRIMARY KEY 생성 예제. 제약조건명 EMP5_PK_EMPNO
CREATE TABLE EMP5(
    empno NUMBER CONSTRAINT EMP5_PK_EMPNO PRIMARY KEY
);

-- 테이블 생성 후 ALTER 명령어로 PRIMARY KEY 생성 예제.
ALTER TABLE EMP5 
ADD CONSTRAINT EMP5_PK_EMPNO PRIMARY KEY (empno);

6. FOREIGN KEY (외래키) 지정

• 기본키를 참조하는 컬럼 or 컬럼들의 집합
• 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터 형이 일치해야 한다. (이를 어기면 참조 무결성 제약에 의해 테이블을 생성할 수 없음 !)
• 외래키에 의해 참조되고 있는 기본키 : 삭제 불가 !!
• ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제될 때 같이 삭제된다

-- emp2 테이블의 deptno 컬럼이 dept 테이블의 deptno 컬럼을 참조하도록 외래키를 생성하자.
ALTER TABLE EMP2 ADD CONSTRAINT emp2_fk_deptno
FOREIGN KEY (deptno) REFERENCES TO DEPT(deptno);

/* 
FOREIGN KEY 뒤의 (deptno)는 emp2 테이블의 deptno 컬럼
REFERENCES TO 뒤에는 참조할 테이블 명 (참조할 컬럼) 순서로 써준다.
*/ 

---

제약조건의 확인

제약조건을 확인할 수 있는 테이블은 USER_CONS_COLUMNS, USER_CONSTRAINTS 이다.
이 둘을 이용해서 해당 테이블의 제약조건을 알아내도록 해보자.

• USER_CONS_COLUMNS : 컬럼에 할당된 제약조건 조회.

• USER_CONSTRAINTS : 유저가 소유한 모든 제약조건 조회.

-- 테이블 이름을 입력했을 때, 해당 테이블의 컬럼명 / 제약조건 종류 / 제약조건 이름 을 출력.

SELECT A.COLUMN_NAME
     , CASE WHEN B.CONSTRAINT_TYPE = 'C' THEN 'CHECK OR NOT NULL'
            WHEN B.CONSTRAINT_TYPE = 'F' THEN 'FOREIGN KEY'
            WHEN B.CONSTRAINT_TYPE = 'P' THEN 'PRIMARY KEY'
            WHEN B.CONSTRAINT_TYPE = 'U' THEN 'UNIQUE KEY'
       END AS CONSTRAINT_TYPE // DECODE 문을 써도 되지만 CASE 문도 사용 가능함. 마지막에 END AS 뒤에는 알리아스로 줄 이름 써주면 됨.
     , A.CONSTRAINT_NAME
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = UPPER('&table_name')
// 치환변수 & 을 사용(실행 시 입력창 뜬다. 자바의 scanner 같은 것.) 
// UPPER 는 대문자로 치환.
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = B.TABLE_NAME;

-- 테이블 명을 입력 하면 됩니다.
table_name의 값을 입력하십시오: emp

-- 결과

COLUMN_NAME   CONSTRAINT_TYPE	    CONSTRAINT_NAME
-----------   ----------------     ----------------
EMPNO	      CHECK OR NOT NULL	    SYS_C007071