Database 기본7 (제약조건)

----------------- 2021-03-10
-- 제약 조건
-- NOT NULL : 지정한 열에 NULL을 허용하지 않음. NULL 제외한 데이터의 중복 허용. (C)
-- UNIQUE : 지정한 열이 유일한 값을 가져야 함(중복 비허용). 오라클에선 NULL은 중복에서 제외된다. (U)
-- PRIMARY KEY : 지정한 열이 유일한 값이면서 NULL 비허용. 테이블에 하나만 지정 가능. (P)
-- FOREIGN KEY : 다른 테이블의 열을 참조해서 존재하는 값만 입력 가능. 참조할 열에는 중복값이 없어야 함. (R)
-- CHECK : 설정한 조건식을 만족하는 데이터만 입력 가능. (C)
 
-- 테이블에 설정된 제약 조건 확인
SELECT *
FROM USER_CONSTRAINTS;
SELECT*FROM USER_CONS_COLUMNS;
-- 테이블에 대한 제약 조건을 검색할 땐 2개 테이블을 조인해서 사용
SELECT*
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS USING(CONSTRAINT_NAME);
 
-- NOT NULL
SELECT * FROM MEMBER;
CREATE TABLE CONS_NN(
    USER_NO NUMBER,
    USER_NAME VARCHAR2(20),
    EROLL_DATE DATE
); -- 일반적인 테이블 생성
DROP TABLE CONS_NN;
 
CREATE TABLE CONS_NN(
    USER_NO NUMBER NOT NULL,
    USER_NAME VARCHAR2(20) NOT NULL,
    EROLL_DATE DATE
); -- NOT NULL 설정
 
INSERT INTO CONS_NN VALUES (NULL, NULL, NULL); -- 삽입 불가
 
-- UNIQUE
CREATE TABLE TBL_USER_UQ(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE ,
    USER_PWD VARCHAR2(20),
    USER_NAME VARCHAR2(20),
    EMAIL VARCHAR2(20) UNIQUE
);
--유니크 그룹화
CREATE TABLE TBL_USER_UQ(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) NOT NULL UNIQUE,
    USER_PWD VARCHAR2(20),
    USER_NAME VARCHAR2(20),
    EMAIL VARCHAR2(20),
    UNIQUE (USER_ID, EMAIL) -- 그룹화.. 윗 테이블의 유니크랑 다름.
);
 
DROP TABLE TBL_USER_UQ;
SELECT*FROM TBL_USER_UQ;
INSERT INTO TBL_USER_UQ VALUES (1, 'GOM', '1234', 'JANG', 'JANG@NAVER.COM'); -- 중복 삽입 불가
 
-- PRIMARY KEY : 중복값 없고 NULL도 없어야 함. 한 테이블당 한 PK만 가능.
CREATE TABLE TBL_USER_PK(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) NOT NULL UNIQUE,
    USER_PWD VARCHAR2(20) NOT NULL
);
 
-- 상품, 날짜, 수량, 아이디, 주문번호
CREATE TABLE TBL_ORDER(
    PRODUCT_NO NUMBER,
    USER_ID VARCHAR2(20),
    ORDER_DATE DATE,
    COUNT NUMBER,
    ORDER_NO NUMBER,
    PRIMARY KEY (PRODUCT_NO, USER_ID, ORDER_DATE)
);
DROP TABLE TBL_ORDER;
INSERT INTO TBL_ORDER VALUES (2, 'ADMIN', '21/03/10',10,1);
SELECT*FROM TBL_ORDER;
 
-- 외래키 (FOREIGN KEY) : 다른 테이블의 열을 참조해서 존재하는 값만 입력 가능. 참조할 열에는 중복값이 없어야 함. (R)
-- 카테고리, 부서명 , 학과명
-- 부모자식 관계가 성립
 
-- 게시판
CREATE TABLE BOARD (
    BOARD_NO NUMBER,
    BOARD_TITLE VARCHAR2(20) NOT NULL,
    BOARD_CONTENT VARCHAR2(2000),
    BOARD_WRITE VARCHAR2(20) REFERENCES MEMBER(USER_ID), -- 밑 테이블 MEMBER가 글을 쓰는 것이니, FOREIGN 사용
    BOARD_DATE DATE
);
DROP TABLE BOARD;
DROP TABLE MEMBER;
CREATE TABLE MEMBER(
    USER_ID VARCHAR2(20) NOT NULL UNIQUE ,
    USER_PWD VARCHAR2(20)
);
 
INSERT INTO MEMBER VALUES ('USER01','USER01');
SELECT*FROM MEMBER;
INSERT INTO BOARD VALUES (1, '무야호', NULL,'USER01', SYSDATE);
SELECT*FROM BOARD;
 
CREATE TABLE SHOP_BUY(
    BUY_NO NUMBER PRIMARY KEY ,
    USER_ID VARCHAR2(20) NOT NULL,
    PRODUCT_NAME VARCHAR2(50),
    REG_DATE DATE,
    FOREIGN KEY (USER_ID) REFERENCES MEMBER2(USER_ID) ON DELETE SET NULL
);
 
SELECT * FROM SHOP_BUY;
INSERT INTO SHOP_BUY VALUES (1, 'USER01', 'AIR CONDITIONAL', SYSDATE);
INSERT INTO SHOP_BUY VALUES (3, 'USER02', 'AIR CONDITIONAL', SYSDATE); -- USER02는 MEMBER 테이블에 없으므로 ERROR
 
CREATE TABLE MEMBER2(
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(20)
);
 
SELECT * FROM MEMBER2;
DROP TABLE SHOP_BUY;
DROP TABLE MEMBER2;
 
-- 참조된 값은 함부로 데이터를 수정하지 못하게 만듦
SELECT * FROM BOARD;
DELETE FROM MEMBER2 WHERE USER_ID='USER01'; -- 참조중인 테이블이 있어서 실행안됨
DELETE FROM BOARD WHERE BOARD_WRITE='USER01'; -- BOARD_WRITE에서 참조하고 있어서 이걸 실행하면 위 MEMBER에서 지울 수 있다
 
-- ㄴ 잠조관계 있을 땐, 못지우는게 DEFAULT지만 옵션 설정시 가능.
-- ON DELETE SET NULL / ON CASCADE
 
INSERT INTO MEMBER2 VALUES('USER01', 'USER01')
INSERT INTO SHOP_BUY VALUES(1, 'USER01', '아이스크림', SYSDATE);
 
-- CHECK : 내가 설정한 값만 저장가능
CREATE TABLE USER_CHECK(
    USER_NO NUMBER,
    USER_NAME VARCHAR2(20),
    GENDER VARCHAR2(10) CHECK(GENDER IN('남','여')),
    AGE NUMBER,
    CHECK(AGE BETWEEN 10 AND 30)
);
INSERT INTO USER_CHECK VALUES (1,'곰저씨','남',20); -- 가능
SELECT*FROM USER_CHECK;
DROP TABLE USER_CHECK;
 
CREATE TABLE CONS_NAME(
    USER_ID VARCHAR2(20) CONSTRAINT CONS_NAME_PK PRIMARY KEY,
    USER_NAME VARCHAR2(20),
    CONSTRAINT USER_NAME_UQ UNIQUE (USER_NAME)
);
 
SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'CONS_NAME';