Home » SQL & PL/SQL » SQL & PL/SQL » inserting
inserting [message #187036] Thu, 10 August 2006 10:54 Go to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Hi!
I am trying to insert data from a DB-table and the Foreign key ID's of author, type_of_book, supplier. I get the following...

INSERT INTO book (isbn...)
*
ORA-00001: unique constraint (user.PK_BOOK) violated


INSERT INTO book (isbn, titel, IN_stock, Price, author_id,
type_id,supplier_id)
SELECT
GROSSISTEN.isbn,
GROSSISTEN.title,
GROSSISTEN.IN_store,
GROSSISTEN.price,
author.id,
type_of_book.id,
supplier.id
FROM GROSSISTEN, author, type_of_book, supplier
WHERE GROSSISTEN.author = author.name
AND GROSSISTEN.cathegory =TYPE_of_book.name
AND GROSSISTEN.supplier = supplier.name
and GROSSISTEN.isbn not in (select isbn from book);

then Problem 2...
I tried to add FK to a table where there are double PK, ALTER TABLE ITEM_OF_INVOICE add PRIMARY KEY (INVOICE_NR, ITEM_ID);

ALTER TABLE ITEM_OF_INVOICE
MODIFY ISBN references BOOK (ISBN);
ORA-02298: cannot validate (user.SYS_C0057454) - parent keys not found (????)
Re: inserting [message #187048 is a reply to message #187036] Thu, 10 August 2006 11:55 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
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.

Oracle Concept Manual would be a good reading.
Re: inserting [message #187051 is a reply to message #187048] Thu, 10 August 2006 12:20 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
thanks..
ORA-00001: unique constraint (U1A4CJG5.PK_BOOK) violated

but table BOOK is empty...
Re: inserting [message #187053 is a reply to message #187036] Thu, 10 August 2006 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
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
Re: inserting [message #187064 is a reply to message #187053] Thu, 10 August 2006 13:56 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Could there be trigger or sequence in the FK tables returning duplicate rows? some constraint?



Re: inserting [message #187068 is a reply to message #187064] Thu, 10 August 2006 14:18 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
I dropped PK_BOOK and this is the result... instead of 54 rows 5585... first row dublicated into 8 rows.. but TYPE_ID has a new value on every row. ??
ISBN TITLE IN_STOCK PRICE AUTHOR_ID TYPE_ID SUPPLIER_ID
------------------------------ --------------------------------------------- ---------- ---------- ---------- ---------- -----------
27594920 Den rörliga bildens århundrade 45 60 22 160 1000
27594920 Den rörliga bildens århundrade 45 60 22 150 1000
27594920 Den rörliga bildens århundrade 45 60 22 140 1000
27594920 Den rörliga bildens århundrade 45 60 22 130 1000
27594920 Den rörliga bildens århundrade 45 60 22 110 1000
27594920 Den rörliga bildens århundrade 45 60 22 100 1000
27594920 Den rörliga bildens århundrade 45 60 22 30 1000
27594920 Den rörliga bildens århundrade 45 60 22 10 1000
2707935X Ägande och frihet 17 76 17 170 1000
27077934 Svensk idéhistoria, Del 1 30 89 16 500 1000
27077934 Svensk idéhistoria, Del 1 30 89 16 480 1000
Re: inserting [message #187146 is a reply to message #187068] Fri, 11 August 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
All it takes is one row with a duplicated primary key to get that error.

@Anacedent is right - if the Db is telling you that you've got a duplicate row, then you have a duplicate row.

Run this query:
SELECT type_id
FROM book
HAVING count(*) >1
GROUP BY type_id
Re: inserting [message #187164 is a reply to message #187146] Fri, 11 August 2006 03:17 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
This is the result of the Select:
TYPE_ID
----------
10
20
30
40
50
60
70
80
90
100
110
120
130
140
150
160
170
180
190
200
210
220
230
240
250
260
270
280
290
300
310
320
330
340
350
360
370
380
390
400
410
420
430
440
450
460
470
480
490
500
510
520
530
540

54 rows.

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 ?
Re: inserting [message #187165 is a reply to message #187164] Fri, 11 August 2006 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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.

Quote:

some dubble index messing

Ummm......no idea what this means.
Re: inserting [message #187177 is a reply to message #187165] Fri, 11 August 2006 04:03 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
thanks...
TYPE_ID is a FK! ISBN is the PK...(i deleted PK_ISBN, inserted values and got that strange table)Below are the tables and they look fine to me...

ISBN TITLE IN_STOCK PRICE AUTHOR_ID TYPE_ID SUPPLIER_ID
------------------------------ --------------------------------------------- ---------- ---------- ---------- ---------- -----------
27594920 Den rörliga bildens århundrade 45 60 22 160 1000
27594920 Den rörliga bildens århundrade 45 60 22 150 1000
27594920 Den rörliga bildens århundrade 45 60 22 140 1000


AUTHOR
ID NAME
---------- -----------------
1 Lars Westman
2 Janne Carlsson


TYPE_OF_BOOK
ID NAME
---------- --------------------
10 Historia
20 Konst
30 Historia


SUPPLIER
ID NAME ----------------------------------------
100 Bilda Förlag
200 Bokförlaget Cordia
300 Bilda Förlag


Re: inserting [message #187181 is a reply to message #187177] Fri, 11 August 2006 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Got you.
From your 2nd post I got the mistaken impression that Type_id was your PK.

So, your actual problem would be that the query that you posted in the OP is returning multiple rows for every book?

If that is the case, then given that you have a copy of each type_id for each book, it looks like the query is effectively performing a cartesian join with the TYPE_OF_BOOK table.

The small sample data you post:
Quote:

TYPE_OF_BOOK
ID NAME
---------- --------------------
10 Historia
20 Konst
30 Historia


shows that there are at least 2 rows for the name 'Historia'.

In order to get one row per ISBN from your original query, you need each Name in TYPE_OF_BOOK to match to just one ID
Re: inserting [message #187191 is a reply to message #187177] Fri, 11 August 2006 04:37 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
Off course Smile , my small mistake...
Thank you, you're fantastic !!

I need to create a double PK, but get ORA-02260: table can have only one primary key..
ALTER TABLE ITEM_OF_INVOICE
add PRIMARY KEY (INVOICE_NR, ITEM_ID);
Re: inserting [message #187193 is a reply to message #187191] Fri, 11 August 2006 04:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't have a double primary key.
each primary key record must me unique.

You can have a primary key on more than one field though, so in your case you should be able to have a primary key on (ISBM,TYPE_ID)
Re: inserting.... [message #187209 is a reply to message #187193] Fri, 11 August 2006 05:38 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
so book is ok

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);

0 rows.

or update?
Re: inserting.... [message #187212 is a reply to message #187209] Fri, 11 August 2006 05:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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.
Re: inserting.... [message #187244 is a reply to message #187212] Fri, 11 August 2006 08:26 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
no, please... but my BOOK is sorted out now!!

BOOK
ISBN
------------------------------
1325283
1373008
1385123
1385427
1417964
1484041
27071537
27073459
27073858
27077934
2707823X
27078434
2707935X
27081052
27082938
27089592
27090159
2709300X
27594920
40634868
46175253
46184945
46184961
569836
5986X
70552675
70852278
70852294
71084649
71188762
71188843
71928197
71929290
72090391
72091207
7209155X
72091649
72092149
72092467
72210265
72210362
72211504
72211822
85276731
86424491
86425145
8642517X
86425218
87148555
88524213
89394038
89394062
89394070
97355119

54 rows.


ITEM_OF_INVOICE
ISBN
----------
1385123
1385427
27081052
2709300X
86425218
27077934
72092467
27073459
2707823X
86425218
27073858
569836
72211504
87148555
8642517X
72091207
72210265
72210362
1417964
89394062
27078434
27089592
1484041
46175253
71929290
89394062
89394070
27081052
87148555
7209155X
97355119
8642517X
27081052
2707935X
46175253
46184961

36 rows.


INSERT into BOOK(ISBN)
select ISBN from item_of_invoice
where item_of_invoice.isbn not in (select isbn from BOOK);

0 rows... ??
Re: inserting.... [message #187250 is a reply to message #187244] Fri, 11 August 2006 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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.
Re: inserting.... [message #187296 is a reply to message #187250] Fri, 11 August 2006 13:23 Go to previous messageGo to next message
eqqqqqq
Messages: 30
Registered: August 2006
Member
... 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);

0 rows

How would I avoid to insert double PK's into isbn?
Re: inserting.... [message #187462 is a reply to message #187296] Mon, 14 August 2006 02:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
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)

Try running these two:
SELECT isbn FROM book
MINUS
SELECT isbn FROM ITEM_OF_INVOICE
and
SELECT isbn FROM ITEM_OF_INVOICE
MINUS
SELECT isbn FROM book
Previous Topic: Parallel execution of procedures
Next Topic: On External Table
Goto Forum:
  


Current Time: Fri Dec 09 21:15:00 CST 2016

Total time taken to generate the page: 0.10861 seconds