There error message are quite straightforward. 1) You tried inserting a value that already exists in the table. 2) You have values in the child table that don't match the parent table, therefore a foreign key cannot be created.

If I have a choice between believing Oracle
>ORA-00001: unique constraint (U1A4CJG5.PK_BOOK) violated
or believing you
>but table BOOK is empty...
I'll believe Oracle every time. Oracle is too dumb to misrepresent reality.
You likely have duplicate rows being returned by the SELECT

I still wonder if there is some dubble index messing... select object_type, object_name from user_objects gave me an unknown index. INDEX SYS_C0056723... and TABLE BIN$UUCSu/cWRSyAuaS0Ls+KdA==$0 How do I find out more information, their relation ?

So, if you're meant to have 54 unique TYPE_IDs, then what that select is telling you is that every single type_id has duplicate records.
Are you absolutely sure that TYPE_ID is meant to be a PK?
If I had a table of books, and didn't want to use a sequence based PK (which you don't seem to), then the natural choice for PK would be ISBN, which is (as far as I know) unique to each book.

I have another table ITEM_OF_INVOICE.isbn, where I forgot to create a FK_constraint to BOOK. I tried to copy ITEM_OF_INVOICE.isbn (foreign key) values to BOOK.ISBN (PK).. then I planned to create a constraint"ISBN refernces BOOK(ISBN)"

INSERT INTO BOOK(ISBN)
SELECT ISBN FROM ITEM_OF_INVOICE
where ITEM_OF_INVOICE.isbn not in (select isbn from book);

You can't create a Foreign key (isbn) unless you have a primary key on ISBN only.

As we've seen that you have multiple records in BOOK for each ISBM, you can't create this foreign key.

If I were you, I'd get your data model sorted out.

Create a table book_type with columns of (isbn, type_id).
Put one record in this table for each type_id that applies to a Book.

That way you can have one record in Book per ISBN, and use ISBM as your primary key.

Honest advice: Bad data models just keep on coming back to haunt you. Almost every bad decision I've made in designing a schema has cost me enough time and effort further down the road to make me wish I'd put in the effort to do it right originally.

I really would sort out your data model.
Unless you've got your Type_id's set up so that there's only ever one category for a book (Do Peter Hamiltons SF Detective novels go under Detectice or SF?) then I think you;ve got a problem that needs fixing.

When I create some tables, format your data up into insert statements and run your query (there's a hint here if you look hard enough), I get a list of 25 isbns that aren't in BOOK. I don't know what you might be doing wrong.

Also - lots of your ISBN numbers are garbage, rather than valid ISBN numbers.

... You must be mixing up the questions and ISBN... (Peter Hamiltons SF Detective novels (??), where did you get that from.. ???
Why not forget the past, and focus on this. I just checked the tables, no double PK's...

I need to insert ISBN though, IOI(ISBN) into table PK BOOK(ISBN)

INSERT INTO book (isbn)
SELECT isbn
FROM IOI
WHERE IOI.isbn not in (select isbn from book);

It's not that I'm mixing up the questions, its more that you have posted several questions based on problems you've encountered in this application, and there is a constant theme of Really Poor Schema Design running through them. I'm trying to help you solve your underlying problems. (I'm assuming that this is a problem you've run into at work - if it's a class assignment, then you REALLY need to be able to do this yourself).

Peter Hamilton is an author whose work I enjoy. Seeing as you've consistently failed to provide any meaningful test data for your problems, prefering to post single columns from tables, or (at best) 2 or 3 rows from tables that we have no table definiton for, I used it by way of an example.

As I said in my previous post

Quote:

When I create some tables, format your data up into insert statements and run your query (there's a hint here if you look hard enough), I get a list of 25 isbns that aren't in BOOK. I don't know what you might be doing wrong.

Thinking about it, it's possible that you are using CHAR data types of different lengths for the ISBN fields, but I don't know (because you've never provided any table definitions)