오라클 PK 제약조건 만들기

테스트 환경

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');

---

문제 상황

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

ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY (SID);

SQL 오류: ORA-02437: (SCOTT.SID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다.

---

중복값 확인

SELECT * 

FROM (SELECT SID, COUNT(SID) CNT FROM STUDENT GROUP BY SID)

A WHERE CNT >1;

  SID       CNT

582812      3

241234      2

* SID 에 중복 값이 있다.

---

중복값 제거

방법 1) 최근 것을 남김

SID 별 입력 역순 조회

SELECT ROWID, 

RANK() OVER(PARTITION BY SID ORDER BY ROWID DESC) RNK,

STUDENT.* 

FROM STUDENT;

 

      ROWID                RNK      SID       NAME           EMAIL

AAASSdAAEAAAAKtAAE 1 241234 두식이 NULL

AAASSdAAEAAAAKtAAD 2 241234 두식이 dusigi@example.com

AAASSdAAEAAAAKtAAF 1 372342 석삼         seoksam@example.com

AAASSdAAEAAAAKtAAC 1 582812 한놈        b@b.b

AAASSdAAEAAAAKtAAB 2 582812 한놈        hannom@example.com

AAASSdAAEAAAAKtAAA 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 한놈      hannom@example.com

582812 한놈       a@a.a

---

삭제 후 결과 확인

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);

* 3개행이 삭제 되었습니다.

SELECT * FROM STUDENT;

  SID       NAME         EMAIL

582812 한놈       b@b.b

241234 두식이      NULL

372342 석삼       seoksam@example.com

---

방법2) 잘  입력된 것을 남김

EMAIL 이 잘 입력된 순으로 조회

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

AAASSfAAEAAAAK1AAD 1 241234 두식이 dusigi@example.com

AAASSfAAEAAAAK1AAE 2 241234 두식이 NULL

AAASSfAAEAAAAK1AAF 1 372342 석삼    seoksam@example.com

AAASSfAAEAAAAK1AAB 1 582812 한놈       hannom@example.com

AAASSfAAEAAAAK1AAC 2 582812 한놈       b@b.b

AAASSfAAEAAAAK1AAA 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

582812 한놈        a@a.a

241234 두식이      NULL

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 );

*3개 행이 삭제 되었습니다.

SELECT * FROM STUDENT;

  SID       NAME         EMAIL

582812 한놈    hannom@example.com

241234 두식이 dusigi@example.com

372342 석삼       seoksam@example.com

---

PK 제약조건 생성 테스트

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

ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY (SID);

* table STUDENT이(가) 변경되었습니다.

출처 : http://zetawiki.com/

---