Re: Foreign Keys Problem
On Thu, Nov 19, 2009 at 9:34 PM, Ye Yuan yuan4...@gmail.com wrote: Hi Victor, It looks to me the foreign key syntax is wrong. Can you create the Relationship table on your database by using below ddl? create table if not exists Relationship (ID integer auto_increment primary key, Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID), check (Parent Child) ); Yes, I can create it. Thank you. V
Re: foreign keys: Cannot create InnoDB table
Hi, Martijn, Gavin. SHOW INNODB STATUS gave me helpful messages like following: LATEST FOREIGN KEY ERROR 090821 12:53:18 Error in foreign key constraint of table test_fk/tbl1: FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB: 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/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. TRANSACTIONS and then, I modified columns order, I succeeded to create `test_fk`.`tbl1` on 5.1.31sp1-ent. Thank you! wabi Gavin Towey wrote: Run: SHOW ENGINE INNODB STATUS \G And look for the LATEST FOREIGN KEY ERROR section. It'll explain the reason for the (errno: 150) message. Regards, Gavin Towey -Original Message- From: wabiko.takuma [mailto:wab...@sysrdc.ns-sol.co.jp] Sent: Friday, August 14, 2009 3:35 AM To: mysql@lists.mysql.com Subject: foreign keys: Cannot create InnoDB table Hi, All, I can't create InnoDB table with foreign key constraints using more than 3 colmuns. When I create table `test_fk`.`tbl1`, it gives me: Can't create table 'test_fk.tbl1' (errno: 150) why? CREATE TABLE syntax looks perfectly right to me. Any suggestions are welcome. Thank you, wabi -- -- -- DDL CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( `col1` VARCHAR(2) NOT NULL , `col2` VARCHAR(2) NOT NULL , `col3` VARCHAR(2) NOT NULL , PRIMARY KEY (`col1`, `col2`, `col3`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( `tbl1_id` VARCHAR(12) NOT NULL , `col1` VARCHAR(2) NULL , `col2` VARCHAR(2) NULL , `col3` VARCHAR(2) NULL , PRIMARY KEY (`tbl1_id`) , INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- -- -- mysql Output mysql SELECT VERSION(),NOW() FROM DUAL\G *** 1. row *** VERSION(): 5.1.31sp1-enterprise-gpl-advanced NOW(): 2009-08-14 18:04:00 1 row in set (0.00 sec) mysql DROP DATABASE `test_fk` ; ERROR 1008 (HY000): Can't drop database 'test_fk'; database doesn't exist mysql CREATE DATABASE IF NOT EXISTS `test_fk` ; Query OK, 1 row affected (0.00 sec) mysql SHOW WARNINGS; +---+--+---+ | Level | Code | Message | +---+--+---+ | Error | 1008 | Can't drop database 'test_fk'; database doesn't exist | +---+--+---+ 1 row in set (0.00 sec) mysql USE `test_fk`; Database changed mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl2` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl2' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( - `col1` VARCHAR(2) NOT NULL , - `col2` VARCHAR(2) NOT NULL , - `col3` VARCHAR(2) NOT NULL , - PRIMARY KEY (`col1`, `col2`, `col3`) ) - ENGINE = InnoDB; Query OK, 0 rows affected (0.00 sec) mysql SHOW WARNINGS; Empty set (0.00 sec) mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl1` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl1' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( - `tbl1_id` VARCHAR(12) NOT NULL , - `col1` VARCHAR(2) NULL , - `col2` VARCHAR(2) NULL , - `col3` VARCHAR(2) NULL , - PRIMARY KEY (`tbl1_id`) , - INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , - CONSTRAINT `fk_test` - FOREIGN KEY (`col1` , `col2` , `col3` ) - REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) - ON DELETE NO ACTION - ON UPDATE NO ACTION)
Re: foreign keys: Cannot create InnoDB table
Hi, I can't create InnoDB table with foreign key constraints using more than 3 colmuns. When I create table `test_fk`.`tbl1`, it gives me: Can't create table 'test_fk.tbl1' (errno: 150) why? CREATE TABLE syntax looks perfectly right to me. Any suggestions are welcome. Thank you, wabi -- -- -- DDL CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( `col1` VARCHAR(2) NOT NULL , `col2` VARCHAR(2) NOT NULL , `col3` VARCHAR(2) NOT NULL , PRIMARY KEY (`col1`, `col2`, `col3`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( `tbl1_id` VARCHAR(12) NOT NULL , `col1` VARCHAR(2) NULL , `col2` VARCHAR(2) NULL , `col3` VARCHAR(2) NULL , PRIMARY KEY (`tbl1_id`) , INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; I guess your FK constraint needs the columns in the same order as the PK constraint. That is: col1, col2, col3 in the REFERENCES clause. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: foreign keys: Cannot create InnoDB table
Run: SHOW ENGINE INNODB STATUS \G And look for the LATEST FOREIGN KEY ERROR section. It'll explain the reason for the (errno: 150) message. Regards, Gavin Towey -Original Message- From: wabiko.takuma [mailto:wab...@sysrdc.ns-sol.co.jp] Sent: Friday, August 14, 2009 3:35 AM To: mysql@lists.mysql.com Subject: foreign keys: Cannot create InnoDB table Hi, All, I can't create InnoDB table with foreign key constraints using more than 3 colmuns. When I create table `test_fk`.`tbl1`, it gives me: Can't create table 'test_fk.tbl1' (errno: 150) why? CREATE TABLE syntax looks perfectly right to me. Any suggestions are welcome. Thank you, wabi -- -- -- DDL CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( `col1` VARCHAR(2) NOT NULL , `col2` VARCHAR(2) NOT NULL , `col3` VARCHAR(2) NOT NULL , PRIMARY KEY (`col1`, `col2`, `col3`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( `tbl1_id` VARCHAR(12) NOT NULL , `col1` VARCHAR(2) NULL , `col2` VARCHAR(2) NULL , `col3` VARCHAR(2) NULL , PRIMARY KEY (`tbl1_id`) , INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , CONSTRAINT `fk_test` FOREIGN KEY (`col1` , `col2` , `col3` ) REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- -- -- mysql Output mysql SELECT VERSION(),NOW() FROM DUAL\G *** 1. row *** VERSION(): 5.1.31sp1-enterprise-gpl-advanced NOW(): 2009-08-14 18:04:00 1 row in set (0.00 sec) mysql DROP DATABASE `test_fk` ; ERROR 1008 (HY000): Can't drop database 'test_fk'; database doesn't exist mysql CREATE DATABASE IF NOT EXISTS `test_fk` ; Query OK, 1 row affected (0.00 sec) mysql SHOW WARNINGS; +---+--+---+ | Level | Code | Message | +---+--+---+ | Error | 1008 | Can't drop database 'test_fk'; database doesn't exist | +---+--+---+ 1 row in set (0.00 sec) mysql USE `test_fk`; Database changed mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl2` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl2' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl2` ( - `col1` VARCHAR(2) NOT NULL , - `col2` VARCHAR(2) NOT NULL , - `col3` VARCHAR(2) NOT NULL , - PRIMARY KEY (`col1`, `col2`, `col3`) ) - ENGINE = InnoDB; Query OK, 0 rows affected (0.00 sec) mysql SHOW WARNINGS; Empty set (0.00 sec) mysql mysql DROP TABLE IF EXISTS `test_fk`.`tbl1` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql SHOW WARNINGS; +---+--+--+ | Level | Code | Message | +---+--+--+ | Note | 1051 | Unknown table 'tbl1' | +---+--+--+ 1 row in set (0.00 sec) mysql CREATE TABLE IF NOT EXISTS `test_fk`.`tbl1` ( - `tbl1_id` VARCHAR(12) NOT NULL , - `col1` VARCHAR(2) NULL , - `col2` VARCHAR(2) NULL , - `col3` VARCHAR(2) NULL , - PRIMARY KEY (`tbl1_id`) , - INDEX `fk_test` (`col1` ASC, `col2` ASC, `col3` ASC) , - CONSTRAINT `fk_test` - FOREIGN KEY (`col1` , `col2` , `col3` ) - REFERENCES `test_fk`.`tbl2` (`col1` , `col3` , `col2` ) - ON DELETE NO ACTION - ON UPDATE NO ACTION) - ENGINE = InnoDB; ERROR 1005 (HY000): Can't create table 'test_fk.tbl1' (errno: 150) mysql SHOW WARNINGS; +---+--++ | Level | Code | Message| +---+--++ | Error | 1005 | Can't create table 'test_fk.tbl1' (errno: 150) | +---+--++ 1 row in set (0.00 sec) mysql Bye # perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing
Re: Foreign Keys
Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Foreign Keys
So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com for detailed examples of how this is done, but here is the thumbnail sketch: The table has to have a ParentID column (call it what you want) that points, in the case of a Friend, to the Friend Of Whom UserID column. This simple statement avoids the complexity of the situation where a Friend can have Friends and so on. That problem requires detailed explanation, which you can obtain from the site mentioned above. See also Joe Celko's books. Arthur On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. [EMAIL PROTECTED] wrote: I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK) So if I want a list of USER [EMAIL PROTECTED]'s friends: SELECT friend_emailID from USER_FRIEND WHERE user_emailID=[EMAIL PROTECTED] In this (and pretty much every case), the FRIEND table is useless and doesn't make sense logically. *I THINK I EXPLAINED THINGS INCORRECTLY* Let me try again: * I'm not sure if it's even a true many to many relationship as this is actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD in THE SAME TABLE! A USER is: USER emailID userName A friend is really just another RECORD in the USER table. i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE: USER: emailID userName |1 |many FRIEND: emailID |many |1 USER: emailID username (i.e. it's two records in the same USER table) How are relationships between records in the same table usually dealt with in terms of design? Implementation? ThanX, Ben Jim Lyons [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK) So if I want a list of USER [EMAIL PROTECTED]'s friends: SELECT friend_emailID from USER_FRIEND WHERE user_emailID=[EMAIL PROTECTED] In this (and pretty much every case), the FRIEND table is useless and doesn't make sense logically. *I THINK I EXPLAINED THINGS INCORRECTLY* Let me try again: * I'm not sure if it's even a true many to many relationship as this is actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD in THE SAME TABLE! A USER is: USER emailID userName A friend is really just another RECORD in the USER table. i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE: USER: emailID userName |1 |many FRIEND: emailID |many |1 USER: emailID username (i.e. it's two records in the same USER table) How are relationships between records in the same table usually dealt with in terms of design? Implementation? ThanX, Ben Jim Lyons [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign Keys
So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com http://www.artfulsoftware.com/ for detailed examples of how this is done, but here is the thumbnail sketch: -- Actually a Parent-childREN relationship. A USER can have 0 to many friends. This person can also BE THE friend of 0 to many other USERS. AKA: I (A user) can have many friends (other users), I can be the friend (user) of many users The table has to have a ParentID column (call it what you want) that points, in the case of a Friend, to the Friend Of Whom UserID column -- Not sure what you mean as aren't you describing a 1 to 1 relation? I'll check the site mentioned, thank-you so much! Ben A. Hilleli Programmer / Analyst _ From: Arthur Fuller [mailto:[EMAIL PROTECTED] Sent: October 8, 2008 2:55 PM To: Ben A.H. Cc: mysql@lists.mysql.com Subject: Re: Foreign Keys So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com http://www.artfulsoftware.com/ for detailed examples of how this is done, but here is the thumbnail sketch: The table has to have a ParentID column (call it what you want) that points, in the case of a Friend, to the Friend Of Whom UserID column. This simple statement avoids the complexity of the situation where a Friend can have Friends and so on. That problem requires detailed explanation, which you can obtain from the site mentioned above. See also Joe Celko's books. Arthur On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. [EMAIL PROTECTED] wrote: I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK) So if I want a list of USER [EMAIL PROTECTED]'s friends: SELECT friend_emailID from USER_FRIEND WHERE user_emailID=[EMAIL PROTECTED] In this (and pretty much every case), the FRIEND table is useless and doesn't make sense logically. *I THINK I EXPLAINED THINGS INCORRECTLY* Let me try again: * I'm not sure if it's even a true many to many relationship as this is actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD in THE SAME TABLE! A USER is: USER emailID userName A friend is really just another RECORD in the USER table. i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE: USER: emailID userName |1 |many FRIEND: emailID |many |1 USER: emailID username (i.e. it's two records in the same USER table) How are relationships between records in the same table usually dealt with in terms of design? Implementation? ThanX, Ben Jim Lyons [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com http://www.weblyons.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
I'll check the site mentioned, thank-you so much! http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB/ - / Ben A. Hilleli wrote: So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com http://www.artfulsoftware.com/ for detailed examples of how this is done, but here is the thumbnail sketch: -- /Actually a Parent-childREN relationship... / /A USER can have 0 to many friends. / /This person can also BE THE friend of 0 to many other USERS./ / / /AKA: I (A user) can have many friends (other users), I can be the friend (user) of many users/ The table has to have a ParentID column (call it what you want) that points, in the case of a Friend, to the Friend Of Whom UserID column -- /Not sure what you mean as aren't you describing a 1 to 1 relation?/ / / /I'll check the site mentioned, thank-you so much!/ Ben A. Hilleli *Programmer / Analyst* *From:* Arthur Fuller [mailto:[EMAIL PROTECTED] *Sent:* October 8, 2008 2:55 PM *To:* Ben A.H. *Cc:* mysql@lists.mysql.com *Subject:* Re: Foreign Keys So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com http://www.artfulsoftware.com/ for detailed examples of how this is done, but here is the thumbnail sketch: The table has to have a ParentID column (call it what you want) that points, in the case of a Friend, to the Friend Of Whom UserID column. This simple statement avoids the complexity of the situation where a Friend can have Friends and so on. That problem requires detailed explanation, which you can obtain from the site mentioned above. See also Joe Celko's books. Arthur On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK) So if I want a list of USER [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]'s friends: SELECT friend_emailID from USER_FRIEND WHERE user_emailID=[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] In this (and pretty much every case), the FRIEND table is useless and doesn't make sense logically. *I THINK I EXPLAINED THINGS INCORRECTLY* Let me try again: * I'm not sure if it's even a true many to many relationship as this is actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD in THE SAME TABLE! A USER is: USER emailID userName A friend is really just another RECORD in the USER table. i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE: USER: emailID userName |1 |many FRIEND: emailID |many |1 USER: emailID username (i.e. it's two records in the same USER table) How are relationships between records in the same table usually dealt with in terms of design? Implementation? ThanX, Ben Jim Lyons [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com http://www.weblyons.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM
Re: Foreign Keys
On Wed, Oct 8, 2008 at 11:56 AM, Jim Lyons [EMAIL PROTECTED] wrote: Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs If you add a FOREIGN KEY constraint in MySQL 5+ it adds an index automatically. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
Hi Steffan, all !
Steffan A. Cline wrote:
[[...]]
I am hoping that by using FK based relationships I can just do one massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
AFAIK, this isn't possible.
Foreign keys (aka referential integrity) has a different purpose:
Cross-table (or inter-table) consistency.
If one table refers to (the primary key of) another table, it should be
guaranteed that this reference is an existing value (does point to an
existing record).
If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Using your example:
The purpose of referential integrity is to avoid the case where a
homes record contains a person_id for which there is no people
record, IOW the case of a home whose owner isn't known.
The relation is asymmetric, as indicated by parent and child:
You cannot have a child record without a parent,
but you can have a parent type record without an actual child.
The typical example is customer and order:
You cannot have an order without customer (so you must insert the
customer first, and you must not delete a customer record from your data
while there is an order associated with it),
but you can easily enter a (prospective) customer into your system who
hasn't yet placed an order.
Then I could do something like:
insert into people (firstname, lastname, address) values ('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field and
insert the data into the matching table and auto populate the person_id in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
It isn't possible by using foreign keys, AFAIK.
You could do that by creating an updateable join view: a view which
creates a 1:1 relationship between people and homes on the
person_id column.
Drawbacks:
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each people_id value there
could be only *one* homes record (IOW, you couldn't define both a
town and a summer house).
What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a homes record when you delete the owner's people record
(on delete cascade), or
- prevent you from deleting a people record referencing a homes
record (on delete restrict).
I did not follow how far this is already implemented, depending on the
MySQL version and the table handler.
Regards and HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
Joerg- Your comments below are excellent and my comments are just building
off what you have so clearly stated.
I think Stefan's best bet would be a stored procedure. Pass all the data,
insert the parent record (or make sure the parent record exists ... perhaps
it already is present) and then do an insert into the child table. The PK's
and FK's should be good.
Thanks and have a great day!
Tom
On Mon, Aug 25, 2008 at 2:37 AM, Joerg Bruehe [EMAIL PROTECTED] wrote:
Hi Steffan, all !
Steffan A. Cline wrote:
[[...]]
I am hoping that by using FK based relationships I can just do one massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
AFAIK, this isn't possible.
Foreign keys (aka referential integrity) has a different purpose:
Cross-table (or inter-table) consistency.
If one table refers to (the primary key of) another table, it should be
guaranteed that this reference is an existing value (does point to an
existing record).
If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Using your example:
The purpose of referential integrity is to avoid the case where a homes
record contains a person_id for which there is no people record, IOW the
case of a home whose owner isn't known.
The relation is asymmetric, as indicated by parent and child:
You cannot have a child record without a parent,
but you can have a parent type record without an actual child.
The typical example is customer and order:
You cannot have an order without customer (so you must insert the customer
first, and you must not delete a customer record from your data while there
is an order associated with it),
but you can easily enter a (prospective) customer into your system who
hasn't yet placed an order.
Then I could do something like:
insert into people (firstname, lastname, address) values
('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field
and
insert the data into the matching table and auto populate the person_id in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
It isn't possible by using foreign keys, AFAIK.
You could do that by creating an updateable join view: a view which
creates a 1:1 relationship between people and homes on the person_id
column.
Drawbacks:
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each people_id value there
could be only *one* homes record (IOW, you couldn't define both a
town and a summer house).
What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a homes record when you delete the owner's people record
(on delete cascade), or
- prevent you from deleting a people record referencing a homes
record (on delete restrict).
I did not follow how far this is already implemented, depending on the
MySQL version and the table handler.
Regards and HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
I forgot to mention that the only benefit of a stored procedure would be
minimizing code in your application. you'd have a single call to the stored
procedure... though some people may prefer having the back-to-back insert
statements in their code.
On Mon, Aug 25, 2008 at 11:28 AM, Tom Nugent [EMAIL PROTECTED] wrote:
Joerg- Your comments below are excellent and my comments are just building
off what you have so clearly stated.
I think Stefan's best bet would be a stored procedure. Pass all the data,
insert the parent record (or make sure the parent record exists ... perhaps
it already is present) and then do an insert into the child table. The PK's
and FK's should be good.
Thanks and have a great day!
Tom
On Mon, Aug 25, 2008 at 2:37 AM, Joerg Bruehe [EMAIL PROTECTED] wrote:
Hi Steffan, all !
Steffan A. Cline wrote:
[[...]]
I am hoping that by using FK based relationships I can just do one
massive
insert into the parent table and include all related columns and somehow
magically all field and relational keys fall into place.
AFAIK, this isn't possible.
Foreign keys (aka referential integrity) has a different purpose:
Cross-table (or inter-table) consistency.
If one table refers to (the primary key of) another table, it should be
guaranteed that this reference is an existing value (does point to an
existing record).
If there were no target record, your data were inconsistent.
Foreign keys are meant to prevent such an inconsistency.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
Using your example:
The purpose of referential integrity is to avoid the case where a homes
record contains a person_id for which there is no people record, IOW the
case of a home whose owner isn't known.
The relation is asymmetric, as indicated by parent and child:
You cannot have a child record without a parent,
but you can have a parent type record without an actual child.
The typical example is customer and order:
You cannot have an order without customer (so you must insert the customer
first, and you must not delete a customer record from your data while there
is an order associated with it),
but you can easily enter a (prospective) customer into your system who
hasn't yet placed an order.
Then I could do something like:
insert into people (firstname, lastname, address) values
('xxx','xxx',xxx');
And hopefully due to the FK relationship it would match the proper field
and
insert the data into the matching table and auto populate the person_id
in
the homes table with the corresponding parent row's PK (person_id)
Am I totally off base or is something like this possible?
It isn't possible by using foreign keys, AFAIK.
You could do that by creating an updateable join view: a view which
creates a 1:1 relationship between people and homes on the person_id
column.
Drawbacks:
1) AFAIK, MySQL doesn't yet support this, it is a ToDo item.
2) This would be a 1:1 relationship, for each people_id value there
could be only *one* homes record (IOW, you couldn't define both a
town and a summer house).
What foreign keys are good for:
Depending on how you define the options of the relationship, they would
- delete a homes record when you delete the owner's people record
(on delete cascade), or
- prevent you from deleting a people record referencing a homes
record (on delete restrict).
I did not follow how far this is already implemented, depending on the
MySQL version and the table handler.
Regards and HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
Hi Yves, I have a problem with my foreign keys. I have the following two tables: CREATE TABLE keylist ( KeylistId INTEGER NOT NULL, UserId INTEGER NOT NULL, PRIMARY KEY (KeylistId, UserId)); CREATE TABLE user ( UserId INTEGER NOT NULL PRIMARY KEY, AdditionalKeylist INTEGER); A keylist stores multiple user IDs for each keylist ID. A user has a reference to one keylist to keep multiple additional keys. (My key is the same as a user ID.) Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; This cannot work. The column in KEYLIST to which you are pointing should have a unique value, that means either a primary key or unique constraint. Given that the constraint on KEYLIST means that you can have multiple KEYLIST entries for each USERID value, how is a foreign key constraint supposed to be pointing to a single entry in KEYLIST? It cannot, unless you're referencing a unique (pair) value. What is it exactly that you want to store? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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 non-unique columns (problem)
On 04.11.2007 21:10 CE(S)T, Martijn Tonies wrote: Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; This cannot work. The column in KEYLIST to which you are pointing should have a unique value, that means either a primary key or unique constraint. I know, how I've written further down. Given that the constraint on KEYLIST means that you can have multiple KEYLIST entries for each USERID value, how is a foreign key constraint supposed to be pointing to a single entry in KEYLIST? It cannot, unless you're referencing a unique (pair) value. That's an interesting point. Actually, I'm not referencing a single row, but a single value which can occur multiple times. What is it exactly that you want to store? What I want to store is the reference on a key list ID that really exists. And as soon as the key list ID does not exist any more (i.e. because of the last occurence has been deleted), the reference on that value (not row!) shall be set to NULL. Regarding it this way, my hope to get this done with RDBMS means shrinks... (Sorry for double sending, I hit the Reply button first...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
On 03.11.2007 22:52 CE(S)T, Yves Goergen wrote: Is my design bad? I should explain why I do it this way at all. There's some other tables in my system that need to keep a list of keys (i.e. user IDs) for several actions. A message (one of the tables) has one keylist for read access, one for alter access and a third one for reply access. The lists (if not NULL = empty) contain a list of authorised users to perform the respective action. (If NULL, everybody's allowed.) In an older design, I had a separate table for each list type, which was 6 tables altogether. Instead of a keylist.KeylistId, there was e.g. a MessageReadAccessKeys.MessageId referencing message.MessageId. First, this makes 5 more tables and second, I doubt that it would solve my non-unique foreign key problem. What I need is to store those keys for several tasks, object types and instances of them. What I would like to have is the DBMS keeping those references valid. I guess my last chance is implementing this check in my application (which I already had before I chose to entirely rely on referential integrity and then deleted these few lines). Please tell me if there's a better way. PS: I searched a little more and found out that PostgreSQL also forbids foreign keys referencing non-unique columns (like in SQL92) due to serious bugs in the past, which is one more reason why I don't want to keep it this way. (The whole thing should be somewhat portable...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys
I had that problem as well and a quick search revealed this: A) Whole databases are not handled by InnoDB. The individual tables are. Check the results of SHOW CREATE TABLE table_name_here for each table you are linking from and linking to. You should see ENGINE = InnoDB after the last ) but before the final ; in each definition. If not, you need to change your tables to use that engine. B) Each column participating in either end of a FOREIGN KEY must be of the same data type and size and signedness (everything about the columns must match except the names and comments) C) Each column participating in either end of a FOREIGN KEY must be the leftmost column of an index. Simply declaring a FOREIGN KEY constraint will not automatically create an index on the child table if one is missing. D) When all else fails, read the detailed error message returned by InnoDB by using the SHOW INNODB STATUS; command Basically ensure that your foriegn key is an index in the group_system table. Regards, Olusola On 24/03/06, João Cândido de Souza Neto [EMAIL PROTECTED] wrote: Hello everybody. I'm using php 4.4 and mysql 5.0 and i'm having the bellow issue: I've a group table and a system table, bellow this i have a group_system table that has a foreign key to group table and to system table. When a tried to delete one register in the group_system table, it gives me the bellow error: Cannot add or update a child row: a foreign key constraint fails (`buffets/group_system`, CONSTRAINT `fk_group_group_sistema` FOREIGN KEY (`idgrp`) REFERENCES `group` (`idgrp`) ON DELETE CASCADE ON Anyone knows to tell me wath's happening here? Thanks for all tips. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
בעניין: RE: FOREIGN KEYS
Thanks! Nanu Ing. Edwin Cruz [EMAIL PROTECTED] 10/03/2006 22:06:27 Ive found this on internet: If you re-create a table which was dropped, it has to have a definition which conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated above. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string. Use show create table statement to see table definition (on both)... Regards! -Mensaje original- De: Nanu Kalmanovitz [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 10 de Marzo de 2006 01:35 p.m. Para: mysql@lists.mysql.com Asunto: FOREIGN KEYS Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. I just finished create a new DB called TIULIM (InnoDB) with 3 tables (Sites, Tracks Pathes). Now, using MySQL Query Browser ver. 1.1.15, I'm trying to build FOREIGN KEYS. The above tool is generating the following query: ALTER TABLE `tiulim`.`pathes` ADD CONSTRAINT `FK_pathes_1` FOREIGN KEY `FK_pathes_1` (`Site_ID`) REFERENCES `sites` (`Site_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `FK_pathes_2` FOREIGN KEY `FK_pathes_2` (`Track_ID`) REFERENCES `tracks` (`Track_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; After executing the query, it display the error message: MySQL Error Number 1005 Can't create table './tiulim/#sql-84_169.frm' (errno: 150) What is the problem? How to solve it? TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: FOREIGN KEYS
Nanu Kalmanovitz [EMAIL PROTECTED] wrote on 03/10/2006 02:35:08 PM: Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. I just finished create a new DB called TIULIM (InnoDB) with 3 tables (Sites, Tracks Pathes). Now, using MySQL Query Browser ver. 1.1.15, I'm trying to build FOREIGN KEYS. The above tool is generating the following query: ALTER TABLE `tiulim`.`pathes` ADD CONSTRAINT `FK_pathes_1` FOREIGN KEY `FK_pathes_1` (`Site_ID`) REFERENCES `sites` (`Site_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `FK_pathes_2` FOREIGN KEY `FK_pathes_2` (`Track_ID`) REFERENCES `tracks` (`Track_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; After executing the query, it display the error message: MySQL Error Number 1005 Can't create table './tiulim/#sql-84_169.frm' (errno: 150) What is the problem? How to solve it? TIA Nanu Another FAQ A) Whole databases are not handled by InnoDB. The individual tables are. Check the results of SHOW CREATE TABLE table_name_here for each table you are linking from and linking to. You should see ENGINE = InnoDB after the last ) but before the final ; in each definition. If not, you need to change your tables to use that engine. B) Each column participating in either end of a FOREIGN KEY must be of the same data type and size and signedness (everything about the columns must match except the names and comments) C) Each column participating in either end of a FOREIGN KEY must be the leftmost column of an index. Simply declaring a FOREIGN KEY constraint will not automatically create an index on the child table if one is missing. D) When all else fails, read the detailed error message returned by InnoDB by using the SHOW INNODB STATUS; command More details here: http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: FOREIGN KEYS
I´ve found this on internet: If you re-create a table which was dropped, it has to have a definition which conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated above. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string. Use show create table statement to see table definition (on both)... Regards! -Mensaje original- De: Nanu Kalmanovitz [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 10 de Marzo de 2006 01:35 p.m. Para: mysql@lists.mysql.com Asunto: FOREIGN KEYS Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. I just finished create a new DB called TIULIM (InnoDB) with 3 tables (Sites, Tracks Pathes). Now, using MySQL Query Browser ver. 1.1.15, I'm trying to build FOREIGN KEYS. The above tool is generating the following query: ALTER TABLE `tiulim`.`pathes` ADD CONSTRAINT `FK_pathes_1` FOREIGN KEY `FK_pathes_1` (`Site_ID`) REFERENCES `sites` (`Site_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT, ADD CONSTRAINT `FK_pathes_2` FOREIGN KEY `FK_pathes_2` (`Track_ID`) REFERENCES `tracks` (`Track_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; After executing the query, it display the error message: MySQL Error Number 1005 Can't create table './tiulim/#sql-84_169.frm' (errno: 150) What is the problem? How to solve it? TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys
Nick Hird [EMAIL PROTECTED] wrote on 09/29/2005 05:03:53 PM: Is there a way to have foreign keys and force referential integrity? I have a main table and a lookup table, i want to be able to require that the main table is using values from the lookup table. I am just not sure how in MySQL. Thanks Obviously you are new. Please do not take this the wrong way. We love to help people that are truly stuck, however you seem to be just a bit under informed about what MySQL can do. Please, allow me to help you to RTFM: http://dev.mysql.com/doc/mysql/en/index.html If you look in the index or use the search feature for Foreign Key, you will find what you need. IF you can't understand what you read. Please come back and post another question to the list and I or anyone else will be very glad to help. With respect, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: foreign keys in MyISAM?
Hello. I don't know the date you're asking about. However, you can implement the FKs' features using stored procedures in MySQL 5.0. For a small example have a look at the nice article about stored procedures: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html Jacek Becla wrote: Hi, The documentation says At a later stage, foreign key constraints will be implemented for MyISAM tables as well. Does anybody know what is the timescale? thanks, Jacek -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 in MyISAM?
I don't know the date you're asking about. However, you can implement the FKs' features using stored procedures in MySQL 5.0. For a small example have a look at the nice article about stored procedures: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html Hmmm... given the no-transaction nature of MyISAM, this would probably fail completely :-) Even with transactions, it could easily fail... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys in MyISAM?
On Sep 23, 2005, at 12:27 PM, Jacek Becla wrote: Hi, The documentation says At a later stage, foreign key constraints will be implemented for MyISAM tables as well. Does anybody know what is the timescale? I'm not sure there is a timescale.. I think it might be pretty open ended. You could check out the changelog for MySQL 5 if you're really interested. I wish the MySQL guys would start a blog or something so you could figure out what's going on with their development. I'd like MyISAM to just keep the metadata and not enforce the FKs. That would be pretty nice. Same with INNODB. Kevin -- Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys across databases?
Hello Jake, I would like to use separate databases to group certain related tables to keep things clean, but I would also like to use foreign keys to enforce referential integrity between tables in different databases. I don't think this is possible. Am I right? And if so, am I reduced to using carefully chosen table names to keep things organized? What have others done in this situation? Ehm... According to me, all tables related to eachother or a certain domain should go in 1 database. That is, each database should be able to exist by itself. So, in your case, dump everything into 1 database. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys - No action - Errors
My understanding is that RESTRICT and NO ACTION share the same behavior. http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html * |NO ACTION|: In |ANSI SQL-92| standard, |NO ACTION| means /no action/ in the sense that an attempt to delete or update a primary key value will not be allowed to proceed if there is a related foreign key value in the referenced table (Gruber, Mastering SQL, 2000:181). Starting from 4.0.18 |InnoDB| rejects the delete or update operation for the parent table. * |RESTRICT|: Rejects the delete or update operation for the parent table. |NO ACTION| and |RESTRICT| are the same as omitting the |ON DELETE| or |ON UPDATE| clause. (Some database systems have deferred checks, and |NO ACTION| is a deferred check. In MySQL, foreign key constraints are checked immediately, so |NO ACTION| and |RESTRICT| are the same.) This works only on InnoDB tables as - AFAIK - the InnoDB engine the only engine is which implements FK constraints. on 05/24/2005 07:46 AM Sven Åke Johansson said the following: I have some problem with Foreign Key settings. I use MySQL 4.12, MySQL Query Browser 4.16 and Windows XP. Restrict and Cascade is Ok but when I try to set No action it wont work. Sometimes there is no error message and it seams that the change is saved. But when I check there is no changes. When an error message shows its nr 1005. What is the conditions to set No action. Ok In the manual it says only that No action is taken in the child table when rows are deleted from the parent or values in the referenced columns in the parent table are updated. I read the articles on MySQL , a lot of books and the manual but I cant get any answer. Thanks for any answer wich will solve my problem. Sven Åke Johansson [EMAIL PROTECTED] mailto:[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
Hi, I have a table that with a TEXT filed as a primary key. I can't make a foreign key of this field on another table, why does this happens? Ex: Product --- | ref - text, PK | name - text | ProductsList | ID int, PK | profref - foreign key of the table product | I'm surprised you can have a TEXT column as the primary key... :-) Doesn't this strike you at least as weird? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys......
Tom Roos wrote: hi how does 1 know if a foreign key is defined? describe tablename and show index from tablename dont sufice tks SHOW CREATE TABLE tablename; or SHOW TABLE STATUS LIKE 'tablename'; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys......
Look for a substring beginning with 'FOREIGN KEY' in the result of 'SHOW CREATE TABLE tblname'. PB - Original Message - From: Tom Roos To: [EMAIL PROTECTED] Sent: Thursday, July 08, 2004 10:05 AM Subject: foreign keys.. hi how does 1 know if a foreign key is defined? describe tablename and show index from tablename dont sufice tks Disclaimer http://www.shoprite.co.za/disclaimer.html
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: alea mysql -V mysql Ver 12.22 Distrib 4.0.17, for pc-linux-gnu (i386) but mysql SHOW VARIABLES LIKE have_innodb; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.09 sec) why? the gentoo ebuild configure mysql with innodb support: where i m getting wrong? how can i see a yes working 'value'? Value NO means that MySQL server was configured without InnoDB support. -- 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 help
- snip - Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_innodb; - snip - no, InnoDB is not enabled. how can i enable it? the referece manual show a my.cnf configuration for a machine with at least 2gb of ram and 60 of hard disk. how can i adapt this configuration for an home usage? is this a sufficient condition to emerge innodb tables? tnx a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: - snip - Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_innodb; - snip - no, InnoDB is not enabled. how can i enable it? What version of MySQL do you use? 3.23.xx or 4.0.x? If you use 3.23 you should install MySQL-Max binaries or if you install from source distribution configure MySQL with --have-innodb option. More info you can find at: http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html the referece manual show a my.cnf configuration for a machine with at least 2gb of ram and 60 of hard disk. how can i adapt this configuration for an home usage? For home usage you can use default values. is this a sufficient condition to emerge innodb tables? -- 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
Hi, I am new to mysql. I am trying to create tables with foreign key constraints. but the constraintsdoesnt seem to be showing any effect on the table. I am able to add any info in the foreign key table witout the same info in the main table. what is the problem?? 1) are you running the InnoDB version of MySQL? 2) are you using InnoDB tables? If (1) and (2) are not satisfied, MySQL will parse your SQL and totally ignore everything that has to with Foreign Keys. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign Keys
Can you please post your DDL? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 4/16/04 10:22 AM Subject: Foreign Keys hi, I am new to mysql. I am trying to create tables with foreign key constraints. but the constraintsdoesnt seem to be showing any effect on the table. I am able to add any info in the foreign key table witout the same info in the main table. what is the problem?? Liza -- 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 help
alea mysql -V mysql Ver 12.22 Distrib 4.0.17, for pc-linux-gnu (i386) but mysql SHOW VARIABLES LIKE have_innodb; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | NO| +---+---+ 1 row in set (0.09 sec) why? the gentoo ebuild configure mysql with innodb support: where i m getting wrong? how can i see a yes working 'value'? tnx again -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys help
saiph [EMAIL PROTECTED] wrote: i'm studing foreign keys but my sql code does not react as would imagine when i insert inconsistent data. i.e. create database urls; use urls; create table caths ( name varchar(7) primary key ) type = innodb; create table urls ( name varchar(10) primary key, home varchar(30) unique, cath varchar(7), constraint fk foreign key(cath) references caths(name) on update cascade on delete set null ) type = innodb; load data local infile caths.lst into table caths; load data local infile urls.lst into table urls; why it it possible to insert urls such as: mysql mysql.com dev-null when dev-null in not a value of the attribute caths.name? Because your tables are not InnoDB. Check if InnoDB is enabled: SHOW VARIABLES LIKE have_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 help
Don't foreign keys need to be explicitly indexed? Respectfully, Ligaya Turmelle saiph [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] hi list, i'm studing foreign keys but my sql code does not react as would imagine when i insert inconsistent data. i.e. create database urls; use urls; create table caths ( name varchar(7) primary key ) type = innodb; create table urls ( name varchar(10) primary key, home varchar(30) unique, cath varchar(7), constraint fk foreign key(cath) references caths(name) on update cascade on delete set null ) type = innodb; load data local infile caths.lst into table caths; load data local infile urls.lst into table urls; why it it possible to insert urls such as: mysql mysql.com dev-null when dev-null in not a value of the attribute caths.name? thanks. -- here are more things in heaven and earth, horatio, than are dreamt of in your philosophy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys in CREATE TABLEs produced by mysqldump
Michael, - Alkuperäinen viesti - Lähettäjä: [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Monday, December 29, 2003 11:53 PM Aihe: Re: Foreign Keys in CREATE TABLEs produced by mysqldump Heikki Tuuri [EMAIL PROTECTED] wrote on 12/24/2003 01:53:07 PM: Michael, this is the question where the valid answer is: upgrade! In which version was this corrected? I failed to mark it to the release notes :(. It was fixed in some 4.0.x version = 4.0.12. I'm hesitant to upgrade to the latest and greatest without it being banged on by the masses first.. InnoDB-4.0.17 should be stabler than any of 3.23.xx. It contains more diagnostic code and more checks. I have half a dozen or so business applications running w/ this mysql data store, regression testing them would involve an amount of time I can not afford to spend. The 3.23 branch works well for us right now and I have no real need to use 4.0 (feature-wise.) So until 3.23.x is no longer supported or there is a dire need to use 4.x functionality... I can't make a business case justifying the regression test work. I believe 3.23.58 was the very last release from the 3.23 series. MySQL/InnoDB-3.23.57, June 20, 2003 Changed the default value of innodb_flush_log_at_trx_commit from 0 to 1. If you have not specified it explicitly in your my.cnf, and your application runs much slower with this new release, it is because the value 1 causes a log flush to disk at each transaction commit. Thats it! Thanks. -mike Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys in CREATE TABLEs produced by mysqldump
Heikki Tuuri [EMAIL PROTECTED] wrote on 12/24/2003 01:53:07 PM: Michael, this is the question where the valid answer is: upgrade! In which version was this corrected? I'm hesitant to upgrade to the latest and greatest without it being banged on by the masses first.. I have half a dozen or so business applications running w/ this mysql data store, regression testing them would involve an amount of time I can not afford to spend. The 3.23 branch works well for us right now and I have no real need to use 4.0 (feature-wise.) So until 3.23.x is no longer supported or there is a dire need to use 4.x functionality... I can't make a business case justifying the regression test work. MySQL/InnoDB-3.23.57, June 20, 2003 Changed the default value of innodb_flush_log_at_trx_commit from 0 to 1. If you have not specified it explicitly in your my.cnf, and your application runs much slower with this new release, it is because the value 1 causes a log flush to disk at each transaction commit. Thats it! Thanks. -mike
Re: foreign keys plz?
Hi, Hi there. I heave heared that mysql doesnot support foreign keys? It does, but only with the InnoDB table type. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys plz?
with myIsam? Does not check about integrity? Mysql 3.2? What u will use for your application Innodb or MyISAM? --- Martijn Tonies [EMAIL PROTECTED] wrote: Hi, Hi there. I heave heared that mysql doesnot support foreign keys? It does, but only with the InnoDB table type. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys plz?
* Alaios with myIsam? Does not check about integrity? That is correct, the myisam table handler does not support foreign key constraints. It does of course support foreign keys, but not foreign key constraints, consequently the foreign key itegrity is not checked for myisam tables. Mysql 3.2? (I suppose you meant 3.23) What u will use for your application Innodb or MyISAM? Both? It would depend on your needs. Foreign key constraints is neat, and in some projects it might be a requirement, but the myisam table handler is lighter because it does not do these checks, and it is also not transactional, thus it is faster than InnoDB in many cases. myisam also requires less disk space. Read more about the different table handlers in the manual, and note that you can combine different table types in the same database and in the same statement: URL: http://www.mysql.com/doc/en/Table_types.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Keys in CREATE TABLEs produced by mysqldump
Michael, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, December 24, 2003 1:45 AM Subject: Foreign Keys in CREATE TABLEs produced by mysqldump --=_alternative 008277CD88256E05_= Content-Type: text/plain; charset=US-ASCII First let me state that this is not a question where a valid answer is to SET FOREIGN_KEY_CHECKS=0... (no, its not *that* question..) I have noticed that mysqldump includes the database name in foreign key specifications within CREATE TABLEs. This is causing a bit of grief as I would like to reimport such a dumped data set with a *different* database name (multiple instances of a data set being created for development, qa, and ua purposes..) Is there any way to modify this behaviour? I would prefer not to have to modify a mysqldump'ed file to have it reimported (at all!) this is the question where the valid answer is: upgrade! InnoDB no longer prints the database name in SHOW CREATE TABLE: [EMAIL PROTECTED]:~/mysql-4.0/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.17-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE parent(id INT NOT NULL, - PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.08 sec) mysql CREATE TABLE child(id INT, parent_id INT, - INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) - REFERENCES parent(id) - ON DELETE CASCADE) TYPE=INNODB; Query OK, 0 rows affected (0.02 sec) mysql show create table child; +---+--- --+ | Table | Create Table | +---+--- --+ | child | CREATE TABLE `child` ( `id` int(11) default NULL, `parent_id` int(11) default NULL, KEY `par_ind` (`parent_id`), CONSTRAINT `0_15` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELE TE CASCADE ) TYPE=InnoDB | +---+--- --+ 1 row in set (0.03 sec) mysql In addition, is there a known bug with 3.23.58 where reading bulk queries is very slow? (i.e., mysql FOODB foodb.dump.) Ever since upgrading (on FreeBSD 5.2-RC1) this is horrendously slow. Almost NO cpu time is consumed by any process involved, and both server/client processes are often in state 'S' (sleeping for less than 20 seconds.) This also occurs when using a client running on a linux machine to feed data to the aforementioned mysqld on the FreeBSD host. I'm about to break up my dumps into table-specific files so that I can use LOAD DATA INFILE to help work around this problem. The following may explain this: MySQL/InnoDB-3.23.57, June 20, 2003 Changed the default value of innodb_flush_log_at_trx_commit from 0 to 1. If you have not specified it explicitly in your my.cnf, and your application runs much slower with this new release, it is because the value 1 causes a log flush to disk at each transaction commit. -mike Merry Christmas! Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign keys.
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? 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) -- 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.
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 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.
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? 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/ 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.
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.
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.
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.
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.
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? -- 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.
Hi, I don't know much about the foreign key syntax, but I would think it should reference a field in a different table. If I'm wrong, I'm sorry. :-) Bob -Original Message- From: Mofeed Shahin [mailto:[EMAIL PROTECTED] Sent: Sunday, December 14, 2003 9:56 PM To: Paul DuBois; [EMAIL PROTECTED] Subject: 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] -- 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.
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.
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.
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.
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; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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]
Re: Foreign Keys
Hi there, here a simple sample (basicall taken from the documents) with 3 tables. 2 points to watch out for: 1) first declare the primary keys here (p1.id and p2.id) 2) index the foreign key column here (child.id1 and child.id2) mysql CREATE TABLE p1(id INT PRIMARY KEY)TYPE=INNODB; Query OK, 0 rows affected (0.03 sec) mysql CREATE TABLE p2(id INT PRIMARY KEY)TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE child( - id1 INT, - id2 INT, - INDEX(id1), - INDEX(id2), - FOREIGN KEY (id1) REFERENCES p1(id), - FOREIGN KEY (id2) REFERENCES p2(id))TYPE=InnoDB; Query OK, 0 rows affected (0.01 sec) In you case you haven't indexed IDTr Create table y ( IDTr INT NOT NULL, INDEX (IDTr) constraint FOREIGN KEY IDTr REFERENCES x(ID) )Type=Innodb; That should work. Best regards Nils Valentin Tokyo/Japan 2003 8 12 04:31b b : I am using MYSQL 4. I understand that it allows for foreign keys. Could someone show me an example of how to declare a foriegn key. I tried a combination of statements but I always got a syntax error. Here is what I am trying for example ... Create table x ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, whatever int )Type=Innodb; Create table y ( IDTr INT NOT NULL, constraint FOREIGN KEY IDTr REFERENCES x(ID) )Type=Innodb; How would I create a foreign key linking IDTr to x(ID)? Cheers. Cheers. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign Keys
InnoDB is using a totally different concept than MyISAM. Where in MyISAM all the data is in one file, InnoDB uses the principle of Tablespaces (like ORACLE). This puts the whole tables (and metadata) into one HUGE file (in theory. In practice this file can be split and extends can be defined of how it grows). In general, MyISAM is faster. So if you are developing an application which is logging low level traffic information for telephony calls, use MyISAM. If you develop a normal application, like a webshop, you won't notice the difference. Executive Summary: If you need foreign constraints, use InnoDB. Cheers /rudy -Original Message- From: Andrew Kuebler [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 4:54 To: [EMAIL PROTECTED] Subject: Foreign Keys When I type: Show keys from [tablename]; I do not see foreign keys listed there and I cannot seem to find any command that will list foreign keys. Can anyone help? Also, I notice Innodb tables only store one file unlike MyISAM which stores 3. Are Innodb tables any slower than MyISAM since they store all data in a single file? I am looking to use Innodb for an application simply because of the referential integrity. Thanks in advance... Andrew -- 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 and being FIRST index
http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. Major performance hit would be a guess. Otherwise the database would have to do a table scan. Think about how it would find the related record. I think is a requirement of db2 as well William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - 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: Foreign keys and being FIRST index
On 5 Mar 2003, at 19:39, Daevid Vincent wrote: Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. I think you're misunderstanding something. In the subject line you talk about the first index, and talking about using up the first spot seems to go with that misconception. The documentation you quote says nothing about such a restriction, however. It only says the key must be the first *column* in *an* index, which makes sense (otherwise the index wouldn't be useful for that key alone). [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. -- Dan Nelson [EMAIL PROTECTED] - 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. - 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I If it isn't, then create another index, on just that field. If you have multiple foreign keys, create a separate index, one for each foreign key. You can have multiple indexes on one table with no problems. Maybe MySQL should be modified so that the required indexes are silently created when a FOREIGN KEY clause is processed. am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. If it's only a few records then the index won't take up that much space :) I'm pretty sure the requirement was done to make the InnoDB coders' lives easier. I know there are low-level functions for look up a keyvalue in an index. I don't know if there is one for do a full table scan and search for a value in one field without having to generate a small query and execute it (i.e. subquery, which mysql does not yet support). -- Dan Nelson [EMAIL PROTECTED] - 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: foreign keys across databases?
You can do this. E.g.: FOREIGN KEY (parent_id) REFERENCES databasename.parent(id) and I'm already using this kind of foreign key. Try yourself ! - Original Message - From: Tor R. Skoglund (NextG) [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Sent: Monday, February 24, 2003 4:59 AM Subject: foreign keys across databases? This might be a stupid question, but is it possible to have a foreign key reference to a table.column in another database? E.g.: FOREIGN KEY (parent_id) REFERENCES databasename.parent(id) Probably not, but are there any other ways to enforce such relations? Best regards Tor Rune Skoglund [EMAIL PROTECTED] sql,query,queries,smallint - 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 - 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: Foreign keys
Use table type =Innodb for foreing keys and transacionts...etc and type =myisam for speed Simon -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED]] Sent: 14 January 2003 10:20 To: MySQL Subject: Foreign keys HI all, I've read the following text in a book: ANSI SQL supports a special kind of key called a foreign key. Foreign keys help protect database integrity by enabling the database to manage things such as the deletion of rows with dependent relationships in other tables. Though MySQL supports the ANSI syntax for foreign keys, it does not actually use them to perform integrity checking in the database. This is a situation in which the introduction of a feature would cause a slowdown in performance with little real benefit. Applications themselves should generally worry about foreign key integrity. What does this mean? Does MySQL allow using foreign keys or not? Thanks. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - 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 - 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: Foreign keys
On Tue, Jan 14, 2003 at 12:20:06PM +0200, Octavian Rasnita wrote: I've read the following text in a book: ANSI SQL supports a special kind of key called a foreign key. Foreign keys help protect database integrity by enabling the database to manage things such as the deletion of rows with dependent relationships in other tables. Though MySQL supports the ANSI syntax for foreign keys, it does not actually use them to perform integrity checking in the database. This is a situation in which the introduction of a feature would cause a slowdown in performance with little real benefit. Applications themselves should generally worry about foreign key integrity. What does this mean? This means that the book is out of date. :) Does MySQL allow using foreign keys or not? The InnoDB table type has some support for foreign keys. Read more at http://www.mysql.com/doc/en/SEC449.html Cheers! -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com MySQL Tip: Display the option files read by the server % mysqld --help Email signature rotated by Signify v1.10 http://www.debian.org/ - 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: Foreign keys
Use table type =Innodb for foreing keys and transacionts...etc and type =myisam for speed Yes, but do the foreign keys in InnoDB honor the constraints? I think that was the original question. What's the poing of using foreign keys if it's not going to maintain the integrity of your data. That is the crux of the text that was quoted by the original poster. Chris mysql,query,grr - 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: Foreign keys
Does MySQL allow using foreign keys or not? It does. Try it by yourself with version 4.0.8 +. Adolfo - 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: Foreign Keys and InnoDb in MySQL
Hello, In Case 1, you need to change: index i_Tb (customerId, carId) = index i_Tb (carId, customerId) or add index i_carId (carId) In Case 2, you need : foreign key (customerId) references customerTb (customerId) = foreign key (customerId) references customerTb (customerId) on delete cascade. José Ceferino Ortega -Mensaje original- De: Bruce Lee [mailto:itnobita;hotmail.com] Enviado el: viernes, 15 de noviembre de 2002 4:35 Para: [EMAIL PROTECTED] Asunto: Foreign Keys and InnoDb in MySQL Hello, I have a problem about foreign key in using MySQL.com manual. According to the example in MySQL.com manual, one foreign key is ok. However, I have 3 tables (ie. 2 for parent tables and 1 for child table). When I use 2 or more foreign keys, I have the following errors. Case 1: mysql create table customerTb ( customerId int not null, primary key (customerId) ) type = innodb; mysql insert into customerTb values (1), (2), (3), (4), (5); mysql create table carTb ( carId int not null, primary key (carId) ) type = innodb; mysql insert into carTb values (1), (2), (3), (4), (5); mysql create table buyTb ( customerId int not null, carId int not null, primary key (customerId, carId), index i_Tb (customerId, carId), foreign key (customerId) references customerTb (customerId), foreign key (carId) references carTb (carId) on delete cascade ) type = innodb; ERROR 1005 at line 15: Can't create table '.\ibdata\buytb.frm' (errno: 150) Case 2: mysql create table customerTb ( customerId int not null, primary key (customerId) ) type = innodb; mysql insert into customerTb values (1), (2), (3), (4), (5); mysql create table carTb ( carId int not null, primary key (carId) ) type = innodb; mysql insert into carTb values (1), (2), (3), (4), (5); mysql create table buyTb ( customerId int not null, carId int not null, primary key (customerId, carId), index i_customerTb (customerId), index i_carTb (carId), foreign key (customerId) references customerTb (customerId), foreign key (carId) references carTb (carId) on delete cascade ) type = innodb; mysql insert into buyTb values (1, 1), (1, 2), (1, 3), (2, 3), (4, 3), mysql (4, 4), (4, 5), (5, 5), (5, 1); mysql delete from customerTb where customerId = 1; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails I have thought it for more than three nights, but I still have no solution for this. Can the manual state whether Innodb can support more then one foreign key or not? It makes me so confused. Thanks a lot! Nobita _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail - 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 - 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: Foreign Keys and InnoDb in MySQL
Bruce, Friday, November 15, 2002, 5:35:21 AM, you wrote: BL I have a problem about foreign key in using MySQL.com manual. According to BL the example in MySQL.com manual, one foreign key is ok. However, I have 3 BL tables (ie. 2 for parent tables and 1 for child table). When I use 2 or more BL foreign keys, I have the following errors. BL Case 1: BL mysql create table customerTb ( BL customerId int not null, BL primary key (customerId) BL ) type = innodb; BL mysql insert into customerTb values (1), (2), (3), (4), (5); BL mysql create table carTb ( BL carId int not null, BL primary key (carId) BL ) type = innodb; BL mysql insert into carTb values (1), (2), (3), (4), (5); BL mysql create table buyTb ( BL customerId int not null, BL carId int not null, BL primary key (customerId, carId), BL index i_Tb (customerId, carId), BL foreign key (customerId) references customerTb (customerId), BL foreign key (carId) references carTb (carId) BL on delete cascade BL ) type = innodb; BL ERROR 1005 at line 15: Can't create table '.\ibdata\buytb.frm' (errno: 150) Thats correct, because you must have separate indexes (as you did below). BL Case 2: BL mysql create table customerTb ( BL customerId int not null, BL primary key (customerId) BL ) type = innodb; BL mysql insert into customerTb values (1), (2), (3), (4), (5); BL mysql create table carTb ( BL carId int not null, BL primary key (carId) BL ) type = innodb; BL mysql insert into carTb values (1), (2), (3), (4), (5); BL mysql create table buyTb ( BL customerId int not null, BL carId int not null, BL primary key (customerId, carId), BL index i_customerTb (customerId), BL index i_carTb (carId), BL foreign key (customerId) references customerTb (customerId), BL foreign key (carId) references carTb (carId) BL on delete cascade BL ) type = innodb; BL mysql insert into buyTb values (1, 1), (1, 2), (1, 3), (2, 3), (4, 3), (4, BL 4), (4, 5), (5, 5), (5, 1); BL mysql delete from customerTb where customerId = 1; BL ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Thats correct too. Because you specify ON DELETE CASCADE only for the second foreign key constraint. BL I have thought it for more than three nights, but I still have no solution BL for this. Can the manual state whether Innodb can support more then one BL foreign key or not? It makes me so confused. InnoDB can have more than one foreign key. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Foreign keys in InnoDB tables
Christos, Monday, October 21, 2002, 5:46:07 AM, you wrote: CS Hello I am a brand new user of MySql, and have some questions about CS using InnoDB tables. CS I noticed in the manual that you can only specify CS - ON DELETE SET NULL CS Or CS - ON DELETE CASCADE CS What is the default behaviour if nothing is specified? Nothing will be deleted or set to NULL in the child table :-) But if you have row in child table that has value in the reference column equal value in the parent table, you will get an error. CS Also, does anyone know if there are any plans to support additional CS options? Full foreign key support is in our future plans. CS Finally, when you have many tables with a foreign key referencing a CS table, how does this affect performance of updates on the table being CS referenced? ON UPDATE CASCADE is not supported yet. So, you will get an error or child row will be successfully updated. -- 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: foreign keys
Ilyas, Wednesday, September 04, 2002, 2:27:27 PM, you wrote: IK Is it in Mysql 4 possible to use foreign keys? Foreign key constarints are supported in InnoDB tables (3.23 and 4.0) IK If yes, how can I create two tables with foreign keys and use them? Take a look at: http://www.mysql.com/doc/en/SEC447.html -- 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: Foreign keys vont delete on cascade
# are willing to compromise this safety, #and you are running small transactions, #you may set this to 0 to reduce disk # i/o to the logs. The default value of # this parameter is 0. innodb_flush_log_at_trx_commit=1 #set-variable = innodb_file_io_threads = 4 #set-variable = innodb_lock_wait_timeout = 50 # Plus d'informations sur parametres INNODB http://www.innodb.com/ibman.html # COnfiguration optimisée pour [WinMySQLadmin] Server=C:/mysql/bin/mysqld-nt.exe THANKS HEIKKI :) !! -Message d'origine- De : Heikki Tuuri [mailto:[EMAIL PROTECTED]] Envoyé : jeudi, 8. août 2002 08:17 À : [EMAIL PROTECTED] Objet : Re: Foreign keys vont delete on cascade Alexandre, ON DELETE CASCADE only works starting from 3.23.50 and 4.0.2. The version numbers are misleading because for the InnoDB subsystem it is the date of release which determines how new the InnoDB version is. Thus, for InnoDB, 4.0.1 == 3.23.47. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Alexandre Zglav [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, August 08, 2002 6:00 AM Subject: Foreign keys vont delete on cascade Hi all , Im trying to have innodb to work . I've made my way through the configuration process and passed the creation of tables with primarey key and foreign keys. Im am currently trying to do exactly what is done in the innodb manual so that i'm sure i'm doing it correct :) Here is a very simple log of what i've done . I am working with mysql 4.0.1-alpha-max on a win 2K platform . You will see that ( if every thing is done correctly ) my records in table ' enfant ' should be delete when I try to delete a record from table ' parent' , where the id of table parent is referenced in the enfant ' table ' errrm well never mind lets get to that log . I hope you can help me . Its been a long time since ive been trying to get this stuff to work and I'd really appreciate a tip or two :) See ya all Wmysql CREATE DATABASE famille - ; Query OK, 1 row affected (0.01 sec) mysql CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id)) TYPE=INNODB; ERROR 1046: No Database Selected mysql use famille Database changed mysql CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.09 sec) mysql CREATE TABLE enfant (id INT, parent_id INT, INDEX par_id (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; Query OK, 0 rows affected (0.70 sec) mysql INSERT INTO parent (id , nom) VALUES (1, 'alex'); Query OK, 1 row affected (0.04 sec) mysql INSERT INTO parent (id , nom) VALUES (3, 'tonio'); Query OK, 1 row affected (0.02 sec) mysql INSERT INTO parent (id , nom) VALUES (24, 'poulaga'); Query OK, 1 row affected (0.02 sec) mysql INSERT INTO parent (id , nom) VALUES (22, 'hellscream'); Query OK, 1 row affected (0.02 sec) mysql select * from parent; +++ | id | nom| +++ | 1 | alex | | 3 | tonio | | 22 | hellscream | | 24 | poulaga| +++ 4 rows in set (0.00 sec) mysql insert into enfant ( id, parent_id) VALUES (12, 223): - ; ERROR 1064: You have an error in your SQL syntax near ':' at line 1 mysql insert into enfant ( id, parent_id) VALUES (12, 223); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql insert into enfant ( id, parent_id) VALUES (12, 3); Query OK, 1 row affected (0.04 sec) mysql YEEHAA; ERROR 1064: You have an error in your SQL syntax near 'YEEHAA' at line 1 DOH ! mysql select * from enfant; +--+---+ | id | parent_id | +--+---+ | 12 | 3 | +--+---+ 1 row in set (0.00 sec) mysql insert into enfant ( id, parent_id) VALUES (4, 3); Query OK, 1 row affected (0.02 sec) mysql insert into enfant ( id, parent_id) VALUES (4, 22); Query OK, 1 row affected (0.02 sec) mysql select * from enfant; +--+---+ | id | parent_id | +--+---+ | 12 | 3 | | 14 | 3 | |4 | 3 | |4 | 3 | |4 |22 | +--+---+ 5 rows in set (0.00 sec) mysql DELETE FROM parent WHERE ID = 3; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails !!! mysql DELETE FROM parent WHERE nom = tonio; ERROR 1054: Unknown column 'tonio' in 'where clause' mysql DELETE FROM parent WHERE nom = 'tonio'; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails mysql DELETE FROM parent WHERE nom = 'tonio'; ERROR 1217: Cannot delete a parent
RE: Foreign keys vont delete on cascade
OK IT WORKED FINE :)) !!! I am finally going to be able to work correctly :)) Here is a log of what i did to test : mysql create database fam2 - ; Query OK, 1 row affected (0.00 sec) mysql use fam2 Database changed mysql create table CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id)) TYPE=INNODB; ERROR 1064: You have an error in your SQL syntax near 'CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id)) TYPE=INNOD' at line 1 mysql CREATE TABLE parent (id INT NOT NULL, nom CHAR(10), PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.08 sec) mysql CREATE TABLE enfant (id INT, parent_id INT, INDEX par_id (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON - DELETE CASCADE) TYPE=INNODB; Query OK, 0 rows affected (0.03 sec) mysql INSERT INTO parent (id , nom) VALUES (1, 'alex'); Query OK, 1 row affected (0.03 sec) mysql INSERT INTO parent (id , nom) VALUES (3, 'tonio'); Query OK, 1 row affected (0.03 sec) mysql INSERT INTO parent (id , nom) VALUES (24, 'poulaga'); Query OK, 1 row affected (0.02 sec) mysql INSERT INTO parent (id , nom) VALUES (22, 'hellscream'); Query OK, 1 row affected (0.03 sec) mysql select * from parent; +++ | id | nom| +++ | 1 | alex | | 3 | tonio | | 22 | hellscream | | 24 | poulaga| +++ 4 rows in set (0.02 sec) mysql insert into enfant ( id, parent_id) VALUES (14, 3); Query OK, 1 row affected (0.01 sec) mysql insert into enfant ( id, parent_id) VALUES (14, 546); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql insert into enfant ( id, parent_id) VALUES (234,22); Query OK, 1 row affected (0.03 sec) mysql insert into enfant ( id, parent_id) VALUES (24,22); Query OK, 1 row affected (0.40 sec) mysql insert into enfant ( id, parent_id) VALUES (25,22); Query OK, 1 row affected (0.01 sec) mysql insert into enfant ( id, parent_id) VALUES (1234,22); Query OK, 1 row affected (0.02 sec) mysql insert into enfant ( id, parent_id) VALUES (1234,24); Query OK, 1 row affected (0.02 sec) mysql insert into enfant ( id, parent_id) VALUES (123444,24); Query OK, 1 row affected (0.02 sec) mysql insert into enfant ( id, parent_id) VALUES (1232323,24); Query OK, 1 row affected (0.03 sec) mysql insert into enfant ( id, parent_id) VALUES (122,24); Query OK, 1 row affected (0.02 sec) mysql SELECT * FROM ENFANT; +-+---+ | id | parent_id | +-+---+ | 14 | 3 | | 234 |22 | | 24 |22 | | 25 |22 | |1234 |22 | |1234 |24 | | 123444 |24 | | 1232323 |24 | | 122 |24 | +-+---+ 9 rows in set (0.01 sec) mysql DELETE FROM PARENT WHERE id=22; Query OK, 1 row affected (0.07 sec) mysql SELECT * FROM ENFANT; +-+---+ | id | parent_id | +-+---+ | 14 | 3 | |1234 |24 | | 123444 |24 | | 1232323 |24 | | 122 |24 | +-+---+ 5 rows in set (0.00 sec) mysql On cascade delete was processed correctly . Thats perfect. Thanks again ! -Message d'origine- De : Alexandre Zglav [mailto:[EMAIL PROTECTED]] Envoyé : vendredi, 9. août 2002 19:53 À : Heikki Tuuri; [EMAIL PROTECTED] Objet : RE: Foreign keys vont delete on cascade OK thanks a lot for your help ! :) I just downloaded the latest version ( 4.0.2) and installed it . I hoped I could keep the settings of my ini file ( my.ini) but I got the following error when trying to start in standalone console : Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. C:\cd mysql C:\mysqlcd bin C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_buffer_pool_size = 150M' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_buffer_pool_size = 150M' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_additional_mem_pool_size = 25M' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_log_files_in_group = 3' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_log_file_size = 20M' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_log_buffer_size = 15M' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_file_io_threads = 4' C:\mysql\binmysqld-max --standalone --console mysqld-max: ERROR: unknown variable 'innodb_lock_wait_timeout = 50' C:\mysql\binmysqld-max --standalone --console InnoDB: Error: log file c:\mysql\ibdata\iblogs\ib_logfile0 is of different size InnoDB: than specified in the .cnf file! 020809 19:46:16 Can't init databases C:\mysql\binmysqld-max --standalone --console InnoDB: The first specified data file C:\mysql\ibdata\ibdata1 did
Re: Foreign keys in MySQL
* Hanxue Lee In the MySQL documentation, it is stated that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). This could have been formulated in a better way, IMHO. I would even say the statement is wrong. In RDBMS terminology, a 'foreign key' is one (or more) column(s) in a table identifying records in a different table. Consider this schema: CREATE TABLE owner ( id int primary key, name varchar(30)); CREATE TABLE pet ( id int primary key, owner int, name varchar(30)); The 'owner' field of the 'pet' table is a foreign key if it used to store the primary keys from the 'owner' table. It _is_ a foreign key, even if no foreign key _constraints_ have been defined. The FOREIGN KEY keywords in MySQL is used to define such _constraints_, not to define the foreign key itself. Maybe the phrase in the manual should have been something like: ...the FOREIGN KEY keywords are not used to join tables, but... Does it mean that constraints are not supported? Why did you think that? The statement you quoted says ...not used to join tables... used mostly for... foreign key constraints. Anyway, the answer is 'yes and no', foreign key _constraints_ are supported, but only if you use InnoDB tables. Using the FOREIGN KEY keywords on other table types will not give errors, but it will be ignored. I need to have Update and perhaps Delete Referential Integrity. URL: http://www.mysql.com/doc/S/E/SEC446.html URL: http://www.mysql.com/doc/e/x/example-Foreign_keys.html -- 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
Re: Foreign keys in query optimization
Kiss Dániel wrote: I studied the MySQL and InnoDB manual, but I did not find anything about the internal usage of foreign keys. MySQL does not support foreign keys at the moment. This will be implemented in version 4.1. For more info see: http://www.mysql.com/products/mysql-4.0/index.html Cheers, Harm de Laat Informatiefabriek The Netherlands - 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: Foreign keys in query optimization
If you create in index on your FK then it will be used to optimize the query. =C= * * Cal Evans * Techno-Mage * http://www.calevans.com * - Original Message - From: Kiss Dániel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 10, 2002 9:19 AM Subject: Foreign keys in query optimization I studied the MySQL and InnoDB manual, but I did not find anything about the internal usage of foreign keys. I mean that I would like to know if foreign keys are used for query optimizations or functions like that. Because I think foreign keys should be used not only for keeping the data integrity of the DB. Thanks, Daniel Kiss - 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 - 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: Foreign keys in query optimization
InnoDB supports foreign keys. And, an index must be created on the foreign key; this would help from an optimization point of view. --Walt Weaver Bozeman, Montana -Original Message- From: harm de laat [mailto:[EMAIL PROTECTED]] Sent: Monday, June 10, 2002 8:43 AM To: Kiss Dániel Cc: [EMAIL PROTECTED] Subject: Re: Foreign keys in query optimization Kiss Dániel wrote: I studied the MySQL and InnoDB manual, but I did not find anything about the internal usage of foreign keys. MySQL does not support foreign keys at the moment. This will be implemented in version 4.1. For more info see: http://www.mysql.com/products/mysql-4.0/index.html Cheers, Harm de Laat Informatiefabriek The Netherlands - 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 - 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: Foreign keys in query optimization
Kiss, Monday, June 10, 2002, 5:19:17 PM, you wrote: KD I studied the MySQL and InnoDB manual, but I did not find anything about KD the internal usage of foreign keys. KD I mean that I would like to know if foreign keys are used for query KD optimizations or functions like that. KD Because I think foreign keys should be used not only for keeping the data KD integrity of the DB. Yes, you are right. Foreign keys implement constraints or referential integrity. But you can create foreign key only on indexed column. KD Thanks, KD Daniel Kiss -- 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: Re: Foreign Keys and NULL
Chris, Thursday, May 30, 2002, 1:58:54 AM, you wrote: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; CK OI!!! I'm going to sound like a complete ass now (I know) But since CK when does MySQL support FOREIGN keys?? *gasp* MAN this is going to CK make my life S much easier *curses at phpMyAdmin for not having the CK option* Yes, MySQL has foreign key constraints, but ON DELETE CASCADE is supported since 3.23.50: http://www.mysql.com/doc/S/E/SEC446.html CK *lick* *bounce* *all happy now* *goes to redo all his DBs* CK Now, I like MySQL 10 times more -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: Foreign Keys and NULL
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; OI!!! I'm going to sound like a complete ass now (I know) But since when does MySQL support FOREIGN keys?? *gasp* MAN this is going to make my life S much easier *curses at phpMyAdmin for not having the option* *lick* *bounce* *all happy now* *goes to redo all his DBs* Now, I like MySQL 10 times more - 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: Foreign Keys and NULL
snip tables OI!!! I'm going to sound like a complete ass now (I know) But since when does MySQL support FOREIGN keys?? *gasp* MAN this is going to make my life S much easier *curses at phpMyAdmin for not having the option* Do remember that this is only valid for mysql 3.23.50+ and 4.0.2+. Now, I like MySQL 10 times more Cool. :) -- Gabriel Cain Unix Systems Administrator [EMAIL PROTECTED] Dialup USA, Inc. 888-460-2286 ext 208 - 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: Foreign Keys with MySQL 3.23.49 and 3.23.50
Hi Nothing is wrong! The behaviour of your example is correct, because MySQL does not support the concept of foreign keys. See section 1.4.4.5 of the MySQL manual. In other words data integrity between two tables is not ensured by MySQL. Your application code must solve this. Regards Rene Moonen Jostkleigrewe, Heiner wrote: sql,query Hello, i've tried the foreign key-example out of the documentation(3.5.6 Using Foreign Keys) on W2K (3.23.49 + 3.23.50) and LINUX (3.23.49). The tables persons and shirts are created as innodb-tables. I could insert a shirt for a non-existing person. Also i could delete a person with existing shirts. I have experimented with 'ON DELETE' and 'ON UPDATE' with no effects. What's wrong? Is there a switch to activate foreign keys? Heiner Jostkleigrewe - 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 - 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: Foreign Keys with MySQL 3.23.49 and 3.23.50
This example is in InnoDB though, which should support foreign key constraints. Chris Book In other words data integrity between two tables is not ensured by MySQL. Your application code must solve this. - 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: Foreign keys
Hi! use mysqlSHOW TABLE STATUS FROM yourdatabasename LIKE 'yourtablename'; Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: Marius Røstad [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, April 18, 2002 11:44 AM Subject: Foreign keys Hello! I have a problem: How can I find the FOREIGN KEY definitions for a table? I'm using MySQL 4.0.1 MAX, and I define the table as a InnoDB-table. Someone, pleace help me! Marius - 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 - 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: Foreign keys
Marius, Thursday, April 18, 2002, 11:43:38 AM, you wrote: MR I have a problem: How can I find the FOREIGN KEY definitions for a table? MR I'm using MySQL 4.0.1 MAX, and I define the table as a InnoDB-table. SHOW TABLE STATUS statement shows you foreign key constraints for a table, look at: http://www.mysql.com/doc/S/E/SEC445.html MR Someone, pleace help me! MR Marius -- For technical support contracts, goto https://order.mysql.com/ 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: foreign keys
root, Monday, April 08, 2002, 12:24:42 PM, you wrote: r i tried to use the foreign key feature in mysql 4.0.1 using innodb tables . r then i have tried the delete cascade feature but it didn't work . I don't r know why ? have you any suggestions ? . InnoDB does not yet support ON DELETE CASCADE ... -- For technical support contracts, goto https://order.mysql.com/ 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: Foreign keys in mysqldump?
David, a Solaris version of InnoDB Hot Backup is planned, as well as a FreeBSD version. Currently I am working with beta testers trying to get the Large file support on Linux to work :). Regards, Heikki Tuuri Innobase Oy -Original Message- From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, March 12, 2002 7:18 PM Subject: Re: Foreign keys in mysqldump? InnoDB Hot Backup-0.33 beta is now available for Windows NT/2000/XP and Linux. Any plans for Solaris by the May 2002 release date? From: Heikki Tuuri [EMAIL PROTECTED] The beta testing of InnoDB Hot Backup (a non-free add-on tool) started on Monday. If you want to take part in the beta testing, look at http://www.innodb.com sql David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - 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 - 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: Foreign keys in mysqldump?
Bob, I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Monday, March 11, 2002 8:11 PM Subject: Foreign keys in mysqldump? When I do a mysqldump and include table creation, it does not seem to include the foreign key constraints in the create table statements of the InnoDB tables. Is there a command line switch or other way to make this happen? Thanks, -Bob - 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: foreign keys for relation ships
Hi! The message I sent 5 minutes ago answers also this :). I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: van den Heuvel, Frank [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, March 12, 2002 10:16 AM Subject: foreign keys for relation ships Hello, I am creating tables within MySql 3.23.49. I want to add relation ships between different tables. I tried to do this with foreign key statements. For example: CREATE TABLE domains ( domain varchar(100) NOT NULL, registrarid int(10) NOT NULL, foreign key (registrarid) REFERENCES registrars(registrarid), PRIMARY KEY (domain) ) TYPE=INNODB; CREATE TABLE registrars ( registrarid int(10) unsigned NOT NULL auto_increment, url varchar(200) default NULL, whois varchar(100) default NULL, tld varchar(100) default NULL, PRIMARY KEY (registrarid) ) TYPE=INNODB So the column registrarid in the domain table is a reference to the registrars table(registrarid). When I put this in mysql it doesn't complain about it. Then I use ODBC to link the database to Access. I use Access as my front-end. There I cannot see the relationship. Also when I do a show create table within mysql I cannot see the relationship. Is this the wrong way to do this ? In other words, what is the right way to do this ? Thanks Frank - 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 - 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: Foreign keys in mysqldump?
Heikki, You are the man! I am glad you are addressing these issues in 3.23.50. They will make foreign key support in MySQL much more robust (in my opinion, anyway..) Is the .50 release available yet? If not, do you have a timeline for when it will be, and when it would be recommended to use in production work? Thanks much, -Bob -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 4:50 AM To: Bob McLaughlin; MySQL Mailing List Subject: Re: Foreign keys in mysqldump? Bob, I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Monday, March 11, 2002 8:11 PM Subject: Foreign keys in mysqldump? When I do a mysqldump and include table creation, it does not seem to include the foreign key constraints in the create table statements of the InnoDB tables. Is there a command line switch or other way to make this happen? Thanks, -Bob - 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: Foreign keys in mysqldump?
Bob, thank you! I believe 3.23.50 will be out in two weeks. It is a stable version. The changes in foreign keys were actually rather small. I hope they do not degrade .50 to the beta category. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; MySQL Mailing List [EMAIL PROTECTED] Date: Tuesday, March 12, 2002 4:00 PM Subject: RE: Foreign keys in mysqldump? Heikki, You are the man! I am glad you are addressing these issues in 3.23.50. They will make foreign key support in MySQL much more robust (in my opinion, anyway..) Is the .50 release available yet? If not, do you have a timeline for when it will be, and when it would be recommended to use in production work? Thanks much, -Bob -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 4:50 AM To: Bob McLaughlin; MySQL Mailing List Subject: Re: Foreign keys in mysqldump? Bob, I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Monday, March 11, 2002 8:11 PM Subject: Foreign keys in mysqldump? When I do a mysqldump and include table creation, it does not seem to include the foreign key constraints in the create table statements of the InnoDB tables. Is there a command line switch or other way to make this happen? Thanks, -Bob - 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: Foreign keys in mysqldump?
On Tue, Mar 12, 2002 at 11:50:12AM +0200, Heikki Tuuri wrote: : Bob, : : I have now improved foreign key support so that version 3.23.50 does : : 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this : should also show them in mysqldumps; : 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; : 3. allow backquotes around column and table names in foreign key : definitions: backquotes are produced by SHOW CREATE TABLE; : 4. allow adding a new foreign key constraint ot a table through : ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); : remember though that you first have to create the appropriate indexes on the : parent and child table so that InnoDB approves the constraint. Heikki, That is fantastic news! As far as everyday usage of InnoDB tables goes, those are the number one issues we have to work around. Thanks for taking the time to implement that. Speaking of InnoDB development, what is the status on the InnoDB backup utility? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - 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: Foreign keys in mysqldump?
Philip, -Original Message- From: Philip Molter [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Bob McLaughlin [EMAIL PROTECTED]; MySQL Mailing List [EMAIL PROTECTED] Date: Tuesday, March 12, 2002 4:21 PM Subject: Re: Foreign keys in mysqldump? On Tue, Mar 12, 2002 at 11:50:12AM +0200, Heikki Tuuri wrote: : Bob, : : I have now improved foreign key support so that version 3.23.50 does : : 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this : should also show them in mysqldumps; : 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; : 3. allow backquotes around column and table names in foreign key : definitions: backquotes are produced by SHOW CREATE TABLE; : 4. allow adding a new foreign key constraint ot a table through : ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); : remember though that you first have to create the appropriate indexes on the : parent and child table so that InnoDB approves the constraint. Heikki, That is fantastic news! As far as everyday usage of InnoDB tables goes, those are the number one issues we have to work around. Thanks for taking the time to implement that. thank you! Speaking of InnoDB development, what is the status on the InnoDB backup utility? The beta testing of InnoDB Hot Backup (a non-free add-on tool) started on Monday. If you want to take part in the beta testing, look at http://www.innodb.com * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - 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
