[Oracle] 제약조건

제약조건

Primary key, Foreign key, Unique, Not Null, Check

 

부적합한 데이터를 막아준다!

제약조건 선언 시기 - table 생성 시(권장), table 생성 후

오라클은 제약조건명 부여하는 걸 권장(MySQL은 안 줘도 된다는 느낌), 생략 시 고유한 제약조건명을 자동 부여한다.

제약조건명은 테이블이 달라도 겹치면 안 된다. DB 안에서 고유해야 한다!

[constraint 제약조건명지정] 제약조건지정
ex) 테이블약자_컬럼약자_제약조건유형약자

 

 

- MySQL의 경우

컬럼레벨 문법으로 쓰되, 포린키만 테이블 레벨 문법으로 쓰길 권장한다.

create table test1
-- 컬럼레벨 문법
(id int primary key,
name varchar(10) not null,
email varchar(20) unique not null,
grade int check (grade between 1 and 3),
abc int,
-- 테이블레벨 문법
foreign key(abc) references test2(aaa));

 

- Oracle은 컬럼레벨, 테이블레벨 둘 다 사용한다.

 

 

NOT NULL

not null은 컬럼 레벨로만 작성해야 한다(MySQL, Oracle 동일)

null 값이 들어올 수 없는 제약 조건

 

CREATE TABLET test1
(	id NUMBER(10)     CONSTRAINT t1_id_nn NOT NULL,
  	name VARCHAR2(30) CONSTRAINT t1_name_nn NOT NULL,
  	job VARCHAR2(20),
  	email VARCHAR2(20),
  	phone VARCHAR(20) CONSTRAINT t1_ph_nn NOT NULL,
  	start_date DATE	);

test1 테이블 생성

 

UNIQUE

고유한 값만 들어올 수 있는 제약조건

중복된 값이 들어올 수 없다(null 값은 중복될 수 있다. not null이면 불가)

CREATE TABLE test2
(   id NUMBER(10)     CONSTRAINT t2_id_nn NOT NULL
		      CONSTRAINT t2_id_uk UNIQUE,
    name VARCHAR2(30) CONSTRAINT t2_name_nn NOT NULL,
    job VARCHAR2(20),
    email VARCHAR2(20),
    phone VARCHAR2(20) CONSTRAINT t2_ph_nn NOT NULL
    		       CONSTRAINT t2_ph_uk UNIQUE,
    start_date DATE,
    CONSTRAINT t2_email_uk UNIQUE(email)	);

 

test2 테이블 생성

UNIQUE 제약조건을 줬지만 DESC로는 조회되지 않는다!

DB사전으로 test2 테이블의 내가 제약조건 걸었던 제약조건명과 타입을 조회할 수 있다.

 

 

PRIMARY KEY

기본키 제약조건

NOT NULL + UNIQUE의 성격을 가지고 테이블당 한 번만 선언 가능

 

test3 테이블의 제약조건 조회

 

FOREIGN KEY

자기 자신 테이블이나 다른 테이블의 특정 컬럼(PK, UK)을 참조하는 제약조건

- 테이블 레벨 문법은 제약조건 유형 뒤에 컬럼명을 한번 더 적어야하다보니 FOREIGN KEY를 생략할 수 없다.

- 컬럼 레벨 문법은 FOREIGN KEY를 생략할 수 있다. REFERENCES가 있는 제약조건 유형은 FK뿐이니까~

 

테이블 레벨 문법 (FOREIGN KEY 기입 생략)

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID	NUMBER(6),
    LAST_NAME	VARCHAR2(25) NOT NULL,
    EMAIL	VARCHAR2(25),
    SALARY	NUMBER(8, 2),
    HIRE_DATE	DATE NOT NULL,
    DEPARTMENT_ID	NUMBER(4),
    
    CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES DEPARTMENTS(DEPARTMENT_ID)	);

 

컬럼 레벨 문법  (FOREIGN KEY 기입 필수)

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID	NUMBER(6),
    LAST_NAME	VARCHAR2(25) NOT NULL,
    EMAIL	VARCHAR2(25),
    SALARY	NUMBER(8, 2),
    HIRE_DATE	DATE NOT NULL,
	
    DEPARTMENT_ID NUMBER(4) CONSTRAINT EMP_DEPTID_FK
    REFERENCES DEPARTMENTS(DEPARTMENT_ID)	);

 

user_constraints 조회, 표시한 거 많이 쓰인다.

 

- CONSTRAINT_TYPE : P, U, R(reference), C(NOT NULL, CHECK)

- SEARCH_CONDITION : NOT NULL, CHECK 구분

- R_CONSTRAINT_NAME : FK가 어디에 참조되어있는 지 알고 싶은 경우,

FK 제약조건이 참조하는 부모컬럼에 선언된 제약조건명(R_CONSTRAINT_NAME에 나온 걸로 다시 조회해봐야한다)

 

NOT NULL과 CHECK는 C로 같지만, SEARCH_CONDITION의 결과로 구분할 수 있다.

나머지는 NN 조건이 붙어있지만 SALARY > 0로 되어있는 것이 CHECK

 

자주 쓰일 것 같은 구문이니 치환변수로 두고 sql문을 저장해두자!

SELECT  CONSTRAINT_NAME, CONSTRAINT_TYPE,
	SEARCH_CONDITION, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE LOWER(TABLE_NAME) = LOWER('&t_name');

 

CHECK

컬럼이 만족해야하는 조건문을 자유롭게 지정하는 제약조건

컬럼 레벨과 테이블 레벨은 똑같이 사용하면 된다.

salary number(10), 
constraint OOO check (salary > 0), 
(==) 
salary number(10) constraint OOO check (salary > 0),

 

 

employees1 테이블 생성

employees1 제약조건 정보 조회

 

제약조건이 두 개 이상일 경우

(+둘 이상 PK를 주거나 CHECK 제약조건 주는 방법)

내가 작성한 것과 비교

 

-- 테이블 이름 : TITLE
Create table title
(	title_id NUMBER(10) CONSTRAINT title_id_pk PRIMARY KEY,
	title VARCHAR2(60) CONSTRAINT title_nn NOT NULL,
	description VARCHAR2(400) CONSTRAINT desc_nn NOT NULL,
	rating VARCHAR2(4) CONSTRAINT rating_ck CHECK (rating IN ('G','PG','R','NC17','NR')),
	category VARCHAR2(20) CONSTRAINT category_ck 
	CHECK (category IN ('DRAMA','COMEDY','ACTION','CHILD','SCIFI','DOCUMENTARY')),
	release_date DATE	);

-- 테이블 이름 : TITLE_COPY
Create table title_copy
(	copy_id NUMBER(10),
	title_id NUMBER(10) CONSTRAINT title_id_fk REFERENCES title(title_id),
	status VARCHAR2(15) CONSTRAINT status_nn NOT NULL
 	CONSTRAINT status_ck CHECK (status IN ('AVAILABLE','DESTROYED','RENTED','RESERVED')),
	CONSTRAINT copy_title_id_pk PRIMARY KEY (copy_id, title_id)	);