TABLE -2 (무결성 CONSTRAINT, primary key, unique key, foreign key ...etc)

Primary Key : 기본키. NULL을 허용하지 않는다. 중복을 허용하지 않는다.    id, 주민번호 

Unique Key  : 고유키. NULL은 허용한다. 중복을 허용하지 않는다.          E-mail  

Foreign Key : 외래키. 테이블과 테이블을 연결하는 목적의 성질이다. Join이 목적.

NULL은 허용 외래키로 설정된 컬럼은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.      

 

CHECK: 범위를 설정. 지정된 값외에 사용할 수 없다. NULL은 허용

 

NOT NULL    : NULL을 허용하지 않는다.

-- NOT NULL
CREATE TABLE TB_TEST01(
    COL1 VARCHAR2(10) NOT NULL,
    COL2 VARCHAR2(20)
);
 
INSERT INTO tb_test01(COL1, COL2)
VALUES('AAA', '111');
 
INSERT INTO tb_test01(COL1, COL2)
VALUES('BBB', '');  -- "" != NULL
 
SELECT * FROM tb_test01;
 
INSERT INTO tb_test01(COL1, COL2)
VALUES('', '222');
 
INSERT INTO tb_test01(COL1)
VALUES('CCC');
 
-- Primary Key = Unique + NOT NULL
DROP TABLE tb_test01
CASCADE CONSTRAINTS;
 
CREATE TABLE TB_TEST01(
    PKCOL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);
 
INSERT INTO tb_test01(PKCOL, COL1, COL2)
VALUES('AAA', 'aaa', '111');
 
INSERT INTO tb_test01(PKCOL, COL1, COL2)
VALUES('BBB', '', '');
 
INSERT INTO tb_test01(PKCOL, COL1, COL2)
VALUES('', 'bbb', '222');
 
SELECT * FROM tb_test01;
 
DROP TABLE tb_test01
CASCADE CONSTRAINTS;
 
CREATE TABLE TB_TEST01(
    PKCOL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    COL1 VARCHAR2(20) CONSTRAINT PK_TEST_02 PRIMARY KEY,    -- table can have only one primary key
    COL2 VARCHAR2(20)
);
 
CREATE TABLE TB_TEST01(
    PKCOL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    CONSTRAINT PK_TEST_01 PRIMARY KEY(PKCOL, COL1)
);
 
DROP TABLE tb_test01
CASCADE CONSTRAINTS;
 
CREATE TABLE TB_TEST01(
    PKCOL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);
 
-- ALTER -> Primary Key 추가
 
-- Unique : 고유키. 중복된 값은 입력불가. NULL허용
CREATE TABLE TB_TEST01(
    UKCOL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(30)
);
 
INSERT INTO tb_test01(UKCOL, COL1, COL2)
VALUES('AAA', 'aaa', '111');
 
INSERT INTO tb_test01(UKCOL, COL1, COL2)
VALUES('', 'bbb', '222');
 
-- unique key를 삭제해야 할 경우
ALTER TABLE tb_test01
DROP CONSTRAINT UK_TEST_01;
 
-- FOREIGN KEY : 외래키
--               기본 테이블(departments)에서는 PK, UK로 설정되어 있어야 한다
--               NULL을 허용
 
-- PARANT TABLE 
CREATE TABLE TB_DEPT(
    DEPARTMENT_ID VARCHAR2(10),
    DEPARTMENT_NAME VARCHAR2(20),
    LOCATION_ID NUMBER,
    CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)
);
 
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
CREATE TABLE TB_EMP(
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(20),
    DEPARTMENT_ID VARCHAR2(10),
    CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID) 
    REFERENCES TB_DEPT(DEPARTMENT_ID)
);
 
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(100, '홍길동', 10);
 
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(110, '성춘향', 20);
 
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(120, '일지매', 30);
 
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES(130, '정수동', '');
 
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('바나나', 0);