By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Table1(
CompA varchar2(10), CompB varchar2(10), constraint Table1_pk primary key(CompA,CompB)
);
Insert all
into Table1 values('Value 1', 'Value 2')
into Table1 values('Value 1', 'Value 3')
into Table1 values('Value 2', 'Value 3')
SELECT null FROM dual;
3 rows affected
CREATE TABLE Table2(
T2PK varchar2(1),
CompA varchar2(10), CompB varchar2(10),
constraint Table2_fk foreign key(CompA,CompB) references table1(CompA,CompB)
);
Insert all
into Table2 values('A', 'Value 1', 'Value 2')
into Table2 values('B', 'Value 1', 'Value 3')
into Table2 values('C', 'Value 2', 'Value 3')
into Table2 values('D', 'Value 2', 'Value 3')
SELECT null FROM dual;
4 rows affected
CREATE TABLE new_Table1(
ID int PRIMARY KEY,
CompA varchar2(10), CompB varchar2(10),
constraint new_Table1_uq unique(CompA,CompB)
)
INSERT INTO new_Table1( id, CompA, CompB)
SELECT rownum, CompA, CompB FROM Table1
3 rows affected
CREATE TABLE new_Table2(
PK varchar2(1),
T1FK int,
constraint new_Table2_fk foreign key(T1FK) references new_Table1( id )
)
INSERT INTO new_Table2( PK , T1FK )
SELECT t2.T2PK, t1.ID
FROM Table2 t2
JOIN new_Table1 t1
ON t2.CompA = t1.CompA AND t2.CompB = t1.CompB
4 rows affected
SELECT * FROM new_Table1
| ID | COMPA | COMPB |
|---|---|---|
| 1 | Value 1 | Value 2 |
| 2 | Value 1 | Value 3 |
| 3 | Value 2 | Value 3 |
SELECT * FROM new_Table2
| PK | T1FK |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
| D | 3 |