테이블 제약 조건
- 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러가지 규칙을 적용해 놓는 것
- 간단하게 말하면 테이블 안에서 데이터의 성격을 정의하는 것.
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
참고