How Do I Create A Table With Constraints While Pulling Data From Another Table

I am trying to create a writers table that contains the author ID, last name, first name, and ISBN and title of the book each author wrote. While using the same data types as the a

Solution 1:

It seems that you need to use a composite key for your WRITERS table. Example (tested with Oracle 12c and 11g, dbfiddle here):

-- 4 authorscreatetable author ( authorid primary key, fname, lname )
asselect1, 'fname_1', 'lname_1'from dual unionallselect2, 'fname_2', 'lname_2'from dual unionallselect3, 'fname_3', 'lname_3'from dual unionallselect4, 'fname_4', 'lname_4'from dual ;

-- 7 bookscreatetable books ( isbn primary key, title )
asselect'978-1449324451', 'title_1'from dual unionallselect'978-1449324452', 'title_2'from dual unionallselect'978-1449324453', 'title_3'from dual unionallselect'978-1449324454', 'title_1_4'from dual unionallselect'978-1449324455', 'title_2_4'from dual unionallselect'978-1449324456', 'title_3_4'from dual unionallselect'978-1449324457', 'title_4_4'from dual ;

-- suppose that 4 books are written by one and the same authorcreatetable bookauthor( authorid, isbn )
asselect A.authorid, B.isbn
from author A 
  join books B on A.authorid = substr( B.title, length( B.title ), 1 ) ;

Add some constraints to the BOOKAUTHOR table, and check its contents:

-- authorid, isbnaltertable 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 )
) ;


SQL>select*from bookauthor;
  AUTHORID ISBN          
---------- --------------1978-14493244512978-14493244523978-14493244534978-14493244544978-14493244554978-14493244564978-1449324457

"Original" DDL code (with minor modifications) -> INSERT fails

createtable writers (
  authorid varchar2( 4 )
, lname varchar2( 10 )
, fname varchar2( 10 )
, isbn char( 14 )
, title varchar2( 30 ) constraint title_nn notnull
, constraint wt_pk primary key ( authorid )
, constraint wt_fk foreign key( isbn ) references books( isbn )
);

INSERTINTO writers
SELECT authorid, fname, lname, isbn, title 
FROM author 
   JOIN bookauthor USING(authorid) 
   JOIN books USING(isbn);
-- ORA-00001: unique constraint (...WT_PK) violated -- author 4 with 4 books!

Suggested DDL code (and testing):

createtable writers2 (
  authorid varchar2( 4 )
, lname varchar2( 10 )
, fname varchar2( 10 )
, isbn char( 14 )
, title varchar2( 30 ) constraint title_nn2 notnull
, constraint wt_pk2 primary key ( authorid, isbn )
, constraint wt_fk2 foreign key( isbn ) references books( isbn )
);

INSERTINTO writers2
SELECT authorid, fname, lname, isbn, title 
FROM author 
   JOIN bookauthor USING(authorid) 
   JOIN books USING(isbn);
-- 7 rows inserted.

SELECTing from WRITERS2:

SQL>select*from writers2 ;

AUTH 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 

Not sure why you would need the WRITERS table, though - as you can generate its data by running a query.

Solution 2:

Maybe try this:

   CREATE TABLEwriters(
        authorid VARCHAR2(4),
        lname VARCHAR2(10),
        fname VARCHAR2(10),
        isbn VARCHAR2(10),
        title VARCHAR2(30) NOT NULL,
        CONSTRAINT wt_pk FOREIGN KEY(authorid) REFERENCES author(authorid),
        CONSTRAINT wt_fk FOREIGN KEY(isbn) REFERENCES books(isbn),
        UNIQUE KEY unique_authorid(authorid)
    );

Just make sure this line references your author table primary key (now it is authorid)

CONSTRAINT wt_pk FOREIGN KEY(authorid) REFERENCES author(authorid),

Also remove unique index if don't need it. And in your example you probably don't need it, becouse one author can have multiple book titles. You should clarify what you trying to accomplish.