데이터베이스3-3 테이블의 제약조건(primary key, foreign key)

제약조건(Constraint)

- 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해 여러 가지 규칙을 적용해 놓는 것(중복방지)

- 데이터의 무결성 유지를 위해 사용자가 지정할 수 있는 성질

- 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER) 명령어를 통해서도 추가가 가능하다.

- NOT NULL 제약조건은 컬럼을 필수 필드화 시킬 때 사용한다.(PRIMARY KEY는 기본으로 NOT NULL 이다.)

 

1. NOT NULL

- 컬럼을 필수 필드화 시킬 때 사용한다.

 

> CREATE TABLE EMP {

           EMPNO VARCHAR2(20) NOT NULL );

 

-> EMPNO 컬럼에는 꼭 데이터를 입력해야 한다.(데이터를 입력하지 않으면 작업 불가능!)

---

2.  UNIQUE

- 데이터의 유일성을 보장(중복되는 데이터가 존재할 수 없다.) 자동으로 index가 생성된다.

-> 중복된 값을 저장할 수 없도록함

 

-- 제약조건 확인하기--1

 

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE 테이블명;

-- 제약조건 추가하기--2 

 

PRIMARY KEY	ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY(NAME)
FOREIGN KEY	ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼명) REFERENCES PK테이블명(PK컬럼명) (ON DELETE CASCADE / ON UPDATE CASCADE)
UNIQUE	ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 UNIQUE(NAME)
DEFAULT	ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입(크기) DEFAULT ‘값’;

 

1) 현재 emp(사원)테이블에는 제약조건이 주어져 있는지 확인하고 emp 테이블에 UNIQUE로 제약조건 설정하기

 

> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME IN('emp');

 

-> 현재 EMP에는 제약조건이 주어져 있지 않다.

 

> ALTER TABLE emp ADD CONSTRAINT emp_uq_name UNIQUE(ename);

 

 

->UNIQUE로 ename 필드명에 이름이 중복으로 들어오는 것을 막는다.

-> emp_uq_name 제약조건명은 알아서 제약조건과 맞게 생성한다.

 

2) 제약조건이 잘 작동되는지 확인해보기

 

> INSERT INTO emp(empno, ename) VALUES(8888, ‘smith’);  -- 이미 ‘smith’가 있기 때문에 중복 데이터 허용 불가

> INSERT INTO emp(empno, ename) VALUES(8888, ‘smith2’) – smith2는 들어갈 수 있다.

 

 

-- 제약조건 삭제하기--3

 

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

 

예제) emp테이블의 emp_uq_ename 제약조건을 삭제한다. 

> ALTER TABLE emp DROP CONSTRAINT emp_uq_ename;

---

3.  CHECK 

- 컬럼의 값을 어떤 특정 범위로 제한할 수 있다.

 

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 CHECK 범위;

 

예제1) emp 테이블에 emp_ck_sal 제약조건명을 설정하고 범위는 급여의 범위는 500초과 8000 미만)

 

> ALTER TABLE emp ADD CONSTRAINT emp_ck_sal CHECK(sal>500 AND sal<8000);

-> 들어올 수 있는 급여의 범위를 500부터 8000까지로 설정했다.

> UPDATE emp SET sal = 8000 WHERE ename = ‘smith’

-> 이름이 smith인 급여를 8000으로 수정하겠다! (오류남 -> 범위 초과되어서)

> UPDATE emp SET sal = 7999 WHERE ename = ‘smith’'

 

 

 

예제2) emp 테이블에 emp_ck_comm 제약조건명을 설정하고, 보너스(comm)의 범위는 100, 200, 300, 400으로 제한한다.

 

> ALTER TABLE emp ADD CONSTRAINT emp_ck_comm CHECK (comm IN(100,200,300,400));

> UPDATE emp SET comm = 200 WHERE sal > 3000; -- 성공(범위 안이어서 성공)

 

 

> UPDATE emp SET comm = 500 WHERE sal < 2000; -- 실패(범위가 넘어가서)

 

---

4.  DEFAULT

- 컬럼 기본값 지정: 데이터 입력시에 입력을 하지 않아도 지정된 값이 입력될 수 있다.

 

> CREATE TABLE EMP(

           HIREDATE DATE DEFAULT NOW()

);

-> HIREDATE 컬럼에 INSERT를 하지 않아도 오늘 날짜가 DEFAULT 값으로 지정됨

---

5.  FOREIGN KEY 지정(외래키 설정)

- 기본 키를 참조하는 컬럼 또는 컬럼들의 집합

 

※ 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키(PRIMARY KEY)의 컬럼과 데이터형이 일치해야 한다. 이를 어기면 참조무결성 제약에 의해 테이블을 생성할 수 없다.

 

EMP 테이블이 DEPTNO 테이블을 참조하게 하기 위해서 (외래키 설정하기 위해서) DEPTNO는 유일한 값이어야 함(그래서 PRIMARY KEY로 세팅해야 함)

 

1) dept 테이블의 deptno 필드를 PRIMARY KEY로 설정하기

 

> ALTER TABLE dept ADD CONSTRATINT dept_pk_deptno PRIMARY KEY (deptno);

 

 

2) PRIMARY KEY가 작동하는지 검사하기

 

> INSERT INTO dept VALUES(40, ‘총무부’, ‘서울’); -- 오류! 40번 값 이미 있음(PRIMARY KEY 제약조건)

 

> INSERT INTO dept VALUES(50, ‘총부무’, ‘서울’); -- 성공!

 

3)  emp테이블의 deptno는 dept테이블의 deptno를 참조하는 외래키(FOREIGN KEY 생성)

 

> ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno);

 

4) FOREINGN KEY가 작동되는지 검사하기

 

예제)  emp테이블에서 MARTIM의 부서이동

> UPDATE emp SET deptno = 50 WHERE ename = ‘MARTIN’; -- 성공!

 

 

> UPDATE emp SET deptno = 35 WHERE ename  = ‘MARTIN’ –- 실패!

 

 

->  외래키가 설정되어 있기 때문에 dept에 존재하는 deptno 중 하나로만 설정 가능

 

만약 삭제하고 싶으면?

> ALTER TABLE emp DROP CONSTRAINT emp_fk_deptno;

 

5) ON DELETE CASCADE

 

※ 외래키에 의해 참조되고 있는 기본키는 삭제할 수 없다.  (여기서는 deptno)

※ ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제될 때 같이 삭제된다.

-> (dept 테이블에서 dept의 데이터가 사라지게 되면 emp에도 영향을 미치게 된다)

 

ON DELETE CASCADE: 다른 테이블의 기존 행에 있는 외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제되도록 지정

 

-> 예를 들어서 emp에 외래키가 있는 상태에서 dept테이블의 20번 레코드가 사라지게 되면 emp 테이블에서 dept의 20번을 참조하고 있는 행들도 같이 사라지게 하는 것이다.

 

<기본 구조>

 

> ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY (컬럼명)  REFERENCES PK테이블명(PK컬럼명) ON DELETE CASCADE

 

> DELETE FROM dept WHERE deptno = 20;

->외래키 제약조건에 CASCADE를 하면 참조되고 있는 emp 테이블의 행들도 같이 사라짐

 

> ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno FOREIGN KEY (deptno) REFERENES dept (deptno) ON DELETE CASCADE;

 

지금까지 생성해본 제약조건들

 

 

* 외래키 삭제하기 *

> ALTER TABLE emp DROP CONSTRAINT emp_fk_deptno;