Interesting problem with Alter table and foreign keys on 3.23.51
Below is an example of a problem I'm having when issuing an alter table command to create a foreign key in mysql version 3.23.51. I am running the max version and the tables exist in the innodb table space. Thoughts? Comments? Criticism? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 mysql alter table Table2 add constraint foreign key (par_id) references Table1 (id); ERROR 1005: Can't create table './test/#sql-6b2e_f.frm' (errno: 150) mysql show create table Table1\G *** 1. row *** Table: Table1 Create Table: CREATE TABLE `Table1` ( `id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=InnoDB 1 row in set (0.00 sec) mysql show create table Table2\G *** 1. row *** Table: Table2 Create Table: CREATE TABLE `Table2` ( `name` char(10) NOT NULL default '', `par_id` int(11) NOT NULL default '0', PRIMARY KEY (`name`) ) TYPE=InnoDB 1 row in set (0.00 sec) mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Interesting problem with Alter table and foreign keys on 3.23.51
Carl, Tuesday, June 18, 2002, 5:30:31 PM, you wrote: CM Below is an example of a problem I'm having when issuing an alter table CM command to create a foreign key in mysql version 3.23.51. I am running the CM max version and the tables exist in the innodb table space. CM Thoughts? Comments? Criticism? par_id column in the Table2 must be indexed. CM Carl McNamee CM Systems Administrator CM Billing Concepts CM (210) 949-7282 [skip] CM Create Table: CREATE TABLE `Table2` ( CM `name` char(10) NOT NULL default '', CM `par_id` int(11) NOT NULL default '0', CM PRIMARY KEY (`name`) CM ) TYPE=InnoDB CM 1 row in set (0.00 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Interesting Problem
* Yoed Anis Hi... I have an interesting problem I don't know which way to solve. I tried posting this on the PHP site (since I'm coding with PHP and mysql) but they said I might want to try my odds here.. since they suggested I go with the mysql solution, but I'm clueless where to start. So I'll shoot it out to you guys and see what you might offer. I have two databases, say X, and Y: CREATE TABLE X( Id int(11) NOT NULL auto_increment, Dep_Date date, Return_Date date, Cat1_Status varchar(100), Cat2_Status varchar(100), Cat3_Status varchar(100), Cat4_Status varchar(100), PRIMARY KEY (Id)); CREATE TABLE Y( Id int(11) NOT NULL auto_increment, Dep_Date date, Return_Date date, A_Status varchar(100), B_Status varchar(100), C_Status varchar(100), D_Status varchar(100), E_Status varchar(100), PRIMARY KEY (Id)); Now what I am trying to do is get it to display on one page one listing in Chronoligical order based on the Dep_Date from BOTH of these tables. Trying something simple like mysql_query(SELECT Id, Dep_Date, Return_DateFROM X,Y WHERE Dep_Date LIKE '%$SelectDate%' OR Return_Date LIKE '%$SelectDate%' ORDER BY Dep_Date); Will give you a ton of errors, and I'm not very fimilar with JOIN and SQL and how that works. My idea was to create two querys, but the results in somesort of array, and then order the array by date... I was wondering though if this is a good efficient way or if you guys have any better suggestions as to what I should do. You can do it using a temporary table and three separate sql statements: CREATE TEMPORARY TABLE t1 SELECT Id, Dep_Date, Return_date FROM X WHERE Dep_Date LIKE '%$SelectDate%' OR Return_Date LIKE '%$SelectDate%'; INSERT INTO t1 SELECT Id, Dep_Date, Return_date FROM Y WHERE Dep_Date LIKE '%$SelectDate%' OR Return_Date LIKE '%$SelectDate%'; SELECT * FROM t1 ORDER BY Dep_Date; (The temporary table is automatically deleted when the connection is closed.) -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Interesting Problem
Hi... I have an interesting problem I don't know which way to solve. I tried posting this on the PHP site (since I'm coding with PHP and mysql) but they said I might want to try my odds here.. since they suggested I go with the mysql solution, but I'm clueless where to start. So I'll shoot it out to you guys and see what you might offer. I have two databases, say X, and Y: CREATE TABLE X( Id int(11) NOT NULL auto_increment, Dep_Date date, Return_Date date, Cat1_Status varchar(100), Cat2_Status varchar(100), Cat3_Status varchar(100), Cat4_Status varchar(100), PRIMARY KEY (Id)); CREATE TABLE Y( Id int(11) NOT NULL auto_increment, Dep_Date date, Return_Date date, A_Status varchar(100), B_Status varchar(100), C_Status varchar(100), D_Status varchar(100), E_Status varchar(100), PRIMARY KEY (Id)); Now what I am trying to do is get it to display on one page one listing in Chronoligical order based on the Dep_Date from BOTH of these tables. Trying something simple like mysql_query(SELECT Id, Dep_Date, Return_DateFROM X,Y WHERE Dep_Date LIKE '%$SelectDate%' OR Return_Date LIKE '%$SelectDate%' ORDER BY Dep_Date); Will give you a ton of errors, and I'm not very fimilar with JOIN and SQL and how that works. My idea was to create two querys, but the results in somesort of array, and then order the array by date... I was wondering though if this is a good efficient way or if you guys have any better suggestions as to what I should do. Thanks for your time and help, Yoed - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
interesting problem
I have a form on a webpage for a simple trouble ticket system. When requesting a computer be fixed, software be installed, etc...a faculty member can go to this website and type in the info via an HTML form. My problem is, the form needs to be submitted to two different tables. All of the faculty info(name, email, etc) goes into a faculty table. The actual description of the request goes into a request table. But I also need to insert the unique id that is given to the faculty member in the faculty table INTO the request table. Because this is how I've related the two tables. In the request table I have a foreign key(called requested_by) that is the primary key of the faculty table. Is it possible for me to get the primary key of the faculty table and insert it into the request table through the same HTML form? Thanks, chriz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
