Oracle (오라클) Primary Key (기본키) 실습

1. 도메인 무결성 제약 (Domain Constraint) : NUMBER에 문자는 못 들어간다.

자료형을 NUMBER로 가지는 NUMCOL 컬럼에 'CHAR' 이라는 문자를 넣으려 해서 invalid number 에러 발생.

 

숫자로 해보면 잘 들어가는 것을 확인.

 

이번에는 NULL을 허용하지 않겠다고(NOT NULL) 했는데 NULL을 넣으려 해서 에러 발생.

 

실습이 끝났으니 테이블을 삭제하자. 테이블은 하나씩 삭제하도록 한다.

 

2. 개체 무결성 제약 (Primary Key Constraint) : Primary Key는 중복 허용 X

다시 불러와보자. 이미 테이블이 있지만 무시하자. 비우면서 밀어넣을거다.

 

CSV로 하고 파일을 다시 찾아온다.

 

Comma(,)로 구분.

 

그대로 진행한다.

 

PK로 지정하고 다음을 누른다.

무시하고 진행

 

데이터를 Import 하기 전에 테이블을 비우겠다! (Truncate table 선택)

그리고 Excute를 하면 기존 테이블을 비우고, PK키를 지정해서 새 테이블로 만들어준다.

 

알겠다고 Yes 해준다. 알아서 잘 넣어줌.

 

Errors 1은 컬럼헤더 1줄은 알아서 빼고 들어간거라 무시하면 된다. 잘 들어간거다. (도메인 참조 무결성에 의한 에러)

 

잘 들어온 것을 확인.

 

이유는 모르겠지만... Import에서 아무리 PK 설정을 해도 PK로 지정이 안 된다. 버그같은데... 혹시 PK 지정이 안 되어서 테이터가 삽입된다면 테이블을 아예 삭제하고 아래와 같이 처음부터 PK를 설정하고 테이블을 다시 만든 다음 Import 해온다.

DROP TABLE KOPO_PRODUCT_VOLUME
 
CREATE TABLE KOPO_PRODUCT_VOLUME
(
    REGIONID VARCHAR2(20),
    PRODUCTGROUP VARCHAR2(20),
    YEARWEEK VARCHAR2(6),
    VOLUME NUMBER,   
    CONSTRAINT PK_KOPO_PRODUCT_VOLUME PRIMARY KEY(REGIONID, PRODUCTGROUP, YEARWEEK)
);
 

 

중복되는 PK로 값을 넣으려 하면 에러 발생

INSERT INTO KOPO_PRODUCT_VOLUME
VALUES('A01','ST0001','201415',222)
 

업데이트도 마찬가지

UPDATE KOPO_PRODUCT_VOLUME
SET YEARWEEK = '201415'
WHERE YEARWEEK = '201413'

SELECT * FROM KOPO_PRODUCT_VOLUME

INSERT INTO KOPO_PRODUCT_VOLUME
VALUES('A01','ST0001','201415',222)

DROP TABLE KOPO_PRODUCT_VOLUME

CREATE TABLE KOPO_PRODUCT_VOLUME
(
    REGIONID VARCHAR2(20),
    PRODUCTGROUP VARCHAR2(20),
    YEARWEEK VARCHAR2(6),
    VOLUME NUMBER,   
    CONSTRAINT PK_KOPO_PRODUCT_VOLUME PRIMARY KEY(REGIONID, PRODUCTGROUP, YEARWEEK)
);

UPDATE KOPO_PRODUCT_VOLUME
SET YEARWEEK = '201415'
WHERE YEARWEEK = '201413'

 

 

3. 참조 무결성 제약 (Foreign Key Constraint) : Foreign Key를 통한 부모/자식 테이블간 Relation

 

-- 부모 테이블
CREATE TABLE KOPO_EVENT_INFO_FOREIGN
(
    EVENTID VARCHAR2(20),
    EVENTPERIOD VARCHAR2(20),
    PROMOTION_RATIO NUMBER,
    CONSTRAINT PK_KOPO_EVENT_INFO_FOREIGN PRIMARY KEY (EVENTID) -- 제약조건 설정 / 형식상 써주는거 / PK (지정할 컬럼)
);
 
SELECT * FROM KOPO_EVENT_INFO_FOREIGN
 
 
-- 자식 테이블
 
CREATE TABLE KOPO_PRODUCT_VOLUME_FOREIGN
(
    REGIONID VARCHAR2(20),
    PRODUCTGROUP VARCHAR2(20),
    YEARWEEK VARCHAR2(8),
    VOLUME NUMBER NOT NULL,
    EVENTID VARCHAR2(20),
    CONSTRAINT PK_KOPO_PRODUCT_VOLUME_FOREIGN PRIMARY KEY (REGIONID, PRODUCTGROUP, YEARWEEK),
    CONSTRAINT FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID) REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID) -- 제약조건 설정 / 형식상 써주는거 / FK (지정할 컬럼) ref '부모테이블'(의 컬럼명)
);
 
SELECT * FROM KOPO_PRODUCT_VOLUME_FOREIGN

컬럼명에는 띄어쓰기가 허용되지 않는다. 'PROMOTION RATIO'
REFERENCES 다. 절대 S를 빼먹지 않는다.

 

INSERT INTO KOPO_EVENT_INFO_FOREIGN
VALUES ('A01','20',0.1)
 
INSERT INTO KOPO_PRODUCT_VOLUME_FOREIGN
VALUES ('SEOUL','REF','202010',20,'A01')
 
INSERT INTO KOPO_PRODUCT_VOLUME_FOREIGN
VALUES ('SEOUL','REF','202010',20,'A02')
 
SELECT * FROM KOPO_PRODUCT_VOLUME_FOREIGN

부모 테이블 EVENTID에 'A01'이 있는 데이터를 넣어줬다.

부모 테이블의 EVENTID를 FK로 갖는 자식 테이블에 'A01'이 포함된 데이터를 넣었다.

이번에는 부모 테이블의 EVENTID를 FK로 갖는 자식 테이블에 'A02'이 포함된 데이터를 넣었다. 에러!!! (부모 테이블에 참조할 'A02'라는 키가 없기 때문!!)

 

 

DELETE FROM KOPO_EVENT_INFO_FOREIGN
WHERE EVENTID = 'A01'

참조중인 키가 있는 행은 삭제할 수 없다.

 

 

DROP TABLE KOPO_EVENT_INFO_FOREIGN

테이블을 지우려는 경우도 마찬가지다.

 

-- 자식 테이블에서 참조키 설정하기
ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
ADD CONSTRAINTS FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID)
REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID)
 
-- 자식 테이블에서 참조키 드랍하기
ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
DROP CONSTRAINT FK_KOPO_PRODUCT_VOLUME_FOREIGN;

자식 테이블에서 참조키를 드랍하고 삭제를 하면 잘 진행된다.

 

또는 자식 테이블 생성시 혹은 자식 테이블에서 참조키 설정시 ON DELETE CASCADE 옵션을 주면 부모 테이블에서 삭제시 아무런 에러 없이 삭제되며 해당 키를 참조한 자식 테이블의 row가 전부 삭제된다. 또는 ON DELETE SET NULL 옵션을 주면 부모 테이블에서 삭제시 아무런 에러 없이 삭제되는 것은 동일하지만 이 경우 자식 테이블의 해당 참조키를 NULL로 바꾼다.

ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
ADD CONSTRAINTS FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID)
REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID)
ON DELETE CASCADE -- 부모 테이블에서 에러 없이 삭제가 가능해진다. (자식 테이블의 해당 row를 전부 삭제)

이렇게 ON DELETE CASCADE 옵션을 준 경우 

DELETE FROM KOPO_EVENT_INFO_FOREIGN
WHERE EVENTID = 'A01'

부모 테이블에서 에러 없이 삭제가 가능하다. 해당 키를 참조하던 자식테이블의 해당 row는 모두 삭제된다.

 

ALTER TABLE KOPO_PRODUCT_VOLUME_FOREIGN
ADD CONSTRAINTS FK_KOPO_PRODUCT_VOLUME_FOREIGN FOREIGN KEY (EVENTID)
REFERENCES KOPO_EVENT_INFO_FOREIGN (EVENTID)
ON DELETE SET NULL -- 부모 테이블에서 에러 없이 삭제가 가능해진다. (자식 테이블의 해당 FK를 NULL처리하고 row 자체를 삭제하진 않는다.)

이렇게 ON DELETE SET NULL 옵션을 준 경우

DELETE FROM KOPO_EVENT_INFO_FOREIGN
WHERE EVENTID = 'A01'

부모 테이블에서 에러 없이 삭제가 가능하다. 해당 키를 참조하던 자식 테이블의 row 자체가 삭제되진 않고 참조하던 키가 있던 데이터만 NULL로 바꾼다.

 

 

또는 테이블 자체를 삭제하려는 경우 CASCADE 옵션을 넣어준다.

DROP TABLE KOPO_EVENT_INFO_FOREIGN
 
DROP TABLE KOPO_EVENT_INFO_FOREIGN CASCADE CONSTRAINT

(이 경우 NULL값을 만들지 않고 참조키로 설정한 테이블에 해당 row를 모두 제거하며 테이블을 삭제하니 주의한다.

 

 

CASCADE : 참조하는 테이이블을 찾아가서 직접 삭제한다. (Recursive와는 다름. Recursive는 재귀함수)

 

 

 

cf.

	도메인 무결성 제약 (Domain Constraint)	개체 무결성 제약 (Primary Key Constraint)	참조 무결성 제약 (Foreign Key Constraint)
제약 대상	column(Artribute)	row(Tuple)	row and column
Null값 허용	O	X	O
요약	NUMBER에 '문자'는 못 들어간다	Primary Key는 중복 허용 X	부모/자식 테이블간 Relation

 

cf. 테이블 정보 조회하기

DESC 테이블명

으로 조회할 수 있다.

이런 창이 뜨는데 처음 Columns에서는 빠르게 컬럼명과 PK(기본키) 설정 여부, Null 허용 여부, 자료형에 대한 정보를 확인할 수 있다.

하지만 FK(참조키) 설정 여부 조회는 Constraints에서 가능하다. (물론 이곳에서는 PK도 조회가 가능하다.)