0505 | ORACLE (7) // view, data dictionary, 도메인제약조건, 기본키, 유니크키, 시퀀스설정
[1] VIEW의 의미와 생성방법
VIEW = 일종의 가상테이블
: 물리적인 데이터구조 (TABLE)과 개념적인 데이터구조(VIEW)의 차이 - 무결성 문제로 인해 데이터가 나눠져있음 ▶ 조인으로 합쳐서 표현한 테이블 = 우리가 원하는 테이블 ▶ 쿼리 문장을 VIEW로 정의해서 사용함으로써 간단하게 쿼리할 수 있다. (조인처리된 뷰가 테이블처럼 하나의 단위로 사용)
CREATE VIEW NOTICE_VIEW
AS
SELECT N.ID, N.TITLE, N.WRITER_ID, M.NAME WRITER_NAME, COUNT(C.ID) COMMENT_CNT FROM
MEMBER M
RIGHT OUTER JOIN NOTICE N ON M.ID = N.WRITER_ID
LEFT OUTER JOIN "COMMENT" C ON N.ID = C.NOTICE_ID
GROUP BY N.ID, N.TITLE, N.WRITER_ID, M.NAME;
[2] 데이터 딕셔너리
: 오라클 dbms는 사용자 데이터나 권한, 테이블 등을 저장하는 데이터베이스가 있어야 한다.
: 현재는 디벨로퍼 좌측에 딕셔너리 내용이 도식화 되어있으니 예전처럼 많이 쓸 일은 없다
: 사용자에게는 뷰만 제공하여 제한된 정보를 제공하지 않음
SELECT * FROM DICT;
DBA_ DBA_TABLES...
ALL_ ALL_TABLES...
USER_ USER_TAB_COLUMNS.. 의 형태를 띈다
[3] 도메인 제약조건
: 데이터를 입력시 제한을 거는 것. 유효하지 않은 형태의 데이터 입력을 막기 위함
예) 바코드 아이디 등 식별할 방법이 없는 아이디는 생성하지 못하도록 해야 함.
도메인 > 엔티티 > 릴레이션
3-1) 도메인이란?
: 유효한 값의 범위
예) 학번 - 0보다 큰 정수 / 핸드폰번호 - 010으로 시작 / 이름 - 20자 내의 문자 등 컬럼의 값 제한
✔ 도메인 제약조건
: 속성에 도메인이 아닌 값이 올 수 없도록 하는 제약조건
NOT NULL - 사용자가 무조건 입력하게 하는 것
DEFAULT - NOT NULL 항목이지만 사용자 입력이 아닌, 알아서 값을 가져야 하는 기본값 (날짜, 조회수, 등록일...)
CHECK - 도메인의 범위
3-2) NOT NULL 제약조건
: 테이블 생성할 때 적용 방법
CREATE TABLE TEST(
ID VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(200) NOT NULL,
PHONE CHAR(13) NOT NULL -- 컬럼 생성시 뒤에 NOT NULL을 붙여서 설정
)
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) NOT NULL; -- ALTER을 통해서 NOT NULL로 수정가능
3-3) 디폴트 제약조건
: 값을 넣지 않으면 알아서 입력되는 기본값
CREATE TABLE TEST
(
ID VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(200) NOT NULL,
PHONE CHAR(13) NOT NULL -- 컬럼 생성시 뒤에 NOT NULL을 붙여서 설정
PWD VHARCHAR2(200) DEFAULT '1234' -- 기본설정됨
)
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) DEFAULT '111';
INSERT INTO TEST ('ID','PHONE') VALUES ('NEWLEC','222);
3-4) 체크 제약조건
: 위반 사항을 제어할 수 있는 제약조건
CREATE TABLE TEST
(
ID VARCHAR2(50) NOT NULL,
PHONE CHAR(13) CHECK (PHONE LIKE '010-%-____') NOT NULL,
)
ALTER TABLE TEST ADD CONSTRAINT CK_TEST_PHONE CHECK(PHONE LIKE '010-%-____'); -- CK_TEST_PHONE 은 체크제약조건의 이름
-- 오류시 에러메세지 - ORA-02290: 체크 제약조건(NEWLEC.MEMBER_PHONE_CHK1)이 위배되었습니다
✔ 정규식을 이용한 체크제약조건
: '010-____-____'으로 제약조건을 건 경우, 010-aaaa-aaaa로 입력해도 입력이 가능하다. 오라클에선 이 제약이 가장 최대치이므로 정밀한 제약을 위해 정규식을 사용.
ALTER TABLE MEMBER
DROP CONSTRAINT MEMBER_PHONE_CHK1; -- 기존 제약조건 삭제
SELECT * FROM user_constraints
WHERE TABLE_NAME ='MEMBER'; -- 멤버테이블의 제약조건 조회
ALTER TABLE MEMBER
ADD CONSTRAINT MEMBER_PHONE_CHK1 CHECK(REGEXP_LIKE(PHONE, '^01[01]-\d{3,4}-\d{4}$')); -- 제약조건 추가
3-5) Entity 제약조건
: 같은 데이터가 들어가도 다른 사람일 수 있다. 중복과 각 개체를 식별할 수 있는 컬럼이 필요
✔ Primary key (기본키)
: 해당 테이블의 식별자 역할. 따라서 테이블에 하나만 지정 가능. (예. 사람을 식별할때 이름이 아닌 주민번호 사용) 중복성이 없는 유일한 성질이자 사용자가 선택한 것 = 기본키이자 식별키. 유니크 키의 성질을 포함
: 기본키를 중복으로 넣는 경우 에러 > 9 행: ORA-00001: 무결성 제약 조건(NEWLEC.NOTICE_PK)에 위배됩니다
✔ Unique (중복불허- 동일한 데이터 입력불가)
: 데이터가 중복되지 않도록 하나의 테이블에 각 컬럼마다 지정 가능. (예. 하나의 테이블에서 메일주소, 핸드폰번호를 중복되지 못하게 지정. 하지만 사람 식별은 기본키인 주민번호로 함!)
CREATE TABLE TEST
(
ID VARCHAR2(50) PRIMARY KEY, -- 기본키 제약조건. CONSTRACINT NOTICE_ID PK PRIMARY KEY로 이름 지정가능
EMAIL VARCHAR2(200) NOT NULL,
PHONE CHAR(13) NOT NULL UNIQUE, -- NOT NULL / UNIQUE
PWD VHARCHAR2(200) DEFAULT '1234' -- 기본값 1234
)
------------------
CONSTRAINT NOTIE_ID_PK PRIMARY KEY(ID)
CONSTRAINT NOTICE_ID_UK UNIQUE(PHONE) -- 가독성을 위해 아래에 따로 적어주기도 한다
[4] 시퀀스 Sequence
: 일련번호
CREATE SEQUENCE NOTICE_ID_SEQ INCREMENT BY 1 START WITH 1; -- 시퀀스 생성시 DDL 탭에 들어가면 쿼리문이 생성되어있다
✔ 시퀀스의 다음값을 불러오는 SEQ(시퀀스이름).NEXTVAL
INSERT INTO NOTICE (ID, TITLE, WRITER_ID)
VALUES(NOTICE_ID_SEQ.NEXTVAL, '시퀀스예제', 'orange');
-- ID값을 생성해둔 시퀀스를 이용하여, 현재 시퀀스의 NEXT값을 불러올 수 있다. 현재 데이터값이 어디인지 확인하지 않고도 입력가능
👉 만약, 일일히 불러오는게 싫을 경우, 해당 컬럼값 아래에서 열시퀀스로 생성해둔 시퀀스를 연결할 수 있다.
그러면 데이터 입력시 ID 컬럼을 빼고도 (ID가 기본키라 할지라도 제외 가능) 데이터를 입력할 수 있다.
👉 INSERT INTO NOTICE (TITLE, WRITER_ID) VALUES('시퀀스예제2', 'apple');
