[SQL] Constraint

Constraint : 무결성

Column을 지정하는 성질, 설정

 

* 제약조건

- Primary key : 기본키. NULL을 허용하지 않는다. 중복을 허용하지 않는다. (ID, 주민번호)

　　　　　　　(기본키는 UNIQUE + NOT NULL 속성)
- Unique key : 고유키. NULL을 허용한다. 중복을 허용하지 않는다. (E-mail)
- Foreign key : 외래키. Join(테이블과 테이블의 연결)이 목적. NULL을 허용.
                    외래키로 지정된 컬럼은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.
- CHECK : 범위를 지정. 지정된 값외에 사용할 수 없다. NULL을 허용.
- NOT NULL : NULL을 허용하지 않는다. 중복을 허용  

 

 

-- NOT NULL

CREATE TABLE TB_TEST06(
    COL1 VARCHAR2(10) NOT NULL,
    COL2 VARCHAR2(20)
);

INSERT INTO tb_test06(COL1, COL2)
VALUES('AAA', '111');

INSERT INTO tb_test06(COL1)
VALUES('BBB');

INSERT INTO tb_test06(COL2)
VALUES('222');

 

-- 무결성 제약조건까지 모두 삭제

DROP TABLE tb_test06
CASCADE CONSTRAINTS;

 

-- Primary key = Unique + NOT NULL

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

INSERT INTO TB_TEST06(PK_COL, COL1, COL2)
VALUES('AAA', 'aaa', '111');

INSERT INTO TB_TEST06(PK_COL)
VALUES('BBB');

INSERT INTO TB_TEST06(PK_COL)
VALUES('bbb');

INSERT INTO TB_TEST06(PK_COL, COL1, COL2)
VALUES('', 'aaa', '111');

DROP TABLE tb_test06
CASCADE CONSTRAINTS;

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20) CONSTRAINT PK_TEST_02 PRIMARY KEY,
    COL2 VARCHAR2(20)
);

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    CONSTRAINT PK_TEST_01 PRIMARY KEY(PK_COL, COL1)
);

CREATE TABLE TB_TEST06(
    PK_COL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

ALTER TABLE TB_TEST06
ADD CONSTRAINT PK_TEST_01
PRIMARY KEY(PK_COL);

INSERT INTO tb_test06(PK_COL, COL1, COL2)
VALUES('AAA', 'aaa', '111');

ALTER TABLE TB_TEST06
DROP CONSTRAINT PK_TEST_01;

DROP TABLE tb_test06
CASCADE CONSTRAINTS;

 

-- Unique : 고유키. 중복된 값은 입력불가. NULL허용

CREATE TABLE TB_TEST06(
    UK_COL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);

INSERT INTO tb_test06(UK_COL, COL1, COL2)
VALUES('AAA', 'aaa', '111');

INSERT INTO tb_test06(UK_COL, COL1, COL2)
VALUES('', 'aaa', '111');

INSERT INTO tb_test06(COL1, COL2)
VALUES('aaa', '111');

 

-- FOREIGN KEY : 외래키
　　　　 　　TABLE생성시 반드시 기본 테이블(departments)에서 PK, UK로 설정되어 있어야 한다.
　　　　 　　NULL 값을 허용

-- PARANT TABLE : departments

CREATE TABLE TB_DEPT(
    DEPARTMENT_ID VARCHAR2(10),
    DEPARTMENT_NAME VARCHAR2(20),
    LOCATION_ID NUMBER,
    CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)   -- PRIMARY KEY 지정
);

INSERT INTO tb_dept(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('10', '기획부', 120);

INSERT INTO tb_dept(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('20', '관리부', 150);

INSERT INTO tb_dept(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('30', '개발부', 180);



-- CHILD TABLE : employees

CREATE TABLE TB_EMP(
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(20),
    DEPARTMENT_ID VARCHAR2(10),
    CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID) -- FOREIGN KEY 지정
        REFERENCES TB_DEPT(DEPARTMENT_ID)   -- TB_DEPT TABLE의 PRIMARY KEY와 연결
);

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('100', '홍길동', '20');

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('101', '성춘향', '10');

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('102', '일지매', '30');

INSERT INTO tb_emp(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('103', '홍두께', '');

SELECT e.empno, e.ename, d.department_id, d.department_name
FROM tb_emp E, tb_dept D
WHERE e.department_id = d.department_id;

 

-- CHECK : 지정된 값만 을 넣을 수 있다. NULL을 허용, 중복 허용

CREATE TABLE TB_CHECK(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(20),
    CONSTRAINT CHK_01 CHECK( COL1 IN('사과', '배', '바나나') ),
    CONSTRAINT CHK_02 CHECK( COL2 > 0 AND COL2 <= 100 )
);

INSERT INTO tb_check(COL1, COL2)
VALUES('사과', 50);

INSERT INTO tb_check(COL1, COL2)
VALUES('귤', 50);

INSERT INTO tb_check(COL1, COL2)
VALUES('', 50);

INSERT INTO tb_check(COL1, COL2)
VALUES('', 101);