제약조건 (constraints)

<제약조건 (constraints)>

 

• 제약조건: 테이블 단위에서 데이터의 무결성을 보장해주는 규칙. 테이블에 데이터가 입력, 수정, 삭제되거나 테이블이 삭제, 변경될 경우 잘못된 트랜잭션이 수행되지 않도록 결함을 유발할 가능성이 있는 작업을 방지하는 역할을 담당.

 

• 무결성: 정보가 원 그대로 변형되지 않고 전달되는 성질. 원래의 정보 또는 신호가 전송/저장/변환 중에 또는 그 후에도 동일함을 유지하는 것

 

• 오라클에서 제공되는 제약조건

• NOT NULL: 열이 NULL값을 포함할 수 없음

• UNIQUE KEY (UK)-고유키: 테이블의 모든 행에서 고유한 값을 갖는 열 또는 열조합을 지정

• PRIMARY KEY (PK)-기본키: 해당 열은 반드시 존재하고 유일해야 함. NOT NULL, UK제약 조건 결합한 형태

• FOREIGN KEY (FK)-외래키: 한 열과 참조된 테이블 열 간의 외래 키 관계를 설정

• CHECK: 해당 열에 저장 가능한 데이터 값의 범위나 조건 지정

 

• 제약조건 문법

CREATE TABLE 테이블이름(
컬럼 데이터타입 [DEFAULT default값] [컬럼 레벨 제약조건], --위치가 열 옆
...
[테이블 레벨 제약조건], --혹은 위치가 아래쪽에
...
);

---

 

• 제약조건을 걸고 emp 테이블 만들기 (열 이름 옆에)

CREATE TABLE emp (
    eno     NUMBER(3) CONSTRAINT emp_eno_pk PRIMARY KEY,
    emp_name VARCHAR2(20)
);

 

---

DESC emp;

emp 테이블 Describe하기: eno는 NOT NULL이므로 반드시 값을 입력해야함

 

 

---

emp 테이블에 데이터 입력하기

INSERT INTO emp
VALUES (1, '김');
INSERT INTO emp
VALUES (2, '박');
INSERT INTO emp
VALUES (2, '양');

 

 

emp 테이블의 모든 데이터 출력

SELECT * FROM emp;

 

---

emp 테이블 삭제하기

DROP TABLE emp;

 

---

 

 

• 제약조건을 걸고 emp 테이블 만들기 (열의 데이터 타입을 정한 뒤)

CREATE TABLE emp (
    eno     NUMBER(3),
    emp_name VARCHAR2(20),
    CONSTRAINT emp_eno_pk PRIMARY KEY (eno)
--어떤 열의 제약조건인지 괄호 안에 적어줌
);

 

 

---

 

• 제약조건의 이름이 없는 경우

CREATE TABLE emp1 (
    eno     NUMBER(3) PRIMARY KEY,
    emp_name VARCHAR2(20)
);
 
INSERT INTO emp1
VALUES (1,'김');
INSERT INTO emp1
VALUES (1,'박');
--컴퓨터에서 자동으로 제약조건을 만들어줌

 

---

• NOT NULL / UK 유니크

DROP TABLE emp1;
 
CREATE TABLE emp1 (
    eno         NUMBER(3),
    emp_name    VARCHAR2(20)
    CONSTRAINT emp1_ename_nn NOT NULL,
    email       VARCHAR2(30)
    CONSTRAINT empl_email_uk UNIQUE
);
 
INSERT INTO emp1
VALUES (1, NULL, 'hong@naver.com');
--emp_name은 NOT NULL이기 때문에 NULL값을 넣을 수 없음
 

 

 

 

• UK 유니크는 동일한 값이 입력될 수 없음

INSERT INTO emp1
VALUES (1, '홍길동', 'hong@naver.com');
INSERT INTO emp1
VALUES (2, '김유신', 'hong@naver.com');

 

 

---

 

• CHECK 제약조건

CREATE TABLE emp2 (
    eno         NUMBER (3),
    emp_name    VARCHAR2(20)
    CONSTRAINT emp2_ename_nn NOT NULL,
    sal      VARCHAR2(10)
    CONSTRAINT emp2_sal_check CHECK (sal > 1000)
);
 
INSERT INTO emp2
VALUES(1, '홍길동', 999);

 

---

 

• emp 테이블 삭제 후 다시 emp 테이블을 만들고 제약조건을 걸기

DROP TABLE emp;
CREATE TABLE emp (
    eno NUMBER (4) PRIMARY KEY, 
--제약조건 이름 생략
    ename VARCHAR2(20) NOT NULL, 
--데이터가 NULL이면 안됨
    gno VARCHAR2(13) UNIQUE CHECK (LENGTH(gno)>=8), 
--8자리 이상 되어야 함
    gender VARCHAR2(5) CHECK (gender IN ('woman', 'man')) 
--woman혹은 man중 하나의 값이어야함
);
 

INSERT INTO emp VALUES (1, '김', '12345678', 'man');
INSERT INTO emp VALUES (2, '강', '123456789', 'woman');
INSERT INTO emp VALUES (3, '강', '1234567', 'woman');

 

 

gender 값은 man혹은 woman 둘 중 하나여야 하기 때문에 오류 발생

INSERT INTO emp VALUES (4, '양', '123456789', 'human');

 

 

---

 

<제약조건 예제>

 

• members라는 새 테이블을 만들기 (제약조건 이름은 생략가능)

CREATE TABLE members (
    member_id    NUMBER (2)     PRIMARY KEY,
    first_name   VARCHAR2 (50)  NOT NULL,
    last_name    VARCHAR2 (50)  NOT NULL,
    gender       VARCHAR2 (5)   CHECK (gender IN('Man','Woman')),
    birth_day    DATE           DEFAULT sysdate,
    email        VARCHAR2 (200) UNIQUE NOT NULL
);
DESC members;

 

 

---

 

• 외래키 (FK) 관련 제약조건

• dept 부서테이블을 생성 (기본키만 가지고 있음)

CREATE TABLE dept (
    dno     NUMBER (4),
    dname   VARCHAR2 (20),
    CONSTRAINT dept_dno_pk PRIMARY KEY(dno) --기본키
);
DROP TABLE emp;

---

 

• dept 테이블을 참조하는 emp 테이블 생성하고 dept 테이블의 dno를 참조

CREATE TABLE emp (
    eno         NUMBER (4),
    emp_name    VARCHAR2 (20),
    sal         NUMBER (10),
    dno         NUMBER (4),
    CONSTRAINT emp_eno_pk PRIMARY KEY(eno),
    CONSTRAINT emp_dno_fk FOREIGN KEY(dno)
        REFERENCES dept(dno)
);

---

 

 

• dept 테이블에 데이터 입력

INSERT INTO dept VALUES (10, 'TEST1');
INSERT INTO dept VALUES (20, 'TEST2');
INSERT INTO dept VALUES (30, 'TEST3');
INSERT INTO dept VALUES (40, 'TEST4');
INSERT INTO dept VALUES (50, 'TEST5');
SELECT * FROM dept;

---

 

 

• emp 테이블에 데이터 입력

INSERT INTO emp VALUES (1010, 'Kim', 200, 10);
INSERT INTO emp VALUES (1020, 'Lee', 180, 20);
INSERT INTO emp VALUES (1030, 'Jung', 220, 30);
INSERT INTO emp VALUES (1040, 'Oh', 250, 40);
INSERT INTO emp VALUES (1050, 'King', 300, 50);
SELECT * FROM emp;

• emp에서 참조하고 있는 dept 테이블의 dno은 현재 dno가 10, 20, 30, 40, 50. 60이 없기에 입력 불가능

--외래키는 참조열의 값 이외에는 입력불가
INSERT INTO emp VALUES (1060, 'Kang', 500, 60);

 

 

INSERT INTO emp VALUES (1060, 'Kang', 500, NULL);

 

 

 

• emp 테이블이 참조하고 있기 때문에 삭제시 에러발생

DELETE FROM dept
WHERE dno=20;

 

 

---

 

• ON DELETE CASCADE: 삭제 시 참조하는 열 값도 삭제

 

DROP TABLE emp; 
 
CREATE TABLE emp (
    eno         NUMBER (4),
    emp_name    VARCHAR2 (20),
    sal         NUMBER (10),
    dno         NUMBER (4),
    CONSTRAINT emp_eno_pk PRIMARY KEY(eno),
    CONSTRAINT emp_dno_fk FOREIGN KEY(dno)
        REFERENCES dept(dno) ON DELETE CASCADE
);
 
INSERT INTO emp VALUES (1010, 'Kim', 200, 10);
INSERT INTO emp VALUES (1020, 'Lee', 180, 20);
INSERT INTO emp VALUES (1030, 'Jung', 220, 30);
INSERT INTO emp VALUES (1040, 'Oh', 250, 40);
INSERT INTO emp VALUES (1050, 'King', 300, 50);
 
DELETE FROM dept
WHERE dno=20;

 

 

---

 

 

• ON DELETE SET NULL: 참조행 삭제시 자동 널값

 

DROP TABLE emp; 
 
CREATE TABLE emp (
    eno         NUMBER (4),
    emp_name    VARCHAR2 (20),
    sal         NUMBER (10),
    dno         NUMBER (4),
    CONSTRAINT emp_eno_pk PRIMARY KEY(eno),
    CONSTRAINT emp_dno_fk FOREIGN KEY(dno)
        REFERENCES dept(dno) ON DELETE SET NULL
);
 
INSERT INTO emp VALUES (1010, 'Kim', 200, 10);
INSERT INTO emp VALUES (1020, 'Lee', 180, 20);
INSERT INTO emp VALUES (1030, 'Jung', 220, 30);
INSERT INTO emp VALUES (1040, 'Oh', 250, 40);
INSERT INTO emp VALUES (1050, 'King', 300, 50);
 
DELETE FROM dept
WHERE dno=30;