Re: foreign keys.
On Wed, 17 Dec 2003 07:55 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: mysql CREATE TABLE foo ( - ID INT PRIMARY KEY, - note VARCHAR(50), - First_Name VARCHAR(50), - Last_Name VARCHAR(50), - FOO_ID INT, - INDEX(FOO_ID), - INDEX(First_Name, Last_Name), - FOREIGN KEY (FOO_ID) REFERENCES foo(ID), - FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name) - ) TYPE=INNODB; ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) What is version of MySQL server? What default-character-set do you use? MySQL version == 4.0.15. Charact set == latin1 Mof. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
Hmmm, Thanks for that. But I'm not quite sure what it means. Mof. LATEST FOREIGN KEY ERROR 031218 8:48:23 Error in foreign key constraint of table moftest/foo, FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name)) TYPE=INNODB Cannot resolve column name close to: , Last_Name)) TYPE=INNODB On Thu, 18 Dec 2003 08:02 am, Heikki Tuuri wrote: Mofeed, if you are using a relatively recent version of 4.0, or 4.1.1, please look with SHOW INNODB STATUS\G what is the latest FOREIGN KEY error. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mofeed Shahin [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, December 17, 2003 1:19 AM Subject: Re: foreign keys. mysql CREATE TABLE foo ( - ID INT PRIMARY KEY, - note VARCHAR(50), - First_Name VARCHAR(50), - Last_Name VARCHAR(50), - FOO_ID INT, - INDEX(FOO_ID), - INDEX(First_Name, Last_Name), - FOREIGN KEY (FOO_ID) REFERENCES foo(ID), - FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name) - ) TYPE=INNODB; ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) Mof. On Tue, 16 Dec 2003 07:54 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: Still doesn't work But thanks for trying. Worked fine for me: mysql CREATE TABLE Blah ( - ID INT PRIMARY KEY, - Fname VARCHAR (50), - Lname VARCHAR (50), - UNIQUE (Fname, Lname) - ) TYPE=INNODB; Query OK, 0 rows affected (0.11 sec) mysql CREATE TABLE foo ( - ID INT PRIMARY KEY, - note VARCHAR(50), - Fname VARCHAR(50), - Lname VARCHAR(50), - FOO_ID INT, - INDEX(FOO_ID), - INDEX(Fname, Lname), - FOREIGN KEY (FOO_ID) REFERENCES foo(ID), - FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) - ) TYPE=INNODB; Query OK, 0 rows affected (0.10 sec) Mof. On Tue, 16 Dec 2003 11:40 am, Aftab Jahan Subedar wrote: If you have foreign key then add a key for each, so the CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), KEY(Fname,Lname), #here this one--if it does not work,its not me FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
On Thu, 18 Dec 2003 09:05 am, Heikki Tuuri wrote: Mofeed, - Original Message - From: Mofeed Shahin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 12:20 AM Subject: Re: foreign keys. Hmmm, Thanks for that. But I'm not quite sure what it means. Mof. LATEST FOREIGN KEY ERROR 031218 8:48:23 Error in foreign key constraint of table moftest/foo, FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name)) TYPE=INNODB Cannot resolve column name close to: , Last_Name)) TYPE=INNODB does the table Blah have the columns First_Name and Last_Name? Yep, here are both my create statements : create table Blah ( ID INT PRIMARY KEY, First_Name VARCHAR (50), Last_Name VARCHAR(50), UNIQUE (First_Name, Last_Name) ); Query OK, 0 rows affected (0.00 sec) create table foo ( ID INT PRIMARY KEY, note VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), INDEX(First_Name, Last_Name), FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name) ) TYPE=INNODB; ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) Mof. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
hehehe, Yeah figures that it was something stupid I did!!! Thanks for that. Mof. On Thu, 18 Dec 2003 09:30 am, Heikki Tuuri wrote: Mofeed, - Original Message - From: Mofeed Shahin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 12:47 AM Subject: Re: foreign keys. On Thu, 18 Dec 2003 09:05 am, Heikki Tuuri wrote: Mofeed, - Original Message - From: Mofeed Shahin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 18, 2003 12:20 AM Subject: Re: foreign keys. Hmmm, Thanks for that. But I'm not quite sure what it means. Mof. LATEST FOREIGN KEY ERROR 031218 8:48:23 Error in foreign key constraint of table moftest/foo, FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name)) TYPE=INNODB Cannot resolve column name close to: , Last_Name)) TYPE=INNODB does the table Blah have the columns First_Name and Last_Name? Yep, here are both my create statements : create table Blah ( ID INT PRIMARY KEY, First_Name VARCHAR (50), Last_Name VARCHAR(50), UNIQUE (First_Name, Last_Name) ); Query OK, 0 rows affected (0.00 sec) but the table above is MyISAM type? FOREIGN KEY relationships can only be defined between InnoDB type tables! create table foo ( ID INT PRIMARY KEY, note VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), INDEX(First_Name, Last_Name), FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name) ) TYPE=INNODB; ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) Mof. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
mysql CREATE TABLE foo ( - ID INT PRIMARY KEY, - note VARCHAR(50), - First_Name VARCHAR(50), - Last_Name VARCHAR(50), - FOO_ID INT, - INDEX(FOO_ID), - INDEX(First_Name, Last_Name), - FOREIGN KEY (FOO_ID) REFERENCES foo(ID), - FOREIGN KEY (First_Name, Last_Name) REFERENCES Blah (First_Name, Last_Name) - ) TYPE=INNODB; ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) Mof. On Tue, 16 Dec 2003 07:54 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: Still doesn't work But thanks for trying. Worked fine for me: mysql CREATE TABLE Blah ( - ID INT PRIMARY KEY, - Fname VARCHAR (50), - Lname VARCHAR (50), - UNIQUE (Fname, Lname) - ) TYPE=INNODB; Query OK, 0 rows affected (0.11 sec) mysql CREATE TABLE foo ( - ID INT PRIMARY KEY, - note VARCHAR(50), - Fname VARCHAR(50), - Lname VARCHAR(50), - FOO_ID INT, - INDEX(FOO_ID), - INDEX(Fname, Lname), - FOREIGN KEY (FOO_ID) REFERENCES foo(ID), - FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) - ) TYPE=INNODB; Query OK, 0 rows affected (0.10 sec) Mof. On Tue, 16 Dec 2003 11:40 am, Aftab Jahan Subedar wrote: If you have foreign key then add a key for each, so the CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), KEY(Fname,Lname), #here this one--if it does not work,its not me FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
On Mon, 15 Dec 2003 09:22 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Paul's example works fine for me. What version of MySQL do you use? yeah, sorry Paul's example works here as well. But the actual create statement I'm using here is failing. My create statement is stightly different, and I didn't think it would make a difference (ooops!!). So here is the actual create statement that is failing ; CREATE TABLE Blah ( ID INT PRIMARY KEY, Fname VARCHAR (50), Lname VARCHAR (50), UNIQUE (Fname, Lname) ) TYPE=INNODB; CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; Once again sorry for the confusion. It must be a problem with adding the second foreign key. Mof. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
Still doesn't work But thanks for trying. Mof. On Tue, 16 Dec 2003 11:40 am, Aftab Jahan Subedar wrote: If you have foreign key then add a key for each, so the CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), KEY(Fname,Lname), #here this one--if it does not work,its not me FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; Mofeed Shahin wrote: On Mon, 15 Dec 2003 09:22 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Paul's example works fine for me. What version of MySQL do you use? yeah, sorry Paul's example works here as well. But the actual create statement I'm using here is failing. My create statement is stightly different, and I didn't think it would make a difference (ooops!!). So here is the actual create statement that is failing ; CREATE TABLE Blah ( ID INT PRIMARY KEY, Fname VARCHAR (50), Lname VARCHAR (50), UNIQUE (Fname, Lname) ) TYPE=INNODB; CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; Once again sorry for the confusion. It must be a problem with adding the second foreign key. Mof. -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh http://www.DhakaStockExchangeGame.com/ - [EMAIL PROTECTED] http://www.CEOBangladesh.com/ - [EMAIL PROTECTED] http://www.geocities.com/jahan.geo/ - [EMAIL PROTECTED] sms://+447765341890 tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
Its not my data. I'm simply writting a program that reads in the meta data, and does stuff with it client side. I just need to be able to handle all sorts of stuff, and I wanted to test it on MySQL (amongst others). Mof. On Tue, 16 Dec 2003 11:49 am, Chris Nolan wrote: Hi, Have you considered just normalising the schema a bit more? It looks like you're attempting to duplicate data within the table (which you don't strictly need) and duplicating Fname and Lname between Blah and foo. Why not just have an AUTO_INCREMENT column (or some other unique row identifier) in Blah and reference that? It will help performance and reduce your database size. Regards, Chris Aftab Jahan Subedar wrote: If you have foreign key then add a key for each, so the CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), KEY(Fname,Lname), #here this one--if it does not work,its not me FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; Mofeed Shahin wrote: On Mon, 15 Dec 2003 09:22 pm, Victoria Reznichenko wrote: Mofeed Shahin [EMAIL PROTECTED] wrote: On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Paul's example works fine for me. What version of MySQL do you use? yeah, sorry Paul's example works here as well. But the actual create statement I'm using here is failing. My create statement is stightly different, and I didn't think it would make a difference (ooops!!). So here is the actual create statement that is failing ; CREATE TABLE Blah ( ID INT PRIMARY KEY, Fname VARCHAR (50), Lname VARCHAR (50), UNIQUE (Fname, Lname) ) TYPE=INNODB; CREATE TABLE foo ( ID INT PRIMARY KEY, note VARCHAR(50), Fname VARCHAR(50), Lname VARCHAR(50), FOO_ID INT, INDEX(FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID), FOREIGN KEY (Fname, Lname) REFERENCES Blah (Fname, Lname) ) TYPE=INNODB; Once again sorry for the confusion. It must be a problem with adding the second foreign key. Mof. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
On Mon, 15 Dec 2003 12:42 pm, Paul DuBois wrote: At 11:09 +1030 12/15/03, Mofeed Shahin wrote: I'm trying to create a bunch of tables in MySQL. I'm having problems creating the following table : CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; The error I get is the following : ERROR 1005: Can't create table './moftest/foo.frm' (errno: 150) I found out that errno 150 means that it didn't like the Foreign key constraint. Does MySQL not support this type of Foreign Key constraint ? If does. However, a foreign key must be indexed, and you have declared no index on FOO_ID. Try this: CREATE TABLE foo( ID INT PRIMARY KEY, note VARCHAR(50), FOO_ID INT, INDEX (FOO_ID), FOREIGN KEY (FOO_ID) REFERENCES foo(ID) ) TYPE=INNODB; Thanks, but I just did, and I got the same error message. Mof. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
