오라클 PK 제약조건 만들기

오라클 기본키 제약조건
오라클 PK 제약조건 만들기

1 테스트 환경[ | ]

CREATE TABLE STUDENT (
  "SID" NUMBER,
  "NAME" VARCHAR2(32),
  "EMAIL" VARCHAR2(32)
)

INSERT INTO STUDENT VALUES (582812, '한놈', 'a@a.a');
INSERT INTO STUDENT VALUES (582812, '한놈', 'hannom@example.com');
INSERT INTO STUDENT VALUES (582812, '한놈', 'b@b.b');
INSERT INTO STUDENT VALUES (241234, '두식이', 'dusigi@example.com');
INSERT INTO STUDENT VALUES (241234, '두식이', '');
INSERT INTO STUDENT VALUES (372342, '석삼', 'seoksam@example.com');

2 문제 상황[ | ]

기본키가 없는 테이블 STUDENT에서 SID를 기본키로 하고 싶다.

ALTER TABLE STUDENT
   ADD CONSTRAINT SID_PK PRIMARY KEY (SID)

ORA-02437: cannot valudate(MYDB.SID_PK) - primary key violated

3 중복값 확인[ | ]

SELECT * FROM (
    SELECT SID, COUNT(SID) CNT FROM STUDENT GROUP BY SID
) A WHERE CNT > 1

SID	CNT
582812	3
241234	2

→ SID가 중복되는 것이 있다.

SELECT * FROM STUDENT WHERE SID IN (
    SELECT SID FROM (
        SELECT SID, COUNT(SID) CNT FROM STUDENT GROUP BY SID
    ) A WHERE CNT > 1
)

SID	NAME	EMAIL
582812	한놈	a@a.a
582812	한놈	hannom@example.com
582812	한놈	a@a.a
241234	두식이	dusigi@example.com
241234	두식이

4 중복값 제거[ | ]

4.1 방법 1: 최근 것을 남김[ | ]

SID별 입력 역순 조회

SELECT ROWID, RANK () OVER (PARTITION BY SID ORDER BY ROWID DESC) RNK, STUDENT.* FROM STUDENT

ROWID	RNK	SID	NAME	EMAIL
AAAcW4AAFAAAAMCAAQ	1	241234	두식이	
AAAcW4AAFAAAAMCAAP	2	241234	두식이	dusigi@example.com
AAAcW4AAFAAAAMCAAR	1	372342	석삼	seoksam@example.com
AAAcW4AAFAAAAMCAAO	1	582812	한놈	b@b.b
AAAcW4AAFAAAAMCAAN	2	582812	한놈	hannom@example.com
AAAcW4AAFAAAAMCAAM	3	582812	한놈	a@a.a

→ RNK가 2이상인 것들을 삭제할 것이다.

삭제 대상 확인

SELECT * FROM STUDENT
WHERE ROWID IN (
    SELECT ROWID FROM (
        SELECT ROWID, RANK () OVER (PARTITION BY SID ORDER BY ROWID DESC) RNK, STUDENT.* FROM STUDENT
    ) WHERE RNK>1
)

SID	NAME	EMAIL
241234	두식이	dusigi@example.com
582812	한놈	a@a.a
582812	한놈	hannom@example.com

삭제 후 결과확인

DELETE FROM STUDENT
WHERE ROWID IN (
    SELECT ROWID FROM (
        SELECT ROWID, RANK () OVER (PARTITION BY SID ORDER BY ROWID DESC) RNK, STUDENT.* FROM STUDENT
    ) WHERE RNK>1
)

SELECT * FROM STUDENT

SID	NAME	EMAIL
582812	한놈	b@b.b
241234	두식이	
372342	석삼	seoksam@example.com

4.2 방법 2: 잘 입력된 것을 남김[ | ]

EMAIL이 잘 입력된 순으로 조회^[1]

SELECT ROWID, RANK () OVER (PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST, ROWID DESC) RNK, STUDENT.* FROM STUDENT

ROWID	RNK	SID	NAME	EMAIL
AAAcW4AAFAAAAMCAAh	1	241234	두식이	dusigi@example.com
AAAcW4AAFAAAAMCAAi	2	241234	두식이	
AAAcW4AAFAAAAMCAAj	1	372342	석삼	seoksam@example.com
AAAcW4AAFAAAAMCAAZ	1	582812	한놈	hannom@example.com
AAAcW4AAFAAAAMCAAa	2	582812	한놈	b@b.b
AAAcW4AAFAAAAMCAAY	3	582812	한놈	a@a.a

→ RNK가 2이상인 것들을 삭제할 것이다.

삭제 대상 확인

SELECT * FROM STUDENT
WHERE ROWID IN (
    SELECT ROWID FROM (
        SELECT ROWID, RANK () OVER (PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST, ROWID DESC) RNK, STUDENT.* FROM STUDENT
    ) WHERE RNK>1
)

SID	NAME	EMAIL
241234	두식이	
582812	한놈	a@a.a
582812	한놈	b@b.b

삭제 후 결과확인

DELETE FROM STUDENT
WHERE ROWID IN (
    SELECT ROWID FROM (
        SELECT ROWID, RANK () OVER (PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST, ROWID DESC) RNK, STUDENT.* FROM STUDENT
    ) WHERE RNK>1
)

SELECT * FROM STUDENT

SID	NAME	EMAIL
582812	한놈	hannom@example.com
241234	두식이	dusigi@example.com
372342	석삼	seoksam@example.com

5 PK 제약조건 생성 테스트[ | ]

이제 중복값이 없어 아래 SQL문을 오류없이 실행할 수 있다.

ALTER TABLE STUDENT
   ADD CONSTRAINT SID_PK PRIMARY KEY (SID)

6 같이 보기[ | ]

기본키

7 주석[ | ]

↑ 정확히는 EMAIL 문자열의 길이가 긴 것, ROWID가 큰 것 순

8 참고[ | ]

[1] 정확히는 EMAIL 문자열의 길이가 긴 것, ROWID가 큰 것 순

[1]