[SQL] 관계를 맺은 테이블의 DML

PK 값의 수정

자식에서 참조하고 있는 PK 값의 수정은 기본적으로 막혀있다.

일반적으로 부모 테이블의 PK를 수정하는 것은 권장하지 않는다.

그 이유는 일관성을 손상시키고, 무결성에 위배될 수 있기 때문이다.

 

하지만, 필요에 따라서 수정해야할 때도 있기 때문에 아래의 두 가지 방법 중 하나를 선택하여 PK 값을 수정해야할 때 사용하도록 한다.

 

---

🔗 DML이란?

[SQL] DML (SELECT, INSERT, UPDATE, DELETE)

🔗 무결성이란?

[SQL] 무결성

---

 

테이블 생성

작업을 하기 위해 테이블 CREATE 및 테이블 내에 값을 INSERT 하도록 한다.

-- [테이블 세팅]===============================================================
CREATE TABLE TBL_PHONE(
	PHONE_SERIAL_NUMBER VARCHAR2(500),
	PHONE_COLOR VARCHAR2(500),
	PHONE_SIZE NUMBER,
	PHONE_PRICE NUMBER,
	PHONE_PRODUCTION_DATE DATE,
	PHONE_SALE NUMBER,
	CONSTRAINT PK_PHONE PRIMARY KEY(PHONE_SERIAL_NUMBER)
);

CREATE TABLE TBL_CASE(
	CASE_SERIAL_NUMBER VARCHAR2(500),
	CASE_COLOR VARCHAR2(500),
	CASE_PRICE NUMBER,
	PHONE_SERIAL_NUMBER VARCHAR2(500),
	CONSTRAINT PK_CASE PRIMARY KEY(CASE_SERIAL_NUMBER),
	CONSTRAINT FK_CASE FOREIGN KEY(PHONE_SERIAL_NUMBER)
	REFERENCES TBL_PHONE(PHONE_SERIAL_NUMBER)
);

INSERT INTO TBL_PHONE
(PHONE_SERIAL_NUMBER, PHONE_COLOR, PHONE_SIZE, PHONE_PRICE, PHONE_PRODUCTION_DATE, PHONE_SALE)
VALUES('S23-001', 'GREEN', 7, 200, '2023-02-11', 0);
-- DATE 타입의 컬럼에 문자타입의 값을 'YYYY-MM-DD' 의 형태로 넣으면
-- 자동으로 DATE 타입으로 변환되어 들어간다. (일정한 데이터타입의 형태에 맞춰서 작성하면)

INSERT INTO TBL_PHONE
(PHONE_SERIAL_NUMBER, PHONE_COLOR, PHONE_SIZE, PHONE_PRICE, PHONE_PRODUCTION_DATE, PHONE_SALE)
VALUES('S23-002', 'WHITE', 7, 200, SYSDATE, 0); -- 프로젝트할 때 가장 많이 사용하는 형태

INSERT INTO TBL_PHONE
(PHONE_SERIAL_NUMBER, PHONE_COLOR, PHONE_SIZE, PHONE_PRICE, PHONE_PRODUCTION_DATE, PHONE_SALE)
VALUES('S23-003', 'BLACK', 7, 200, '2023/02/15', 0);


-- 자식 테이블에도 값 INSERT!
INSERT INTO TBL_CASE
VALUES('ABC', 'WHITE', 10000, 'S23-001');

INSERT INTO TBL_CASE
VALUES('DEF', 'BLACK', 12000, 'S23-002');

-- [오류 CASE] 이런 식으로 부모에 없는 값('S23-004')을 넣으려고 하면 오류가 난다.
INSERT INTO TBL_CASE
VALUES('ABC', 'WHITE', 10000, 'S23-004'); -- 오류! 부모에 없는 값을 참조함.
-- [//오류 CASE] ===================================================

SELECT * FROM TBL_PHONE;
SELECT * FROM TBL_CASE;
-- [//테이블 세팅]===============================================================

 

 

TBL_PHONE

 

TBL_CASE

TBL_PHONE 과 TBL_CASE 의 엔티티 관계도

 

 

 

---

📁 오류 CASE

아래와 같은 쿼리문처럼 PK 를 수정하려고 하면 오류가 발생한다.

PHONE_SERIAL_NUMBER 는 자식에서 참조하고 있는 PK 값이기 때문이다.

-- [오류]=================================================
UPDATE TBL_PHONE 
SET PHONE_SERIAL_NUMBER = 'S23-444'
WHERE PHONE_SERIAL_NUMBER = 'S23-001';

 

'S23-001' 을 자식에서 참조하고 있기 때문에 다른 방법으로 접근해야 한다.

 

 

---

📁 방법1.

자식 테이블에서 부모 테이블을 참조중인 값을 NULL 로 변경한 후에 수정

 

 

1-1. 부모를 참조중인 자식 테이블의 FK 중, 수정하려는 부모 테이블의 값인 친구들을 NULL 로 임시로 바꿔준다.

UPDATE TBL_CASE 
SET PHONE_SERIAL_NUMBER = NULL
WHERE PHONE_SERIAL_NUMBER = 'S23-001';
-- (1)부모를 참조중인 자식 테이블의 FK 중, 수정하려는 부모 테이블의 값인 친구들을 NULL 로 임시로 바꿔준다.

 

 

1-2. 다시 PHONE_SERIAL_NUMBER 의 값을 수정해준다
   (1)의 과정을 거치지 않고 이것부터 바로 실행하면 실행 오류가 뜬다.

UPDATE TBL_PHONE 
SET PHONE_SERIAL_NUMBER = 'S23-444'
WHERE PHONE_SERIAL_NUMBER = 'S23-001';
-- (2)다시 PHONE_SERIAL_NUMBER 의 값을 수정해준다
-- (1)의 과정을 거치지 않고 이것부터 바로 실행하면 실행 오류가 뜬다.

 

 

1-3. 임시로 NULL 을 매워넣은 자식 테이블의 값을 다시 수정한 부모 테이블의 값(S23-444)으로 바꿔넣어준다.

UPDATE TBL_CASE 
SET PHONE_SERIAL_NUMBER = 'S23-444'
WHERE PHONE_SERIAL_NUMBER IS NULL;
-- (3)임시로 NULL 을 매워넣은 자식 테이블의 값을 다시 수정한 부모 테이블의 값(S23-444)으로 바꿔넣어준다.

 

 

---

📁 방법2.

부모에 임의의 값을 INSERT 하고 자식 FK 를 수정한 후에 진행한다.

 

 

2-1. 임시 값을 INSERT 한다.

INSERT INTO TBL_PHONE
(PHONE_SERIAL_NUMBER, PHONE_COLOR, PHONE_SIZE, PHONE_PRICE, PHONE_PRODUCTION_DATE, PHONE_SALE)
VALUES('111', '', 0, 0, '', 0);
-- (1)임시값을 INSERT 한다

 

 

2-2. 임시값을 참조하도록 FK 값을 수정한다.

UPDATE TBL_CASE
SET PHONE_SERIAL_NUMBER = '111'
WHERE PHONE_SERIAL_NUMBER = 'S23-002';
-- (2)임시값을 참조하도록 FK의 값을 수정한다

 

 

2-3. 참조하고 있는 FK 가 없어졌으니 바꾸려고 했던 PHONE_SERIAL_NUMBER 키를 변경한다.

UPDATE TBL_PHONE
SET PHONE_SERIAL_NUMBER = 'S23-222'
WHERE PHONE_SERIAL_NUMBER = 'S23-002';
-- (3)참조하고 있는 FK가 없어졌으니 바꾸려고 했던 PHONE_SERIAL_NUMBER 키를 변경한다

 

 

2-4. 자식 FK의 값을 임시로 만들어놓은 값에서 -> 수정한 부모의 값으로 다시 제대로 참조시켜준다.

UPDATE TBL_CASE
SET PHONE_SERIAL_NUMBER = 'S23-222'
WHERE PHONE_SERIAL_NUMBER = '111';
-- (4)자식 FK의 값을 임시로 만들어놓은 값에서 -> 수정한 부모의 값으로 다시 제대로 참조시켜준다

 

 

2-5. 임시로 넣은 값('111')을 삭제한다.

DELETE FROM TBL_PHONE
WHERE PHONE_SERIAL_NUMBER = '111';
-- (5) 임시로 넣은 값('111')을 삭제한다