DB Constraints (NOT NULL, UNIQUE, primary key, foreign key)

오라클SQL과 PL/SQL을 다루는 기술책 보면서 공부

NULL: 값이 없음을 의미한다. NULL 허용이란 해당 컬럼에 값을 넣지 않아도 된다는 의미이며, NOT NULL로 명시한 컬럼에 데이터를 넣지 않으면 INSERT가 불가능하다.

 

제약조건

제약조건은 컬럼에 대한 속성 형태로 정의하지만 엄연히 오라클 데이터베이스 객체 중 하나이며 데이터 무결성을 보장하기 위한 용도로 사용된다. 제약조건에는 NOT NULL, UNIQUE, 기본키, 외래키, CHECK등이 있다.

1)     NOT NULL

컬럼명 데이터타입 NOT NULL

not null 제약조건을 주고 ''(null 값)을 추가해본다.

 

col_not_null 컬럼은 not null로 제약조건이 걸어져있기 때문에 ''(null 값)이 insert되지 않는다.

 

하지만 null값이 아닌 값을 넣으면 정상적으로 insert 되는 것을 확인할 수 있다.

2)     UNIQUE

UNIQUE 제약조건은 말 그대로 해당 컬럼에 들어가는 값이 유일해야 한다는 의미이다. 즉 중복 값을 허용하지 않는다.

컬럼명 데이터타입 UNIQUE 혹은 CONSTRAINTS 제약조건명 UNIQUE(컬럼명, …)

UNIQUE 비교 대상에는 NULL은 제외된다는 점을 기억해 두자.

unique를 실행할 table을 생성해주고 컬럼에 unique라는 제약조건을 설정해준다. 'aa' 'aa' 값을 넣고 한번더 넣으려고 하면 unique라는 제약조건에 위배되기 때문에 insert 되지 않는다.

다시 한번도 넣지 않은 'bb', 'bb'값을 넣으면 잘 insert 되는 것을 확인할 수 있다.

3)     기본키

UNIQUE와 NOT NULL 속성을 동시에 가진 제약조건으로 테이블 당 1개의 기본키만 생성할 수 잇다.

4)     외래키

외래키는 테이블 간의 참조 데이터 무결성을 위한 제약조건이다.

Constraint 외래키명 foreign key(컬럼명, …) References 참조 테이블(참조 테이블 컬럼명, …)

사원 테이블에도 부서번호(department_id)컬럼이 있고, 부서 테이블에도 부서번호(department_id)컬럼이 있다. 만약 신규 사원이 입사해 데이터를 입력하는 중에 부서 테이블에 없는 부서번호(department_id)를 입력한다면 어떻게 될까? 논리적으로 봐도 합당하지 않고 나중에 이 신입사원에 대한 부서정보를 제대로 찾을 수 없다. 즉 참조 무결성이 깨진다. 하지만 이럴 때 외래키를 생성해 놓으면 부서정보에 없는 부서번호를 입력할 때 오라클은 오류를 발생시켜 잘못된 데이터가 입력되는 것을 방지한다. 물론 삭제와 변경도 마찬가지이다.

외래키의 제약사항은 다음과 같다.

-       반드시 참조하는 테이블이 먼저 생성되어야 하며, 참조키가 참조 테이블의 기본키로 만들어져 있어야 한다.

-       외래키에 사용할 수 있는 컬럼 개수는 최대 32개다.

-       여러 컬럼을 외래키로 만들려면, 참조하는 컬럼과 외래키 컬럼의 순서와 개수는 같아야 한다.

-       REFERENCES : 참조할 부모 테이블과 부모 테이블에 있는 컬럼을 정의한다.

-       ON DELETE CASCADE : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.

        부모 테이블의 행이 지워지면 자식 테이블의 행도 같이 지워진다.

-       ON DELETE SET NULL : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다.

  부모 테이블의 행이 지워지면 자식 테이블의 행은 NULL 값으로 설정된다.

참고: https://keep-cool.tistory.com/51

 

--부모테이블(참조 테이블)을 먼저 만든다.

create table depart_test2(

department_id number primary key);

부모 테이블(참조 테이블)에 데이터 삽입

 

자식 테이블 생성

외래키를 사용하여 자식테이블을 생성한다.

참조테이블(부모 테이블)을 확인한다.

자식테이블에 department_id값을 1~3까지 추가해준다. => 잘 추가가 된다.

참조하고 있는 depart_test2테이블에 있는 1,2,3,4,5인 경우에는 잘 입력이 된다.

하지만 참조 테이블에는 department_id가 1~5까지만 존재하기 때문에 6부터는 insert가 되지 않는 것을 확인할 수 있다.

하지만 참조 테이블에 없는 department_id 부터는 insert 되지 않는다.

------------------------------------ 외래키 추가 내용 ------------------------------------------

1) 테이블 생성

-- 사원 테이블 생성 create table employees( department_id number(6) primary key, emp_name varchar2(80)); --부서 테이블 생성 create table departments( department_id number(6) primary key, department_name varchar2(80));

 

2) 외래키 생성

a. 테이블 생성 후 설정

ALTER TABLE 테이블명 ADD CONSTRAINTS 외래키 이름 FOREIGN KEY (참조컬럼) REFERENCES 참조 테이블명(참조컬럼)

 

 

b. 테이블 생성 시 설정

CONSTRAINTS 외래키 이름 FOREIGN KEY(컬럼명) REFERENCES 참조 테이블명(참조컬럼)

 

참고: https://meaownworld.tistory.com/117

이미 테이블을 생성했으니 a 방법인 테이블 생성 후 설정하는 방법으로 외래키를 생성해보겠다.

alter table employees add constraints fk_emp_dep_id foreign key (department_id) references departments(department_id)

 

user_constraints 주요컬럼

constraint_type은 P, R, U, C 4가지 값 중에 하나를 갖는다.

P: PRIMARY KEY R: FOREIGN KEY U: UNIQUE C: CHECK, NOT NULL

즉 저기서 R은 외래키(FOREIGN KEY)라는 뜻이다.

 

참조 테이블에 데이터를 추가한다.

insert into departments values(111111,'aaaaaa'); insert into departments values(222222,'bbbbbb'); insert into departments values(333333,'ccccc'); insert into departments values(444444,'ddddd');

 

 

참조 테이블에 없는 데이터를 부모 테이블에서 추가하면 어떻게 될까?

에러를 뱉어낸다.

반대로 참조 테이블에 있는 데이터를 추가하면 잘 추가되는 것을 확인할 수 있다.

 

외래키를 삭제해본다

Alter table [스키마.]테이블명 drop constraints 제약조건명;

select constraint_name, constraint_type, table_name, search_condition from user_constraints where table_name = 'EMPLOYEES';

alter employees drop constraints FK_EMP_DEP_ID;

 

삭제 테스트

자식테이블(참조하는 테이블)이 있을 경우 부모테이블(참조테이블)은 삭제되지 않는다.