Thanks.
On Thu, 21 Jul 2005, Gleb Paharenko wrote:
See:
http://dev.mysql.com/doc/mysql/en/temporary-table-problems.html
this page I have read before, but no mention of this problem.
http://bugs.mysql.com/bug.php?id=10105
now this was right on target.
it lead me to use the database.table(column) as the references clause
value.
mysql> show create table bvolsetparents\G
*************************** 1. row ***************************
Table: bvolsetparents
Create Table: CREATE TEMPORARY TABLE `bvolsetparents` (
`volset` int(11) NOT NULL default '0',
`ancestor` int(11) NOT NULL default '0',
PRIMARY KEY (`volset`,`ancestor`),
KEY `ancestor` (`ancestor`),
CONSTRAINT `#sql50c_2_1_ibfk_1` FOREIGN KEY (`volset`) REFERENCES
`crisfield`.`bvolset` (`id`),
CONSTRAINT `#sql50c_2_1_ibfk_2` FOREIGN KEY (`ancestor`) REFERENCES
`crisfield`.`bvolset` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
but the FK is not enforced...., another day to tacle that.
Jason Pyeron <[EMAIL PROTECTED]> wrote:
any ideas on this? google has not helped.
can temp tables be innodb? can they have fk?
mysql>
mysql> create temporary table bvolsetparents
-> (
-> volset int not null,
-> ancestor int not null,
-> primary key (volset,ancestor),
-> foreign key (volset) REFERENCES bvolset(id),
-> foreign key (ancestor) references bvolset(id)
-> );
ERROR 1005: Can't create table
'c:\docume~1\admini~1\locals~1\temp\#sql640_41_0.frm' (errno: 150)
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
- -
- Jason Pyeron PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218 -
- -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you
have received it in error, purge the message from your system and
notify the sender immediately. Any other use of the email by you
is prohibited.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]