Re: "music.sql" with FK from "Learning MySQL" -- 1005 error?

that makes sense except why is that not a problem for flight.sql and
university.sql?

On 3/22/10, Martin Gainty <[email protected]> wrote:
>
> FOREIGN KEY (album_id) REFERENCES album(album_id)whichever value is being
> used for to populate album_id is NOT presently as a row in the album tabl=
e
> (and therefore not in the index)
>
> populate the album record BEFORE inserting / updating the table which
> references album_id thru the FK
>
> Martin Gainty
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugt=
e
> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
> dient lediglich dem Austausch von Informationen und entfaltet keine
> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
> Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAt=
es pas le
> destinataire pr=E9vu, nous te demandons avec bont=E9 que pour satisfaire
> informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e ou la=
 copie
> de ceci est interdite. Ce message sert =E0 l'information seulement et n'a=
ura
> pas n'importe quel effet l=E9galement obligatoire. =C9tant donn=E9 que le=
s email
> peuvent facilement =EAtre sujets =E0 la manipulation, nous ne pouvons acc=
epter
> aucune responsabilit=E9 pour le contenu fourni.
>
>
>
>
>> From: [email protected]
>> Date: Mon, 22 Mar 2010 18:00:27 -0400
>> Subject: "music.sql" with FK from "Learning MySQL" -- 1005 error?
>> To: [email protected]; [email protected]
>>
>> I've been mucking with the O'Reilly book "Learning MySQL" for the
>> obvious purpose of learning MySQL.
>>
>> The book offers two versions of a program:
>>
>> http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key=
_references/music.sql
>> sticks at creating the track table.  However, its sibling programs
>>
>> http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key=
_references/University.sql
>> and
>> http://learningmysql.com/Downloads/Files/Data/SQL_files_with_foreign_key=
_references/Flight.sql
>> work fine, as does the version without foreign key references
>>
>> If this helps explain it, I'm using the latest stable community MySQL
>> on a Dell Optiplex GX620 with 0.5 Gig RAM and Windows XP.
>>
>> The error from "show innodb status" follows. Can anyone clarify what
>> might be causing the problem?
>>
>> 'InnoDB', '', '
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>> 100322 17:30:47 INNODB MONITOR OUTPUT
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>> Per second averages calculated from the last 40 seconds
>> ----------
>> SEMAPHORES
>> ----------
>> OS WAIT ARRAY INFO: reservation count 7, signal count 7
>> Mutex spin waits 0, rounds 70, OS waits 1
>> RW-shared spins 10, OS waits 5; RW-excl spins 1, OS waits 1
>> ------------------------
>> LATEST FOREIGN KEY ERROR
>> ------------------------
>> 100322 17:29:47 Error in foreign key constraint of table music/track:
>> FOREIGN KEY (album_id) REFERENCES album(album_id)
>> ):
>> Cannot find an index in the referenced table where the
>> referenced columns appear as the first columns, or column types
>> in the table and the referenced table do not match for constraint.
>> Note that the internal storage type of ENUM and SET changed in
>> tables created with >=3D InnoDB-4.1.12, and such columns in old tables
>> cannot be referenced by such columns in new tables.
>> See
>> http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.ht=
ml
>> for correct foreign key definition.
>> ------------
>> TRANSACTIONS
>> ------------
>> Trx id counter 0 3373
>> Purge done for trx''s n:o < 0 3370 undo n:o < 0 0
>> History list length 2
>> LIST OF TRANSACTIONS FOR EACH SESSION:
>> ---TRANSACTION 0 0, not started, OS thread id 792
>> MySQL thread id 2, query id 45 localhost root
>> show innodb status
>> --------
>> FILE I/O
>> --------
>> I/O thread 0 state: wait Windows aio (insert buffer thread)
>> I/O thread 1 state: wait Windows aio (log thread)
>> I/O thread 2 state: wait Windows aio (read thread)
>> I/O thread 3 state: wait Windows aio (write thread)
>> Pending normal aio reads: 0, aio writes: 0,
>>  ibuf aio reads: 0, log i/o''s: 0, sync i/o''s: 0
>> Pending flushes (fsync) log: 0; buffer pool: 0
>> 34 OS file reads, 109 OS file writes, 36 OS fsyncs
>> 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
>> -------------------------------------
>> INSERT BUFFER AND ADAPTIVE HASH INDEX
>> -------------------------------------
>> Ibuf: size 1, free list len 0, seg size 2,
>> 0 inserts, 0 merged recs, 0 merges
>> Hash table size 34679, node heap has 1 buffer(s)
>> 0.00 hash searches/s, 0.00 non-hash searches/s
>> ---
>> LOG
>> ---
>> Log sequence number 0 769826
>> Log flushed up to   0 769826
>> Last checkpoint at  0 769826
>> 0 pending log writes, 0 pending chkp writes
>> 29 log i/o''s done, 0.00 log i/o''s/second
>> ----------------------
>> BUFFER POOL AND MEMORY
>> ----------------------
>> Total memory allocated 15522286; in additional pool allocated 1389568
>> Dictionary memory allocated 24888
>> Buffer pool size   512
>> Free buffers       484
>> Database pages     27
>> Modified db pages  0
>> Pending reads 0
>> Pending writes: LRU 0, flush list 0, single page 0
>> Pages read 24, created 3, written 79
>> 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
>> No buffer pool page gets since the last printout
>> --------------
>> ROW OPERATIONS
>> --------------
>> 0 queries inside InnoDB, 0 queries in queue
>> 1 read views open inside InnoDB
>> Main thread id 3936, state: waiting for server activity
>> Number of rows inserted 0, updated 0, deleted 0, read 0
>> 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
>> ----------------------------
>> END OF INNODB MONITOR OUTPUT
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.=
com
>>
>  		 	   	=09
> _________________________________________________________________
> The New Busy is not the old busy. Search, chat and e-mail from your inbox=
.
> http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID27925::T:WLMTAGL=
:ON:WL:en-US:WM_HMP:032010_3


--=20
Due to the recession, requests for instant gratification will be
deferred until arrears in scheduled gratification have been satisfied.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[email protected]