[DB] 📚 테이블 제약 조건 🕵️ 정리

 

테이블 제약 조건

- 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러가지 규칙을 적용해 놓는 것
- 간단하게 말하면 테이블 안에서 데이터의 성격을 정의하는 것.

 

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 조건

• 데이터의 유일성을 보장(=> 중복되는 데이터가 존재할 수 없음)하고, 자동으로 인덱스가 생성.
• unique은 null허용하지만, primary key는 null허용 안함
• unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

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

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

 

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 (컬럼 기본값) 지정

• 데이터를 입력하지 않아도 지정된 값이 기본으로 입력된다.
• default라고 값을 명시하면 기본값이 들어감
• 열이름이 명시되지 않으면 자동 기본값
• 값이 직접 명기되면 기본값은 무시됨.

alter table 테이블명 alter column 필드값 set default 10;

insert into 테이블명 values("홍길동","24",default,“180”);

-- 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);

 

• 만약 테이블에 각 필드값에 유니크한 값이 없다면, 필드 두 개를 묶어서 primary key로 지정 가능하다.

constraint pk_value primary key (필드1,필드2)

 

6. FOREIGN KEY (외래키) 지정

• 기본키를 참조하는 컬럼 or 컬럼들의 집합 (외래키는 기본키나 유니크가 아니면 생성 제약)
• 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터 형이 일치해야 한다. 
  (이를 어기면 참조 무결성 제약에 의해 테이블을 생성할 수 없음 !)
• 외래키에 의해 참조되고 있는 기본키 : 삭제 불가 !!
• on update cascade하면 기본키가 수정될 경우 외래키도 같이 수정해준다는 말
• 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

---

Reference

https://velog.io/@sezzzini/DB

http://www.gurubee.net/lecture/1013