Is it possible you're in the wrong database (different from
'custtournaments') when you issue this command?
No. I've tried in SQL Browser, EMS SQL Manager, and MySQL Command line,
making sure in each case that I have the karate database selected. I have
also re-typed it in MySQL Command line, eliminating the reference to the
karate database, and got the same error.
I tried the SHOW INNODBSTATUS. I did find something in there that may hint
at the problem:
060719 14:07:40 Error in foreign key constraint of table karate/#sql-304_12:
FOREIGN KEY FK_custpoints_1 (CTID)
REFERENCES custtournaments (ID)
ON DELETE CASCADE
ON UPDATE RESTRICT:
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 >= 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/mysql/en/InnoDB_foreign_key_constraints.html
for correct foreign key definition.
I checked through the page listed above, and my syntax appears to be
correct. It appears to be saying that there is no index present for the ID
field in the custtournaments table. But, I've double-checked, and there IS
an index there.
Foreign keys are a little "foreign" to me at the moment. I understand what
they are, and what they do, however, which file to put this on always causes
me a little problems. Here's the current relationship as I can explain it:
Parent: CustTournaments
Children: CustPoints.
In other words, for 1 CustTournaments record, there could be multiple
CustPoints records. Now, my understanding is that the ForeignKey refers to
a "parent record" in for a Child Database. So, for my purposes, Im saying
that if they delete the CustTournaments record, I want it to delete all of
the CustPoints record. So, I add a foreign key to the CustPoints table
pointing towards it's parent record. I've stored the Parent Key value in
the field CTID in the Child table (custpoints), which points to the ID field
in the parent record (custtournaments).
Is my understanding flawed?
Any more light you can shed on this is appreciated.
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
