210526 oracle - CONSTRAINT, SEQUENCE
1. 제약조건에 대해 살펴 보고 컬럼 레벨과 테이블 레벨을 통해 구현할 수 있다.
2. 제약조건을 수정하고 삭제할 수 있다.
3. 제약조건의 정보를 확인할 수 있다.
============================================================================
제약조건
- NOT NULL : 해당 컬럼에 NULL을 포함하지 않도록 선언 (컬럼)
- UNIQUE : 해당 컬럼 또는 컬럼 조합 값이 유일하도록 함 (컬럼, 테이블)
- PRIMARY KEY : 식별 값, UNIQUE + NOT NULL (컬럼, 테이블)
- REFERENCES TABLE(COLUMN) : 해당 컬럼이 참조하고 있는 부모 테이블의 특정 컬럼 값들과
일치하거나 NULL이 되도록 보장한다. (컬럼, 테이블)
- CHECK : 해당 컬럼에 특정 조건을 만족시키도록 한다. (컬럼, 테이블)
============================================================================
제약 조건 확인 : DESC USER_CONSTRAINTS;
- CONSTRAINT_NAME : 제약조건 이름
- CONSTRAINT_TYPE : 유형
- P : PRIMARY KEY
- U : UNIQUE
- R : REFERENCE
- C : CHECK
- TABLE_NAME : 테이블 이름
- SEARCH_CONDITION : CHECK 제약조건 내용
- R_CONSTRAINT_NAME : 참조 테이블의 PRIMARY KEY 이름
- DELETE_RULE : 참조 테이블의 PRIMARY KEY 컬럼이 삭제될 때 사용되는 규칙
- NO ACTION, SET NULL, CASCADE 등
============================================================================
삭제 룰
- ON DELETE CASCADE : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 삭제
- ON DELETE SET NULL : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터는 NULL로 바꿈
- ON DELETE RESTRICTED : 삭제 대상 데이터를 참조하는 데이터가 존재하면 삭제 불가(DEFAULT)
수정 룰
- ON UPDATE CASCADE : 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 수정
============================================================================
[형식]
[테이블 생성]
CREATE TABLE 테이블명(
컬럼명1 데이터타입 [제약조건] ,,,,
);
[테이블 수정]
ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 [제약조건]
ADD CONSTRAINT 제약조건명 제약조건(컬럼명)
MODIFY 컬럼명 데이터타입
DROP COLUMN 컬럼명 [CASCADE CONSTAINTS]
DROP PRIMARY KEY [CASCADE] | UNION(컬럼명 ,,,, ) [CASCADE] | CONSTRAINT 제약조건명[CASCADE]
[이름 변경]
ALTER TABLE 기본 테이블명 RENAME TO 새 테이블명
RENAME 기존 테이블명 TO 새 테이블명
ALTER TABLE 테이블명 RENAME COLUMN 기본컬럼명 TO 새 컬럼명
ALTER TABLE 테이블명 RENAME CONSTRAINT 기존 제약조건명 TO 새 제약조건명
[테이블 복사]
- 서브 쿼리를 이용한 테이블 생성 및 행 (레코드) 복사
- 서브 쿼리를 이용해서 복사한 경우 NOT NULL을 제외한 제약조건은 복사 안됨
(NOT NULL 제약조건도 SYS_****으로 복사된다.)
- CREATE TABLE 테이블명[컬럼명 ,,,, ] AS 서브쿼리
- CREATE TABLE 테이블명1 AS SELECT * FROM 테이블명 WHERE 1=0;
============================================================================
FOREIGN KEY : 참조 테이블의 컬럼값과 일치하거나 NULL 상태를 유지하도록 해야한다.
컬럼레벨 생성 : LID는 MY_DEPT의 PRIMARY KEY 컬럼을 참조한다.
REFERENCES 참조 테이블 [(참조 컬럼)] : 생략가능한 이유는 어차피 PRIMARY KEY를 참조하기 때문
CREATE TABLE TABLE_FK(
ID CHAR(3),
SNAME VARCHAR2(20),
LID NUMBER(2) REFERENCES MY_DEPT (DEPTNO) -- REFERENCES 참조 테이블 [(참조 컬럼)]
);
테이블레벨 생성
CREATE TABLE TABLE_FK(
ID CHAR(3),
SNAME VARCHAR2(20),
LID NUMBER(2)
CONSTRAINT FFFF FOREIGN KEY(LID)
REFERENCES MY_DEPT (DEPTNO) -- REFERENCES 참조 테이블 [(참조 컬럼)]
);
DELETE OPTION
제약조건을 생성할 때, 참조 컬럼 값이 삭제 되는 경우 FOREIGN KEY 컬럼 값을
어떻게 처리할 것인지 지정 가능하다.
============================================================================
SEQUENCE 시퀀스 : 순차적으로 자동으로 정수 값을 생성하는 객체
CREATE SEQUENCE sequence_name
[ INCREMENT BY N ] [ START WITH N ]
[ {MAXVALUE N | NOMAXVALUE} ],[ MINVALUE | NOMINVALUE ]
[ {CYCLE | NOCYCLE} ] [ CACHE N | NOCACHE ]
NEXTVAL, CURRVAL
ex)
CREATE SEQUENCE MY_SEQ
START WITH 300
INCREMENT BY 5
MXVALUE 310
NOCYCLE
NOCACHE
SELECT MY_SEQ.NEXTVAL FROM DUAL;
CYCLE로 했을 때는 START WITH 값이랑 관계없이 1로 시작
============================================================================
예제 모음
Q1) EMP 테이블의 테이블 이름, 제약조건, 제약조건타입을 확인해보자
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP';
Q2) DEPT 테이블의 테이블 이름, 제약조건, 제약조건타입을 확인해보자
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT';
Q3) 테이블 생성
CREATE TABLE TEST(
ID NUMBER(5),
NAME CHAR(10),
ADDRESS VARCHAR2(50)
);
DESC TEST;
Q4) ID컬럼을 USR 컬럼으로 변경해보자
ALTER TABLE TEST
RENAME COLUMN ID TO USR;
Q5) TEST 테이블의 이름을 EXAM_TEST 테이블로 변경해보자
ALTER TABLE TEST
RENAME TO EXAM_TEST;
DESC EXAM_TEST;
Q6) EXAM_TEST 테이블을 삭제하고 휴지통 비우기
CREATE TABLE TEST
AS
SELECT * FROM EXAM_TEST; -- EXAM_TEST 복사해서 TEST 생성
CREATE TABLE TEST01
AS
SELECT * FROM EXAM_TEST; -- EXAM_TEST 복사해서 TEST01 생성
DROP TABLE EXAM_TEST; -- EXAM_TEST 삭제
SELECT * FROM RECYCLEBIN; -- 캐시메모리 버퍼, 휴지통에 EXAM_TEST 있는 것 확인
DESC RECYCLEBIN;
PURGE RECYCLEBIN; -- 휴지통 비우기
DROP TABLE TEST01 PURGE; -- 휴지통으로 가지않고 바로 삭제
Q7) 다양한 제약조건을 지정한 테이블을 만들어보자
CREATE TABLE USER1(
IDX NUMBER PRIMARY KEY,
ID VARCHAR2(10) UNIQUE,
NAME VARCHAR2(10) NOT NULL,
PHONE VARCHAR2(15),
ADDRESS VARCHAR2(50),
SCORE NUMBER(6, 2) CHECK (SCORE >= 0 AND SCORE <= 100),
SUBJECT_CODE NUMBER(5),
HIREDATE DATE DEFAULT SYSDATE,
MARRIAGE CHAR(1) DEFAULT 'N' CHECK(MARRIAGE IN ('Y', 'N'))
);
Q8) 제약조건 확인
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'USER1';
Q9) 값을 입력해서 제약조건 위배를 생각해보자
INSERT INTO USER1 VALUES(1, 'AAA', 'KIM', '010-0000-0000', '서울', 75, 100, '2021-05-28', 'N');
INSERT INTO USER1 VALUES(2, 'BBB', 'KIM', '010-0000-0000', '서울', 75, 100, '2021-05-28', 'N');
INSERT INTO USER1 VALUES(3, 'CCC', 'KIM', '010-0000-0000', '서울', 100, 100, '2021-05-28', 'N');
INSERT INTO USER1 VALUES(4, 'DDD', 'KIM', '010-0000-0000', '서울', 100, 100, '2021-05-28', 'N');
Q10) TEST의 USR을 다시 ID로 변경하자
ALTER TABLE TEST
RENAME COLUMN USR TO ID;
Q11) TEST 테이블에 ID에 제약조건(TEST_ID_PK)을 추가하자
ALTER TABLE TEST
ADD CONSTRAINT TEST_ID_PK PRIMARY KEY(ID);
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TEST';
Q12) TEST의 기본 키를 삭제해보자
ALTER TABLE TEST DROP PRIMARY KEY;
Q13) ID에다가 UNIQUE 제약을 걸자 UID : 제약조건 이름 안댐 예약어인듯?
ALTER TABLE TEST
ADD CONSTRAINT TEST_U_ID UNIQUE(ID);
Q14) NAME CHAR(10) --> VARCHAR2(10)로 변경해보자
ALTER TABLE TEST
MODIFY NAME VARCHAR2(10);
Q15) PKTABLE을 생성한 후 데이터를 입력해보자
CREATE TABLE PKTABLE(
ID NUMBER CONSTRAINT PKTABLE_ID_PK PRIMARY KEY,
NAME VARCHAR2(10)
);
Q16) PKTABLE의 제약조건을 삭제하자
Q17) PKTABLE의 제약조건을 추가해보자
Q18)
CREATE TABLE UTABLE(
ID NUMBER CONSTRAINT U_ID_U UNIQUE,
NAME VARCHAR2(10)
);
INSERT INTO UTABLE VALUES(1, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
DELETE FROM UTABLE WHERE ID IS NULL;
Q19) UTABLE의 제약조건을 삭제한다.
ALTER TABLE UTABLE
DROP CONSTRAINTS U_ID_U;
Q20) 동일한 이름으로 (U_ID_U) UTABLE ID와 NAME으로 테이블 레벨로 복합키를 만들자
ALTER TABLE UTABLE ADD CONSTRAINT U_ID_U UNIQUE(ID,NAME);
INSERT INTO UTABLE VALUES(1, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
INSERT INTO UTABLE VALUES(NULL, '111');
Q21) 테이블 생성 후 PK 연습해보자
- 테이블 당 1개만 생성할 수 있다
- UNIQUE + NOT NULL
CREATE TABLE TABLE_PK(
ID CHAR(3) PRIMARY KEY,
SNAME VARCHAR2(20)
);
Q22) 테이블 생성 후 PK 연습해보자 : 조합 컬럼 생성
CREATE TABLE TABLE_PK(
ID CHAR(3) PRIMARY KEY,
SNAME VARCHAR2(20)
);
Q23) MY_EMP, MY_DEPT의 두 테이블에 각각 PK를 추가한다.
CREATE TABLE MY_EMP
AS
SELECT * FROM EMP;
CREATE TABLE MY_DEPT
AS
SELECT * FROM DEPT;
ALTER TABLE MY_EMP
ADD CONSTRAINT ME_PK PRIMARY KEY(EMPNO);
ALTER TABLE MY_DEPT
ADD CONSTRAINT MD_PK PRIMARY KEY(DEPTNO);
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'MY_EMP' OR TABLE_NAME = 'MY_DEPT';