Oracle 11g Release 2

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

AUTHORID	ISBN
1	978-1449324451
2	978-1449324452
3	978-1449324453
4	978-1449324454
4	978-1449324455
4	978-1449324456
4	978-1449324457

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