2021.02.18 Oracle SQL

--재생성을 위한 기존 테이블 삭제

DROP TABLE buyTBL;
DROP TABLE userTBL;

--userTBL 테이블 생성

CREATE TABLE userTBL
(
userID CHAR(8),
userName NVARCHAR2(10),
birthYear NUMBER(4),
addr NCHAR(2),
mobile1 CHAR(3),
mobile2 CHAR(8),
height NUMBER(3),
mDate DATE
);

--buyTBL 테이블 생성

CREATE TABLE buyTBL
(
idNum NUMBER(8) PRIMARY KEY,
userID CHAR(8),
prodName NCHAR(6),
groupName NCHAR(4),
price NUMBER(8),
amount NUMBER(3)
);

--시퀀스 재생성을 위한 삭제 후 재생성

DROP SEQUENCE idSEQ;
CREATE SEQUENCE idSEQ;

--userTBL에 데이터 입력. 김범수의 출생년도는 모르는 것으로 null값, 김경호는 1871년으로 잘못 입력

INSERT INTO userTBL VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTBL VALUES('KBS', '김범수', NULL, '경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTBL VALUES('KKH', '김경호', 1871, '전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTBL VALUES('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');

--buyTBL에 데이터 입력

INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '모니터', '전자', 200, 5);

--제약 조건 생성. (기본 키 제약 조건을 생성)

ALTER TABLE userTBL
ADD CONSTRAINT PK_userTBL_userID
PRIMARY KEY(userID);

--기본 키 확인

SELECT * FROM USER_CONSTRAINTS
WHERE OWNER = 'TABLEDB' AND TABLE_NAME = 'USERTBL' AND CONSTRAINT_TYPE = 'P';
DESCRIBE userTBL;

--외래 키 설정

ALTER TABLE buyTBL --userID 열에 외래 키를 설정
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL (userID);

--오류 발생.
--ORA-02298: cannot validate (TABLEDB.FK_USERTBL_BUYTBL) - parent keys not found
--02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
--*Cause:    an alter table validating constraint failed because the table has
--           child records.
--*Action:   Obvious

---

--BBK 행을 삭제하고, 다시 외래 키를 설정

DELETE FROM buyTBL WHERE userID = 'BBK';
SELECT * FROM buyTBL; --BBK 삭제 확인

ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL (userID);

--buyTBL의 네 번째 데이터를 다시 입력

INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL,'BBK', '모니터', '전자', 200, 5);

--오류발생.
--ORA-02291: integrity constraint (TABLEDB.FK_USERTBL_BUYTBL) violated - parent key not found

---

--외래 키 제약 조건을 활성화

ALTER TABLE buyTBL
DISABLE CONSTRAINT FK_userTBL_buyTBL;
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '메모리', '전자', 50, 10);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'SSK', '책' , '서적', 15, 5);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'EJW', '책' , '서적', 15, 2);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '운동화', NULL, 30, 2);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'EJW', '책' , '서적', 15 , 1);
INSERT INTO buyTBL VALUES(idSEQ.NEXTVAL, 'BBK', '운동화', NULL , 30, 2);
ALTER TABLE buyTBL
ENABLE NOVALIDATE CONSTRAINT FK_userTBL_buyTBL;

--userTBL의 출생년도를 1900~2017까지만 설정하도록 CHECK 제약 조건을 설정

ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK (birthYear >= 1900 AND birthYear <= 2017) --CHECK 제약 조건 설정
ENABLE;

--오류 발생
--ORA-02293: cannot validate (TABLEDB.CK_BIRTHYEAR) - check constraint violated
--02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
--*Cause:    an alter table operation tried to validate a check constraint to
--           populated table that had nocomplying values.
--*Action:   Obvious

---

ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK (birthYear >= 1900 AND birthYear <= 2017) --CHECK 제약 조건 설정
ENABLE NOVALIDATE; --NOVALIDATE 추가

--나머지 userTBL 데이터 입력

INSERT INTO userTBL VALUES('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO userTBL VALUES('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
INSERT INTO userTBL VALUES('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO userTBL VALUES('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
INSERT INTO userTBL VALUES('JKW', '조관우', 1965, '경기', '018', '99999999', 172, '2010-10-10');
INSERT INTO userTBL VALUES('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');

--바비킴(BBK)이 회원을 탈퇴하면 (=회원 테이블에서 삭제되면) 구매한 기록도 삭제되는지 확인

DELETE FROM userTBL WHERE userID = 'BBK';
--오류발생
--ORA-02292: integrity constraint (TABLEDB.FK_USERTBL_BUYTBL) violated - child record found

ALTER TABLE buyTBL
DROP CONSTRAINT FK_userTBL_buyTBL;
ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL (userID)
ON DELETE CASCADE; --ON DELETE CASCADE 문을 함께 설정

--buyTBL에도 따라서 삭제되었는지 확인

DELETE FROM userTBL WHERE userID = 'BBK';
SELECT * FROM buyTBL;

--userTBL에서 CHECK 제약 조건이 걸린 출생년도(birthYear)열을 삭제

ALTER TABLE userTBL --ALTER TABLE로 삭제
DROP COLUMN birthYear;
SELECT * FROM userTBL;
SELECT birthYear FROM userTBL;