By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table author ( authorid primary key, fname, lname )
as
select 1, 'fname_1', 'lname_1' from dual union all
select 2, 'fname_2', 'lname_2' from dual union all
select 3, 'fname_3', 'lname_3' from dual union all
select 4, 'fname_4', 'lname_4' from dual ;
4 rows affected
create table books ( isbn primary key, title )
as
select '978-1449324451', 'title_1' from dual union all
select '978-1449324452', 'title_2' from dual union all
select '978-1449324453', 'title_3' from dual union all
select '978-1449324454', 'title_1_4' from dual union all
select '978-1449324455', 'title_2_4' from dual union all
select '978-1449324456', 'title_3_4' from dual union all
select '978-1449324457', 'title_4_4' from dual ;
7 rows affected
create table bookauthor( authorid, isbn )
as
select A.authorid, B.isbn
from author A
join books B on A.authorid = substr( B.title, length( B.title ), 1 ) ;
7 rows affected
alter table bookauthor
add (
constraint ba_fk1 foreign key( authorid ) references author( authorid )
, constraint ba_fk2 foreign key( isbn ) references books( isbn )
, constraint ba_pk primary key ( authorid, isbn )
) ;
select * from bookauthor;
| AUTHORID | ISBN |
|---|---|
| 1 | 978-1449324451 |
| 2 | 978-1449324452 |
| 3 | 978-1449324453 |
| 4 | 978-1449324454 |
| 4 | 978-1449324455 |
| 4 | 978-1449324456 |
| 4 | 978-1449324457 |
create table writers (
authorid varchar2( 4 )
, lname varchar2( 10 )
, fname varchar2( 10 )
, isbn char( 14 )
, title varchar2( 30 ) constraint title_nn not null
, constraint wt_pk primary key ( authorid )
, constraint wt_fk foreign key( isbn ) references books( isbn )
);
INSERT INTO writers
SELECT authorid, fname, lname, isbn, title
FROM author
JOIN bookauthor USING(authorid)
JOIN books USING(isbn);
ORA-00001: unique constraint (FIDDLE_BTLASJPOULJFQQJLAPIO.WT_PK) violated
create table writers2 (
authorid varchar2( 4 )
, lname varchar2( 10 )
, fname varchar2( 10 )
, isbn char( 14 )
, title varchar2( 30 ) constraint title_nn2 not null
, constraint wt_pk2 primary key ( authorid, isbn )
, constraint wt_fk2 foreign key( isbn ) references books( isbn )
);
INSERT INTO writers2
SELECT authorid, fname, lname, isbn, title
FROM author
JOIN bookauthor USING(authorid)
JOIN books USING(isbn);
7 rows affected
select * from writers2 ;
| AUTHORID | LNAME | FNAME | ISBN | TITLE |
|---|---|---|---|---|
| 1 | fname_1 | lname_1 | 978-1449324451 | title_1 |
| 2 | fname_2 | lname_2 | 978-1449324452 | title_2 |
| 3 | fname_3 | lname_3 | 978-1449324453 | title_3 |
| 4 | fname_4 | lname_4 | 978-1449324454 | title_1_4 |
| 4 | fname_4 | lname_4 | 978-1449324455 | title_2_4 |
| 4 | fname_4 | lname_4 | 978-1449324456 | title_3_4 |
| 4 | fname_4 | lname_4 | 978-1449324457 | title_4_4 |