[SQL] 부모 일반 컬럼에 FOREIGN KEY 속성 불가 (UNIQUE CONSTRAINT, ALTER PRIMARY KEY 실습 내용 추가)

TL;DR

• 오라클에서는 일반 컬럼에 FOREIGN KEY (외래키, FK) 제약 조건을 걸 수 없습니다. 
• 부모 테이블에 PRIMARY KEY (기본키, PK) 이거나 UNIQUE 제약 조건을 걸어줘야 자식 테이블의 컬럼이 FK 속성으로 걸어줄 수 있습니다. 
• 또한 오라클에서 한 테이블에는 오직 하나의 PK만 가능하고 UNIQUE는 제한이 없습니다. 
• CREATE TABLE 절로 테이블 생성 후에는 ALTER 명령으로 PK를 추가해주는 것은 불가능하고 PK에서 해제하는 것만 가능합니다. 
• 한 컬럼에 PK, FK 속성을 동시에 주는 것은 가능합니다.
• 한 컬럼에 PK, UNIQUE 속성을 동시에 주는 것은 가능합니다.
• 부모/자식 테이블의 연결되는 컬럼은 SELECT 절에서 어떤 테이블의 컬럼을 조회하느냐에 따라 결과 값이 다르다. (건수는 변화없음)

---

예시 데이터

이번 포스팅을 하게 된 계기가 된 문제입니다. 주어진 두 테이블에서 P74A 테이블의 경우 A 컬럼이 PK 속성, C는 P74B 테이블과 연결된 FK라고 문제에서 주어졌습니다. 그리고 두 테이블을 LEFT OUTER JOIN, FULL OUTER JOIN, RIGHT OUTER JOIN하여 생성 되는 결과 건수를 묻는 문제인데요.

 

여러분도 데이터만 일단 보시고 각각 몇 건을 반환할 지 생각해보시면 좋을 것 같습니다. 

 

SQL> select * from p74a;

A          B          C
---------- ---------- ----------
1          b          w
3          d          w
5          y          y

SQL> select * from p74b;

C          D          E
---------- ---------- ----------
w          1          10
z          4          11
v          2          22

---

우선 위 문제대로 속성을 걸어 생성이 가능하다고 가정하여 결과를 보겠습니다. 

먼저 데이터를 생성해보겠습니다. 문제를 풀기 위해 어떠한 제약 조건도 걸지 않았습니다. 

SQL> create table test01.p74a (a varchar2(10), b varchar2(10), c varchar2(10));

Table created.

SQL> create table test01.p74b (c varchar2(10), d varchar2(10), e varchar2(10));

Table created.

SQL> insert into test01.p74a (a,b,c) values (1,'b','w');

1 row created.

SQL> insert into test01.p74a (a,b,c) values (3,'d','w');

1 row created.

SQL> insert into test01.p74a (a,b,c) values (5,'y','y');

1 row created.

SQL> insert into test01.p74b (c,d,e) values ('w',1,10);

1 row created.

SQL> insert into test01.p74b (c,d,e) values ('z',4,11);

1 row created.

SQL> insert into test01.p74b (c,d,e) values ('v',2,22);

1 row created.

SQL> commit;

---

LEFT OUTER JOIN 쿼리와 결과입니다. 

SQL> SELECT TA.A, TA.B, TA.C, TB.D, TB.E
  2  FROM P74A TA LEFT OUTER JOIN P74B TB
  3  ON (TA.C = TB.C);

 

결과1:

A          B          C          D          E
---------- ---------- ---------- ---------- ----------
1          b          w          1          10
3          d          w          1          10
5          y          y

 

LEFT OUTER JOIN 쿼리에서 SELECT 절에 TA.C 대신 TB.C를 쓰게 되면 C 컬럼에서 y가 null로 표시됩니다. P74B 테이블의 C 컬럼에는 y값이 존재하지 않기 때문입니다. 아래 쿼리 결과를 참고하세요. 

SELECT TA.A, TA.B, TB.C, TB.D, TB.E
FROM P74A TA LEFT OUTER JOIN P74B TB
ON (TA.C = TB.C)

 

결과 2:

A          B          C          D          E
---------- ---------- ---------- ---------- ----------
1          b          w          1          10
3          d          w          1          10
5          y

 

RIGHT OUTER JOIN 쿼리와 결과입니다. 

SELECT TA.A, TA.B, TA.C, TB.D, TB.E
FROM P74A TA RIGHT OUTER JOIN P74B TB
ON (TA.C = TB.C)

 

결과1:

A          B          C          D          E
---------- ---------- ---------- ---------- ----------
1          b          w          1          10
3          d          w          1          10
                                 2          22
                                 4          11

 

RIGHT OUTER JOIN에서 SELECT 절에 P74A 테이블의 C 컬럼 (TA.C)가 아닌 P74B의 C 컬럼 (TB.C)을 사용한다면 결과가 또 달라지게 됩니다.

SELECT TA.A, TA.B, TB.C, TB.D, TB.E
FROM P74A TA RIGHT OUTER JOIN P74B TB
ON (TA.C = TB.C)

 

결과2:

A          B          C          D          E
---------- ---------- ---------- ---------- ----------
1          b          w          1          10
3          d          w          1          10
                      v          2          22
                      z          4          11

 

FULL OUTER JOIN 쿼리와 결과입니다. 

SELECT TA.A, TA.B, TA.C, TB.D, TB.E
FROM P74A TA FULL OUTER JOIN P74B TB
ON (TA.C = TB.C)

 

결과1:

A          B          C          D          E
---------- ---------- ---------- ---------- ----------
1          b          w          1          10
3          d          w          1          10
                                 4          11
                                 2          22
5          y          y

 

FULL OUTER JOIN에서도 SELECT 절에 TA.C 컬럼을 사용한 것과 TB.C를 사용한 결과가 달라집니다. 건수는 똑같지만 표시되는게 다른데요. 참고하시기 바랍니다. 

 SELECT TA.A, TA.B, TB.C, TB.D, TB.E
 FROM P74A TA FULL OUTER JOIN P74B TB
 ON (TA.C = TB.C)

 

결과2:

A          B          C          D          E
---------- ---------- ---------- ---------- ----------
1          b          w          1          10
3          d          w          1          10
                      z          4          11
                      v          2          22
5          y

---

이제 문제의 JOIN결과와 별개로 주어진 설명대로 한번 테이블을 생성해보겠습니다. 

 

SQL> create table test01.p74a (a varchar2(10) not null primary key, b varchar2(10), c varchar2(10)) tablespace tbs_test_01;

Table created.

SQL> create table test01.p74b (c varchar2(10), d varchar2(10), e varchar2(10), foreign key (c) references test01.p74a(c) on delete cascade) tablespace tbs_test_01;
create table test01.p74b (c varchar2(10), d varchar2(10), e varchar2(10), foreign key (c) references test01.p74(c) on delete cascade) tablespace tbs_test_01
                                                                                                                *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

 

아래처럼 P74A테이블에 UNIQUE 컬럼을 주게되면 P74A테이블의 PK 속성, P74B 테이블에 FK 속성을 만족하며 문제처럼 설정이 가능한 것 처럼 보입니다. UNIQUE CONTRAINT를 C 컬럼에 준 것은 P74B 테이블에서 FK를 걸기 위해서 입니다. FK는 앞서 얘기했듯 부모 테이블의 일반 컬럼에는 줄 수가 없기 때문입니다. 

SQL> create table test01.p74a (a varchar2(10) not null primary key, b varchar2(10), c varchar2(10), constraint uc_c unique (c)) tablespace tbs_test_01;

Table created.

SQL> create table test01.p74b (c varchar2(10) not null primary key, d varchar2(10), e varchar2(10), foreign key (c) references test01.p74a(c) on delete cascade) tablespace tbs_test_01;

Table created.

하지만 위처럼 테이블을 생성했을 때 P74A 테이블의 C 컬럼에 UNIQUE CONSTRAINT를 주었기 때문에 동일한 값('w')가 두번 째 들어가려하면 에러가 발생합니다:

SQL> insert into test01.p74a (a,b,c) values (1,'b','w');

1 row created.

SQL> insert into test01.p74a (a,b,c) values (3,'d','w');
insert into test01.p74a (a,b,c) values (3,'d','w')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST01.UC_C) violated

 

결국 해당 문제를 굳이 JOIN 건수를 구하는 문제로서 풀기 위해선 1) 모든 PK, FK속성이 없다고 가정하고 2) LEFT OUTER JOIN시 SELECT 절에는 P74A 테이블의 C컬럼을, RIGHT OUTER JOIN 시 SELECT 절에는 P74B 테이블의 C컬럼을 사용했다고 가정하고 풀어야 할 겁니다.

물론 건수에는 차이가 없죠. 그리고 심지어 해설에서 FULL OUTER JOIN의 결과를 보게 되면 C 컬럼에 'w,w,y,z,v'가 표시되어 있는데 오라클에선 단순히 FULL OUTER JOIN으로는 해당 결과를 얻을 수 없습니다.