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
Foreign Keys Problem
Hi;
I don't claim to be an expert in MySQL. The following code was largely
supplied to me by someone who was. I don't really understand foreign keys.
He wrote this off the top of his head, and it's throwing an error. Here's
the python code:
def catTree():
user, passwd, db, host = login()
database = MySQLdb.connect(host, user, passwd, db)
cursor = database.cursor()
cursor.execute('''create table if not exists categories
(ID int(3) unsigned primary key,
Category varchar(40),
Parent varchar(40))''')
cursor.execute('select Category, Parent from categories;')
data = cursor.fetchall()
cursor.execute('select Category from categories order by Parent, ID')
print data
Categories = [itm[0] for itm in cursor] #untuple single column
if len(Categories) 0:
cursor.execute('select Parent from categories order by Parent, ID')
Parents = [itm[0] for itm in cursor]
MAXLEVEL = 15
cursor.execute('''create table if not exists categories
(ID integer auto_increment primary key,
Name varchar(40) not null,
unique (Name)
)''')
cursor.execute('''create table if not exists Relationship
(ID integer auto_increment primary key,
Parent integer not null foreign key references categories (ID),
Child integer not null foreign key references categories (ID),
check (Parent Child) );''')
# get top level
print 'ok'
cursor.execute('select Name from categories order by Name')
theTree = expand(cursor.fetchall())
getChildren(theTree)
connection.commit()
return printTree(theTree)
else:
return ['There are no categories yet.']
Here's the error:
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] from catTree
import catTree
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/var/www/html/angrynates.com/cart/catTree.py, line 74, in ?
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] catTree()
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/var/www/html/angrynates.com/cart/catTree.py, line 59, in catTree
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
cursor.execute('''create table if not exists Relationship
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line 163, in
execute
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
self.errorhandler(self, exc, value)
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
/usr/lib64/python2.4/site-packages/MySQLdb/connections.py, line 35, in
defaulterrorhandler
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] raise
errorclass, errorvalue
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
_mysql_exceptions.ProgrammingError: (1064, You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'foreign key references categories
(ID),\\n Child integer not null foreign key' at line 3)
Please advise.
TIA,
Victor
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
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=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
Foreign Keys
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]
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]
Foreign Keys
I am hoping for a little clarification/education here. My understanding of
how foreign keys work is very minimal.
In using MySQL for the last several years as a backend to web apps I have
always managed relationships amongst the tables with auto increment primary
keys myself.
I have a new project where I need to somehow automate some of that.
Traditionally I have just inserted the parent row into the parent table,
grab the PK and insert this into the child table with the data.
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.
Example:
Parent table - People
Columns - person_id, firstname, lastname
Child table - Homes
Columns - home_id, person_id, address
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?
Thanks
Steffan
---
T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED]
GOOGLE: Steffan.Cline Lasso Partner Alliance Member
---
--
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]
Foreign keys on non-unique columns (problem)
Hi, 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; Which is supposed to mean the following: When I delete a keylist and there's no remaining row with this keylist ID, then find the users that are referencing it and set their AdditionalKeylist value to NULL so that they doesn't keep an invalid reference. The problem: When a keylist ID exists twice and I delete one of them, the user's AdditionalKeylist value is set to NULL immediately, although another keylist ID instance exists. I have read through the MySQL documentation about foreign keys and understand that referencing a non-unique column (i.e. not a candidate key) is not standard SQL and that InnoDB doesn't exactly do what I want (it ignores the remaining relevant rows). From SQLite (which doesn't currently enforce foreign keys) I have learned a trigger that can do the same (haven't tested it yet, though), but to extend it to that look for other instances check, I need it to understand the WHEN part of my trigger, which seems to be commonly known but unsupported (and undocumented) by MySQL 5.0. Also, MySQL requires uncommonly high privileges to create a trigger which is not an option in the field (I'm planning to release my application for use on common web space). I hope you understand my problem. There's two potential solutions which both don't work for me. Is there a third? Can I create this kind of referential integrity on the DBMS level at all? Is my design bad? -- 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]
Retrieving foreign keys and references
Hi: Below is the following relevant create table syntax CREATE Table providers( [snipped] FOREIGN KEY (status) REFERENCES provider_status(ID,title), UNIQUE KEY ID (ID) ) TYPE=MyISAM; and below is the relevant output from a describe query: mysql show columns from providers; +---+---+--+-+++ | Field| Type | Null | Key | Default| Extra +---+---+--+-+++ | status | int(6) | YES || NULL | +---+---+--+-+++ Is there a syntax equivalent of 'describe' that can show me the references? thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
SHOW CREATE TABLE providers\G - Original Message - From: Tim Johnson [EMAIL PROTECTED] To: MySQL General Mailing List mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 2:59:35 AM (GMT-0500) Auto-Detected Subject: Retrieving foreign keys and references Hi: Below is the following relevant create table syntax CREATE Table providers( [snipped] FOREIGN KEY (status) REFERENCES provider_status(ID,title), UNIQUE KEY ID (ID) ) TYPE=MyISAM; and below is the relevant output from a describe query: mysql show columns from providers; +---+---+--+-+++ | Field| Type | Null | Key | Default| Extra +---+---+--+-+++ | status | int(6) | YES || NULL | +---+---+--+-+++ Is there a syntax equivalent of 'describe' that can show me the references? thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- 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: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:01, Rolando Edwards wrote: SHOW CREATE TABLE providers\G Hi Rolando: That doesn't do it on my machine (linux, ver 4.0.2) Here's what I see mysql SHOW CREATE TABLE providers\G *** 1. row *** Table: providers Create Table: CREATE TABLE `providers` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '**', `nick_name` varchar(10) NOT NULL default '**', `email_address` varchar(80) NOT NULL default '**', `start_date` date NOT NULL default '-00-00', `company_ID` int(11) NOT NULL default '0', `status` int(6) default NULL, `modified` timestamp(10) NOT NULL, UNIQUE KEY `ID` (`ID`) ) TYPE=MyISAM 1 row in set (0.00 sec) As you can see there is no reference info for column `status` thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
SHOW INDEXES FROM `providers`; By the way, what version of MySQL are you using ??? - Original Message - From: Tim Johnson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 3:35:46 AM (GMT-0500) Auto-Detected Subject: Re: Retrieving foreign keys and references On Wednesday 21 February 2007 17:01, Rolando Edwards wrote: SHOW CREATE TABLE providers\G Hi Rolando: That doesn't do it on my machine (linux, ver 4.0.2) Here's what I see mysql SHOW CREATE TABLE providers\G *** 1. row *** Table: providers Create Table: CREATE TABLE `providers` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '**', `nick_name` varchar(10) NOT NULL default '**', `email_address` varchar(80) NOT NULL default '**', `start_date` date NOT NULL default '-00-00', `company_ID` int(11) NOT NULL default '0', `status` int(6) default NULL, `modified` timestamp(10) NOT NULL, UNIQUE KEY `ID` (`ID`) ) TYPE=MyISAM 1 row in set (0.00 sec) As you can see there is no reference info for column `status` thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- 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: Retrieving foreign keys and references
Hi Tim, Foreign key definitions are parsed but ignored by MyISAM tables. Try InnoDB or PBXT (http://www.primebase.com/xt) :) On Feb 21, 2007, at 9:35 AM, Tim Johnson wrote: On Wednesday 21 February 2007 17:01, Rolando Edwards wrote: SHOW CREATE TABLE providers\G Hi Rolando: That doesn't do it on my machine (linux, ver 4.0.2) Here's what I see mysql SHOW CREATE TABLE providers\G *** 1. row *** Table: providers Create Table: CREATE TABLE `providers` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(80) NOT NULL default '**', `nick_name` varchar(10) NOT NULL default '**', `email_address` varchar(80) NOT NULL default '**', `start_date` date NOT NULL default '-00-00', `company_ID` int(11) NOT NULL default '0', `status` int(6) default NULL, `modified` timestamp(10) NOT NULL, UNIQUE KEY `ID` (`ID`) ) TYPE=MyISAM 1 row in set (0.00 sec) As you can see there is no reference info for column `status` thanks tim -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:43, Rolando Edwards wrote: SHOW INDEXES FROM `providers`; By the way, what version of MySQL are you using ??? 4.0.20 -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:51, Paul McCullagh wrote: Hi Tim, Hello Paul Foreign key definitions are parsed but ignored by MyISAM tables. Understood. Thanks Try InnoDB or PBXT (http://www.primebase.com/xt) :) for the time being, I'm going to stick with MyISAM. I've got a possible solution I will try later - and the target is a online accounting system for a *very* small company (mine) with a *very* small number of clients. Here we go - it's weird, but it might work. I use a default value, which is itself a foreign key that point to a meta-table example status int(6) default 1, 1 is the ID for a table with columns `ID` 'descriptor`, where the desciptor field holds something like this provider_status.ID.title the script executes the show cols query, then queries the metadata table where provider_status.ID.title tells the script to pull `ID' and `title' from provider_status and load the values into a select/option list form entity. I'm sure that this will not be optimal performance-wise, but where speed is not an issue, will save much coding time. Since it will be the weekend before I try this out, I welcome observations and any references to meta-data approaches using MySQL /MyISAM. Thanks to everybody for the quick responses. regards -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA Alaska Internet Solutions (2 hairy guys in log cabins) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: Hi Frank what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? Yes, if i try to delete or update a record which is referenced by another i get mysql error #1217 , which should be correct. might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. First, i created them without foreign key clauses. After that i added them using 'alter table', but i think this does not matter. Thanks, Nico Hi Nico, the 1217 error means that your foreign key constraints are working, but not necessarily that the action clauses are defined properly (or at all). Anyway, either I don't get what your problem really is, or there is something wrong with 4.1.13. I've tried an on delete clause on 4.1.14 and it worked fine (also using alter table to add the foreign key). Maybe you can upgrade your server? If you want to, I can try the steps you've used to create the tables. Just post the SQL or send it to me. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: hi, are you using the InnoDB storage engine for your tables? yes, i do. example: $ mysqldump -d core groups : CREATE TABLE `groups` ( `id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `parent_id` smallint(5) unsigned default NULL, `setting_` text NOT NULL, `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0', `_ctime` int(10) unsigned NOT NULL default '0', `_mtime` int(10) unsigned NOT NULL default '0', `_uid` smallint(5) unsigned NOT NULL default '0', `_gid` smallint(5) unsigned NOT NULL default '0', `_mod` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `_uid` (`_uid`), KEY `_gid` (`_gid`), KEY `_mod` (`_mod`), KEY `parent_id` (`parent_id`), KEY `r__groups_users_status__id` (`r__groups_users_status__id`), CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups'; As you can see, the foreign keys - statements are included, but without the 'action parts' ( on update,on delete ); thanks, Nico Hi Nico, not sure what is going on here. In a simple test I did, everything was fine as yo can see below. I've also created your groups table (without the references to other tables) and added and on delete clause, which was dumped fine. Could be a bug in 4.1.14 of course. Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? What do you see when you execute show create table groups? Cheers Frank mysqldump -d test child -- MySQL dump 10.9 -- -- Host: 127.0.0.1Database: test -- -- -- Server version 4.1.13 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; [snip] -- -- Table structure for table `child` -- DROP TABLE IF EXISTS `child`; CREATE TABLE `child` ( `parent_id` int(11) NOT NULL default '0', PRIMARY KEY (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Hallo, Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? In the case of a crash i need to restore the dump including the on delete/update clauses. What do you see when you execute show create table groups? CREATE TABLE `groups`( ( the same as mysqldump shows - but without 'on delete/update' ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups' Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: Hallo, Sorry to ask this questions, but are you confident that the action clauses work properly, i.e. the tables were created correctly? In the case of a crash i need to restore the dump including the on delete/update clauses. What do you see when you execute show create table groups? CREATE TABLE `groups`( ( the same as mysqldump shows - but without 'on delete/update' ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups' Thanks, Nico Hi Nico, what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? The fact that show create table does not display them, implies that they might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Hi Frank what I meant was: are you sure that the tables which are in your database are defined correctly, i.e. have the actions? Yes, if i try to delete or update a record which is referenced by another i get mysql error #1217 , which should be correct. might have been lost somewhere. Can you post the statement which has been used to create the groups table in the first place. First, i created them without foreign key clauses. After that i added them using 'alter table', but i think this does not matter. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
Nico Rittner wrote: hello, does anybody now how mysqldump handles the actions for 'on delete' and 'on update' ? In my Version 4.1.14 the action clauses are missing. When reimporting the dump, how does mysql know about the 'action' when altering the table with forein keys. Thanks, Nico Hi Nico, are you using the InnoDB storage engine for your tables? If not, the clauses will not be included in the dump, as only InnoDB supports foreign keys. When you create a non-InnoDB table which has a foreign key specification, MySQL just ignores the specification. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and foreign keys
hi, are you using the InnoDB storage engine for your tables? yes, i do. example: $ mysqldump -d core groups : CREATE TABLE `groups` ( `id` smallint(5) unsigned NOT NULL default '0', `name` varchar(32) NOT NULL default '', `parent_id` smallint(5) unsigned default NULL, `setting_` text NOT NULL, `r__groups_users_status__id` tinyint(3) unsigned NOT NULL default '0', `_ctime` int(10) unsigned NOT NULL default '0', `_mtime` int(10) unsigned NOT NULL default '0', `_uid` smallint(5) unsigned NOT NULL default '0', `_gid` smallint(5) unsigned NOT NULL default '0', `_mod` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `_uid` (`_uid`), KEY `_gid` (`_gid`), KEY `_mod` (`_mod`), KEY `parent_id` (`parent_id`), KEY `r__groups_users_status__id` (`r__groups_users_status__id`), CONSTRAINT `groups_ibfk_10` FOREIGN KEY (`_gid`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_11` FOREIGN KEY (`_uid`) REFERENCES `users` (`id`), CONSTRAINT `groups_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `groups` (`id`), CONSTRAINT `groups_ibfk_8` FOREIGN KEY (`r__groups_users_status__id`) REFERENCES `groups_users_status` (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='groups'; As you can see, the foreign keys - statements are included, but without the 'action parts' ( on update,on delete ); thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and foreign keys
hello, does anybody now how mysqldump handles the actions for 'on delete' and 'on update' ? In my Version 4.1.14 the action clauses are missing. When reimporting the dump, how does mysql know about the 'action' when altering the table with forein keys. Thanks, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do foreign keys affect query speed?
That's the whole question. Do foreign keys (FKs) affect query speed? 'Course the answer could lead to sub-questions grin /, e.g., If so, how best to optimize a query for them? And I guess a corollary question would be whether implementing FKs slows down MySQL processing in general, but that can probably wait for another post. Don't think it really matters for this question, but WinXP SP2, 3G cpu, 1G RAM, PHP v4.3.11, Apache v2.0.55, MySQL v4.1.14, and user v61. Make a good day ... ... barn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do foreign keys affect query speed?
That's the whole question. Do foreign keys (FKs) affect query speed? 'Course the answer could lead to sub-questions grin /, e.g., If so, how best to optimize a query for them? And I guess a corollary question would be whether implementing FKs slows down MySQL processing in general, but that can probably wait for another post. Don't think it really matters for this question, but WinXP SP2, 3G cpu, 1G RAM, PHP v4.3.11, Apache v2.0.55, Make a good day ... ... barn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign keys
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
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: Discussion: the efficiency in using foreign keys
I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 20 Mar 2006, Martijn Tonies wrote: To: mysql@lists.mysql.com From: Martijn Tonies [EMAIL PROTECTED] Subject: Re: Discussion: the efficiency in using foreign keys Hi, This is a fundamental concept in RDBMS: the use of foreign keys in database design. I'd just like to poll the community here, on whether it is a best practice, or practically essential to 'link' related tables by use of foreign keys. For myself, I usually do all the validity checking when adding a new record that references a record id from another table. I understand that this may not be efficient because it becomes 2 database calls (and db calls are expensive in high-load environments). What are the advantages/ disadvantages in using foreign keys? In MySQL, this means one cannot use MyISAM. Do you place a lot of triggers as well? When it comes to referential constraints, the answer is simple: ALWAYS put them on the database. Anyway who answers differently either never had to recover a database that was trashed by the lack of integrity constraints or has no ide what he's talking about. Most probably, this statement will get me tons of e-mail again ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Errors 1005 and 1025 - but not foreign keys
Ah, it was an orphaned table due to a bad shutdown. Thanks. David On Mar 18, 2006, at 12:50 AM, Heikki Tuuri wrote: David, - Original Message - From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 11:31 PM Subject: Errors 1005 and 1025 - but not foreign keys I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. what is the MySQL version? Please post the error messages verbatim. If mysqld prints something to the .err log, please also post the printout. After the failing operation, run SHOW INNODB STATUS\G and post the latest foreign key error explanation in it if any. The issue may be an orphaned table in ibdata1 which does not have an .frm file: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting- datadict.html Or you may have FOREIGN KEY constraints that you are not aware of. Deleting an .ibd file manually from the database directory never helps because the InnoDB internal data dictionary is in ibdata files. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
I've always been a believer in avoiding sql procedures, for the main reason that I want to be as database-independent as possible. I know it is less efficient, but being able to switch between MySQL, Postgre, and the new freebies from IBM, Oracle, and Microsoft is a strong advantage from the business perspective (of total cost to the customer, and customer preference). Of course, this is a discussion point. I'd love to hear from the community on their experiences. [EMAIL PROTECTED] wrote: I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Discussion: the efficiency in using foreign keys
Hi all, This is a fundamental concept in RDBMS: the use of foreign keys in database design. I'd just like to poll the community here, on whether it is a best practice, or practically essential to 'link' related tables by use of foreign keys. For myself, I usually do all the validity checking when adding a new record that references a record id from another table. I understand that this may not be efficient because it becomes 2 database calls (and db calls are expensive in high-load environments). What are the advantages/ disadvantages in using foreign keys? In MySQL, this means one cannot use MyISAM. Do you place a lot of triggers as well? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
IMHO I think you will find that there is a balance between
the speed of opening and reading/writing several related
smaller tables connected by FK's, rather than one mega-sized
gigantic table.
How do you normalise a table without using FK's.
Your right, MySQL does not currently do any checking for
FK's, but this does not mean that you cannot still use them
in MyISAM tables.
Eg.
/* table to store quiz questions */
CREATE TABLE `quiz_question` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`question_text` text NOT NULL,
PRIMARY KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to store quiz answers */
CREATE TABLE `quiz_answer` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`answer_text` text NOT NULL,
`status` enum('wrong', 'right') NOT NULL,
`questionID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to track quiz questions with user answers */
CREATE TABLE `quiz_result` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`user` char(32) NOT NULL default '',
`questionID` mediumint UNSIGNED NOT NULL default '0',
`answerID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`),
KEY `answerID` (`answerID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
So in the quiz_result table above questionID is a
column holding the primary key of each question_text column
in the quiz_question table. It is a foreign key.
answerID is a foreign key that points to the primary
key of the answer submitted by the user doing the quiz.
When the user has finished doing the quiz, the quiz_result
table is scanned for the user session ID, 'user', and then
the question and the user's chosen answer are picked from
the quiz_question and quiz_answer tables, using the foreign
keys in the result table.
I find it helps me to think of foreign keys as unique
pointers to rows in other related tables.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
To: mysql@lists.mysql.com
From: Foo Ji-Haw [EMAIL PROTECTED]
Subject: Discussion: the efficiency in using foreign keys
Hi all,
This is a fundamental concept in RDBMS: the use of foreign keys in
database design.
I'd just like to poll the community here, on whether it is a best
practice, or practically essential to 'link' related tables by use of
foreign keys.
For myself, I usually do all the validity checking when adding a new
record that references a record id from another table. I understand that
this may not be efficient because it becomes 2 database calls (and db
calls are expensive in high-load environments).
What are the advantages/ disadvantages in using foreign keys? In MySQL,
this means one cannot use MyISAM. Do you place a lot of triggers as well?
Thanks.
--
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: Discussion: the efficiency in using foreign keys
Hello Keith,
Thanks for responding. I was actually referring to the subject of using
foreign keys, as opposed to leaving it to the calling application to do
the necessary checks. In particular issues of performance, efficiency
etc. IMHO, foreign keys add 'strictness' to the consistency of related
tables. But for me, I have not bothered to do so, preferring to do the
checking from the application instead.
Appreciate your detailed example on normalising the database, which I
agree with you that it is much preferred against a mega table.
[EMAIL PROTECTED] wrote:
IMHO I think you will find that there is a balance between
the speed of opening and reading/writing several related
smaller tables connected by FK's, rather than one mega-sized
gigantic table.
How do you normalise a table without using FK's.
Your right, MySQL does not currently do any checking for
FK's, but this does not mean that you cannot still use them
in MyISAM tables.
Eg.
/* table to store quiz questions */
CREATE TABLE `quiz_question` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`question_text` text NOT NULL,
PRIMARY KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to store quiz answers */
CREATE TABLE `quiz_answer` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`answer_text` text NOT NULL,
`status` enum('wrong', 'right') NOT NULL,
`questionID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to track quiz questions with user answers */
CREATE TABLE `quiz_result` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`user` char(32) NOT NULL default '',
`questionID` mediumint UNSIGNED NOT NULL default '0',
`answerID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`),
KEY `answerID` (`answerID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
So in the quiz_result table above questionID is a
column holding the primary key of each question_text column
in the quiz_question table. It is a foreign key.
answerID is a foreign key that points to the primary
key of the answer submitted by the user doing the quiz.
When the user has finished doing the quiz, the quiz_result
table is scanned for the user session ID, 'user', and then
the question and the user's chosen answer are picked from
the quiz_question and quiz_answer tables, using the foreign
keys in the result table.
I find it helps me to think of foreign keys as unique
pointers to rows in other related tables.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
To: mysql@lists.mysql.com
From: Foo Ji-Haw [EMAIL PROTECTED]
Subject: Discussion: the efficiency in using foreign keys
Hi all,
This is a fundamental concept in RDBMS: the use of foreign keys in
database design.
I'd just like to poll the community here, on whether it is a best
practice, or practically essential to 'link' related tables by use of
foreign keys.
For myself, I usually do all the validity checking when adding a new
record that references a record id from another table. I understand that
this may not be efficient because it becomes 2 database calls (and db
calls are expensive in high-load environments).
What are the advantages/ disadvantages in using foreign keys? In MySQL,
this means one cannot use MyISAM. Do you place a lot of triggers as well?
Thanks.
--
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: Discussion: the efficiency in using foreign keys
Hi, This is a fundamental concept in RDBMS: the use of foreign keys in database design. I'd just like to poll the community here, on whether it is a best practice, or practically essential to 'link' related tables by use of foreign keys. For myself, I usually do all the validity checking when adding a new record that references a record id from another table. I understand that this may not be efficient because it becomes 2 database calls (and db calls are expensive in high-load environments). What are the advantages/ disadvantages in using foreign keys? In MySQL, this means one cannot use MyISAM. Do you place a lot of triggers as well? When it comes to referential constraints, the answer is simple: ALWAYS put them on the database. Anyway who answers differently either never had to recover a database that was trashed by the lack of integrity constraints or has no ide what he's talking about. Most probably, this statement will get me tons of e-mail again ;-) Martijn Tonies Database Workbench - development tool for MySQL, and more! 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: Errors 1005 and 1025 - but not foreign keys
David, - Original Message - From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 16, 2006 11:31 PM Subject: Errors 1005 and 1025 - but not foreign keys I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. what is the MySQL version? Please post the error messages verbatim. If mysqld prints something to the .err log, please also post the printout. After the failing operation, run SHOW INNODB STATUS\G and post the latest foreign key error explanation in it if any. The issue may be an orphaned table in ibdata1 which does not have an .frm file: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html Or you may have FOREIGN KEY constraints that you are not aware of. Deleting an .ibd file manually from the database directory never helps because the InnoDB internal data dictionary is in ibdata files. How can I get rid of this mess? Thanks. David Best regards, Heikki Oracle Corp./Innobase Oy 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 http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Errors 1005 and 1025 - but not foreign keys
I got an error 1025 trying to rename an InnoDB table. When I go to look in the database now, that table isn't there even though there is a .ibd file in the mysql data directory with the target name. If I try to create a table with the target name (as an InnoDB table), I get error 1005. I can create it as MyISAM, however. If I try to then convert that MyISAM table to InnoDB, I get the 1025 error. I tried removing the .ibd file from the mysql data dir and that did not help. In googling the error, it seems all solutions revolve around foreign keys, but there are no foreign keys in this table nor are there any foreign keys referencing this table. How can I get rid of this mess? Thanks. David -- 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]
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
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]
Insert fails with Foreign Keys
I'm experimenting with Foreign Keys to maintain referential integrity. I have just added a Foreign Key to one of my tables, but now when I attempt to add a new record, I get this error: #23000Cannot add or update a child row: a foreign key constraint fails (`fccamp/families`, CONSTRAINT `FK_Campers` FOREIGN KEY (`ID`) REFERENCES `campers` (`FamilyID`) ON DELETE CASCADE ON UPDATE CASCADE) Did I get something backwards here? Does a child record have to exist before I can add a parent record? I thought that a Foreign key referred to a child record? In other words, the Foreign component is the child table. Is that not right? If it is, how do I add records? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert fails with Foreign Keys
Jesse [EMAIL PROTECTED] wrote on 12/22/2005 05:09:12 PM: I'm experimenting with Foreign Keys to maintain referential integrity. I have just added a Foreign Key to one of my tables, but now when I attempt to add a new record, I get this error: #23000Cannot add or update a child row: a foreign key constraint fails (`fccamp/families`, CONSTRAINT `FK_Campers` FOREIGN KEY (`ID`) REFERENCES `campers` (`FamilyID`) ON DELETE CASCADE ON UPDATE CASCADE) Did I get something backwards here? Does a child record have to exist before I can add a parent record? I thought that a Foreign key referred to a child record? In other words, the Foreign component is the child table. Is that not right? If it is, how do I add records? Thanks, Jesse You have to have the parent record first. The FK ensures that the value you add to the child table is one of the valid values listed in the parent table. So with the key you defined, you would have to have a record in the `campers` table before you could create a `family` record. I think you defined it backwards because the constraint should be defined on the child table, not the parent table. I think it should have been defined on the `camper` table like this ...FOREIGN KEY (`familyID`) references family(`id`)... Hey, I did it too (once a lng time ago). Don't feel bad. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Can foreign keys reference tables in another database?
I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can foreign keys reference tables in another database?
JamesDR wrote: Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D I just did a quick test on my database server and it looks like you can have a foreign key that references tables in another database. Using your example databases and tables, you can create the foreign key by specifying the database name in the REFERENCES clause like this: ALTER TABLE Application.tblAddress ADD FOREIGN KEY (State) REFERENCES *Common*.tblState (State); Eric
Re: Can foreign keys reference tables in another database?
Eric Grau wrote: JamesDR wrote: Scott Plumlee wrote: sol beach wrote: Why in the world are you trying to keep multiple copies of the data? Why can't you just access the 'master database'? On 12/16/05, Scott Plumlee [EMAIL PROTECTED] wrote: I am trying to maintain one master list of countries and the states within those countries. I'd like to keep this info in a common database, to be used by 2 or three other databases on the same host, using foreign keys to make sure that all the country/state data matches up to the master list. If I can't use foreign keys, should I just run regular updates to sync the data between duplicate tables of countries and states in each database, or is there an better method that I'm not seeing? Sorry, I didn't explain very well. I have a db I'll call Common. In there, I'd like to keep two tables, tblCountry and tblState. Then I've got another database, called Application, with a table called tblAddress. In the Application database, I'd like to reference tblCountry and tblStateProvince. When someone fills out form info that is going to be stored in tblAddress, I want the choices to be pulled from tblCountry and tblStateProvince. When I've done this inside only one database, I've always used foreign keys between the Address.State field and the tblState.State fields. My goal IS to only keep one set of country and state data for multiple databases. But I don't know if I can do that using foreign keys when things are in two different databases - it doesn't appear that I can. So my question is am I better off NOT using foreign keys but doing everything else the same or using foreign keys and having to jerryrig some sort of syncing? If your app is just selecting data and inserting it into fields. Then have it do something like: SELECT `ID`,`Stateabv` FROM `Common`.`states` ORDER BY `Stateabv`; Fields, then when the user saves the data, the ID is written to the other DB, no syncing needed. I've done something like this in another app. I'm not sure how foreign keys will work (if they will) but I *think* you can do a join on this -- tho now we are beyond the scope of my knowledge :-D I just did a quick test on my database server and it looks like you can have a foreign key that references tables in another database. Using your example databases and tables, you can create the foreign key by specifying the database name in the REFERENCES clause like this: ALTER TABLE Application.tblAddress ADD FOREIGN KEY (State) REFERENCES *Common*.tblState (State); Eric Of course without the *. I was trying to make it bold and didn't realize the mailing list would add them. Hope this helps, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
--- Kishore Jalleda [EMAIL PROTECTED] wrote: check the permissions on the mysql data dir, may be the user mysql or who ever runs mysql does not have sufficient privileges Kishore Jalleda I agree with Kishore, to elaborate his answer a little longer I would ask the following. - is the hardware all ok? - You said you created the table before, so u must had have access to the directories from the OS prespective, did you use the same method to connect to the server at the time of creation and at the time to alter it? - did u alter the tablespaces location? the configuration files? I am not 100% familiar with the Windows OS but I would expect the full path to the files not a relative one in the error message. - can u select anything from the different tables? - did you use the same userid, passsword and connection method to create the database and to try to alter it? Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's ||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson || __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
ps, I meant to delete the note from my draft folder... sorry Enrique Sanchez. Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's ||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson || __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to list foreign keys
Hello. Have you tried 'SHOW CREATE TABLE'? See: http://dev.mysql.com/doc/mysql/en/show-create-table.html HI everybody I'm tryng to find a way to know if a field is a foreign key, by example if I run this describe tablename; in the Key colum I got PRI for the primary key field, somebody know a way to get the foreign keys ? Operator [EMAIL PROTECTED] wrote: -- 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]
how to list foreign keys
HI everybody I'm tryng to find a way to know if a field is a foreign key, by example if I run this describe tablename; in the Key colum I got PRI for the primary key field, somebody know a way to get the foreign keys ? Regards Daniel
RE: how to list foreign keys
IF you are on 5.0.x you can use INFORMATION_SCHEMA 21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table http://dev.mysql.com/doc/mysql/en/key-column-usage-table.html -Original Message- From: Operator [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 3:02 PM To: mysql@lists.mysql.com Subject: how to list foreign keys HI everybody I'm tryng to find a way to know if a field is a foreign key, by example if I run this describe tablename; in the Key colum I got PRI for the primary key field, somebody know a way to get the foreign keys ? Regards Daniel
Foreign Keys
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
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
Backup / Restore database with foreign keys
Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup / Restore database with foreign keys
Daniel Kasak wrote: Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? Before loading the file, SET FOREIGN_KEY_CHECKS = 0; after loading the file, SET FOREIGN_KEY_CHECKS = 1; Better yet, edit the dump file to place those as the first line and last lines, respectively. Even better, upgrade to a newer mysql (4.1.1+), where they are automatically added to the dump file for you. See the manual for more http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html (way down at the end). Michael Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup / Restore database with foreign keys
i think you can use -K on your mysqldump and it'll put the hints in there for the mysql command to use as well - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 29, 2005 7:45 PM Subject: Backup / Restore database with foreign keys Greetings. I've just hit an interesting problem. Luckily I don't actually *need* to restore from a backup right now - I'm just trying to create a database dump to submit an unrelated bug report. Anyway ... I'm using the command: mysqldump -K DATABASE_NAME db.sql -p However when I create a new database and try to load the dump file: mysql NEW_DATABASE db.sql -p I get an error when I hit an InnoDB table that has a relationship set up with a table that hasn't yet been created. How do I get around this? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- 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: Backup / Restore database with foreign keys
Michael Stassen wrote: Before loading the file, SET FOREIGN_KEY_CHECKS = 0; after loading the file, SET FOREIGN_KEY_CHECKS = 1; That's it! Thanks :) Even better, upgrade to a newer mysql (4.1.1+), where they are automatically added to the dump file for you. Not until the client libraries are ready. I don't feel 'right' about hacking up the place with --old-password options and such. Also, my Gentoo server ( stable branch ) insists that 4.0.x is the latest that I can expect to install without breaking things. After doing some testing on my workstation, I tend to agree - getting everything compiled against 4.1.x is a major pain, and certainly not something I'm about to do at the moment with no real advantages on offer - I'm just likely to break something and be very sorry. Once all the questions about: Client does not support authentication protocol requested by server; consider upgrading MySQL client have died down, *then* it's time to upgrade the server. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- 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?
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]
foreign keys in MyISAM?
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 -- 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]
Foreign keys across databases?
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? Thanks, Jake Krohn -- 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: need help with foreign keys, new to mysql
Sorry for not replying as quickly as I usually do, I ran into some other
server issues. Please take a look at my comments to your post.
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: August 20, 2005 11:11 PM
Cc: John Gonzales; mysql@lists.mysql.com
Subject: Re: need help with foreign keys, new to mysql
Sorry to reply to my own message, but I meant to add that you can read about
defining foreign key constrints in the manual
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html.
[jg.] thanks, this is what I originally based my command off of.
Michael
Michael Stassen wrote:
There are a few possibilities here:
1. Both tables must be InnoDb. I can see table comments is InnoDb. Is
table journal InnoDb?
[jg.] yes both tables are innodb
2. Both ends of the foreign key must be the leftmost column in an
index. I can see an index on comments.journal_id. Does column
journal_id come first in an index in table journal?
[jg.] I don't quite understand what you mean by leftmost column in an
index, but yes journal_id is the first field in table journal, but that's
probably not what you meant. Take a look at the result from SHOW CREATE
TABLE:
CREATE TABLE `journal` (
`journal_id` int(10) unsigned NOT NULL auto_increment,
`journal_category` int(10) unsigned NOT NULL default '1',
`journal_datetime_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
`journal_datetime_modified` timestamp NOT NULL default '-00-00
00:00:00',
`journal_title` varchar(50) NOT NULL default 'no title',
`journal_entry` blob NOT NULL,
PRIMARY KEY (`journal_category`),
KEY `journal_category` (`journal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
3. You are misplacing your parentheses. MySQL looks at parenthesis
placement to help distinguish between functions and non-function
identifiers. In general, when there is no space between a word and a
left parenthesis, '(', the word is a function name, but when there is a
space in between, the word is not a function. Hence, journal(
journal_id ) would be journal_id as input to the function journal(),
where journal (journal_id) is what you want. Now, the parser may be
smart enough to notice there is no function journal(), so this may not
be a problem here, but it still something to be aware of, lest it bite
you later.
You could check on the first two with a simple
SHOW CREATE TABLE journal;
You could fix #3 by changing the CREATE TABLE statement for comments:
CREATE TABLE comments
(
comment_id INT,
journal_id INT,
INDEX jrn_ind (journal_id),
FOREIGN KEY (journal_id) REFERENCES journal (journal_id)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB
[jg.]
CREATE TABLE comments
(
comment_id INT,
comment_journal_id INT,
INDEX jrn_id (journal_id),
FOREIGN KEY (comment_journal_id) REFERENCES journal (journal_id)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB;
I got this as a respsone:
Key column 'journal_id' doesn't exist in table
Michael
--
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: need help with foreign keys, new to mysql
On Wed, 2005-08-24 at 06:11 -0500, John Gonzales wrote: CREATE TABLE `journal` ( `journal_id` int(10) unsigned NOT NULL auto_increment, `journal_category` int(10) unsigned NOT NULL default '1', `journal_datetime_created` timestamp NOT NULL default CURRENT_TIMESTAMP, `journal_datetime_modified` timestamp NOT NULL default '-00-00 00:00:00', `journal_title` varchar(50) NOT NULL default 'no title', `journal_entry` blob NOT NULL, PRIMARY KEY (`journal_category`), KEY `journal_category` (`journal_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 [jg.] CREATE TABLE comments ( comment_id INT, comment_journal_id INT, INDEX jrn_id (journal_id), FOREIGN KEY (comment_journal_id) REFERENCES journal (journal_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB; I got this as a respsone: Key column 'journal_id' doesn't exist in table From the manual: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. You've got an INT in comments table and an int(10) unsigned in the journal table. They need to match in signedness. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas signature.asc Description: This is a digitally signed message part
Re: need help with foreign keys, new to mysql
Pat Adams wrote: On Wed, 2005-08-24 at 06:11 -0500, John Gonzales wrote: CREATE TABLE `journal` ( `journal_id` int(10) unsigned NOT NULL auto_increment, [snip] CREATE TABLE comments ( comment_id INT, comment_journal_id INT, INDEX jrn_id (journal_id), Here you are defining an index named jrn_id on a column called journal_id. This column must be defined in this table. I suppose it is a mistake, it should be: INDEX jrn_id (comment_journal_id), FOREIGN KEY (comment_journal_id) REFERENCES journal (journal_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB; I got this as a respsone: Key column 'journal_id' doesn't exist in table See above. From the manual: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. You've got an INT in comments table and an int(10) unsigned in the journal table. They need to match in signedness. I don't dispute this, but it was not the reason for the error message. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with foreign keys, new to mysql
Wow, thanks both of you. It worked... =) -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: August 24, 2005 9:17 AM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: need help with foreign keys, new to mysql Pat Adams wrote: On Wed, 2005-08-24 at 06:11 -0500, John Gonzales wrote: CREATE TABLE `journal` ( `journal_id` int(10) unsigned NOT NULL auto_increment, [snip] CREATE TABLE comments ( comment_id INT, comment_journal_id INT, INDEX jrn_id (journal_id), Here you are defining an index named jrn_id on a column called journal_id. This column must be defined in this table. I suppose it is a mistake, it should be: INDEX jrn_id (comment_journal_id), FOREIGN KEY (comment_journal_id) REFERENCES journal (journal_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB; I got this as a respsone: Key column 'journal_id' doesn't exist in table See above. From the manual: http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. You've got an INT in comments table and an int(10) unsigned in the journal table. They need to match in signedness. I don't dispute this, but it was not the reason for the error message. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with foreign keys, new to mysql
i am logged in as root -JG. -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: August 19, 2005 10:35 PM To: John Gonzales Cc: mysql@lists.mysql.com Subject: Re: need help with foreign keys, new to mysql check the permissions on the mysql data dir, may be the user mysql or who ever runs mysql does not have sufficient privileges Kishore Jalleda On 8/19/05, John Gonzales [EMAIL PROTECTED] wrote: i am creating my own little blog and i am trying to create a comments table that uses the primary key of my blog table as a foreign key. i've tried the only two ways that i know how and both ways yielded the same error: #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) i orginally created the comments table before i read about FOREIGN KEYS, so i tried executing the following command: ALTER TABLE comments ADD FOREIGN KEY(comment_journal_id) REFERENCES journal(journal_id) ON DELETE CASCADE ON UPDATE CASCADE; which resulted with the same error as listed above. so not really knowing what i was doing, i decided to try and create the comments table from scratch using the following the command thinking that it might work (which it didn't): CREATE TABLE comments( comment_id INT, journal_id INT, INDEX jrn_ind( journal_id ) , FOREIGN KEY ( journal_id ) REFERENCES journal( journal_id ) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
Yes but is the MySQL daemon running as root? I hope it isn't... Jasper John Gonzales wrote: i am logged in as root -JG. -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: August 19, 2005 10:35 PM To: John Gonzales Cc: mysql@lists.mysql.com Subject: Re: need help with foreign keys, new to mysql check the permissions on the mysql data dir, may be the user mysql or who ever runs mysql does not have sufficient privileges Kishore Jalleda On 8/19/05, John Gonzales [EMAIL PROTECTED] wrote: i am creating my own little blog and i am trying to create a comments table that uses the primary key of my blog table as a foreign key. i've tried the only two ways that i know how and both ways yielded the same error: #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) i orginally created the comments table before i read about FOREIGN KEYS, so i tried executing the following command: ALTER TABLE comments ADD FOREIGN KEY(comment_journal_id) REFERENCES journal(journal_id) ON DELETE CASCADE ON UPDATE CASCADE; which resulted with the same error as listed above. so not really knowing what i was doing, i decided to try and create the comments table from scratch using the following the command thinking that it might work (which it didn't): CREATE TABLE comments( comment_id INT, journal_id INT, INDEX jrn_ind( journal_id ) , FOREIGN KEY ( journal_id ) REFERENCES journal( journal_id ) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. -- 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: need help with foreign keys, new to mysql
honestly... i don't even know. =( i'm new to mysql... how would i check? -jg -Original Message- From: Jasper Bryant-Greene [mailto:[EMAIL PROTECTED] Sent: August 20, 2005 2:18 AM To: mysql@lists.mysql.com Subject: Re: need help with foreign keys, new to mysql Yes but is the MySQL daemon running as root? I hope it isn't... Jasper John Gonzales wrote: i am logged in as root -JG. -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: August 19, 2005 10:35 PM To: John Gonzales Cc: mysql@lists.mysql.com Subject: Re: need help with foreign keys, new to mysql check the permissions on the mysql data dir, may be the user mysql or who ever runs mysql does not have sufficient privileges Kishore Jalleda On 8/19/05, John Gonzales [EMAIL PROTECTED] wrote: i am creating my own little blog and i am trying to create a comments table that uses the primary key of my blog table as a foreign key. i've tried the only two ways that i know how and both ways yielded the same error: #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) i orginally created the comments table before i read about FOREIGN KEYS, so i tried executing the following command: ALTER TABLE comments ADD FOREIGN KEY(comment_journal_id) REFERENCES journal(journal_id) ON DELETE CASCADE ON UPDATE CASCADE; which resulted with the same error as listed above. so not really knowing what i was doing, i decided to try and create the comments table from scratch using the following the command thinking that it might work (which it didn't): CREATE TABLE comments( comment_id INT, journal_id INT, INDEX jrn_ind( journal_id ) , FOREIGN KEY ( journal_id ) REFERENCES journal( journal_id ) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
Le vendredi 19 août 2005 à 21:09 -0500, John Gonzales a écrit : Hi John, #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) I've seen this error before. now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. You haven't by any chance deleted or moved your *.idb files by any chance ? If you do that, then you screw up the indexes that InnoDB needs to access the tables. This has happened to me previously when I changed from MySQL-Max to mysql standard versions, and on another occasion when I deleted the idb files because I didn't know what they were and why they were so big compared to the data I actually had in my tables. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help with foreign keys, new to mysql
Hey Alex, Although I am not too sure what *.idb files are, I looked in the directory that I designated to store the InnoDB files and found the directory empty. Could this be it? I don't recall though ever deleting files from that directory. How did you go about fixing this problem? -jg. -Original Message- From: Thurgood Alex [mailto:[EMAIL PROTECTED] Sent: August 20, 2005 3:22 AM To: John Gonzales Cc: mysql@lists.mysql.com Subject: Re: need help with foreign keys, new to mysql Le vendredi 19 août 2005 à 21:09 -0500, John Gonzales a écrit : Hi John, #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) I've seen this error before. now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. You haven't by any chance deleted or moved your *.idb files by any chance ? If you do that, then you screw up the indexes that InnoDB needs to access the tables. This has happened to me previously when I changed from MySQL-Max to mysql standard versions, and on another occasion when I deleted the idb files because I didn't know what they were and why they were so big compared to the data I actually had in my tables. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
John Gonzales wrote:
i am creating my own little blog and i am trying to create a comments table
that uses the primary key of my blog table as a foreign key. i've tried the
only two ways that i know how and both ways yielded the same error:
#1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150)
You can look up what error numbers mean with perror:
~: perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
i orginally created the comments table before i read about FOREIGN KEYS, so
i tried executing the following command:
ALTER TABLE comments
ADD FOREIGN KEY(comment_journal_id)
REFERENCES journal(journal_id)
ON DELETE CASCADE ON UPDATE CASCADE;
which resulted with the same error as listed above. so not really knowing
what i was doing, i decided to try and create the comments table from
scratch using the following the command thinking that it might work (which
it didn't):
CREATE TABLE comments(
comment_id INT,
journal_id INT,
INDEX jrn_ind( journal_id ) ,
FOREIGN KEY ( journal_id ) REFERENCES journal( journal_id )
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB
now before i go and do something stupid, i thought i might ask for some help
on this issue :D both my blog and comments tables are InnoDB.
There are a few possibilities here:
1. Both tables must be InnoDb. I can see table comments is InnoDb. Is
table journal InnoDb?
2. Both ends of the foreign key must be the leftmost column in an index. I
can see an index on comments.journal_id. Does column journal_id come first
in an index in table journal?
3. You are misplacing your parentheses. MySQL looks at parenthesis
placement to help distinguish between functions and non-function
identifiers. In general, when there is no space between a word and a left
parenthesis, '(', the word is a function name, but when there is a space in
between, the word is not a function. Hence, journal( journal_id ) would
be journal_id as input to the function journal(), where journal
(journal_id) is what you want. Now, the parser may be smart enough to
notice there is no function journal(), so this may not be a problem here,
but it still something to be aware of, lest it bite you later.
You could check on the first two with a simple
SHOW CREATE TABLE journal;
You could fix #3 by changing the CREATE TABLE statement for comments:
CREATE TABLE comments
(
comment_id INT,
journal_id INT,
INDEX jrn_ind (journal_id),
FOREIGN KEY (journal_id) REFERENCES journal (journal_id)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
Sorry to reply to my own message, but I meant to add that you can read about
defining foreign key constrints in the manual
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html.
Michael
Michael Stassen wrote:
There are a few possibilities here:
1. Both tables must be InnoDb. I can see table comments is InnoDb. Is
table journal InnoDb?
2. Both ends of the foreign key must be the leftmost column in an
index. I can see an index on comments.journal_id. Does column
journal_id come first in an index in table journal?
3. You are misplacing your parentheses. MySQL looks at parenthesis
placement to help distinguish between functions and non-function
identifiers. In general, when there is no space between a word and a
left parenthesis, '(', the word is a function name, but when there is a
space in between, the word is not a function. Hence, journal(
journal_id ) would be journal_id as input to the function journal(),
where journal (journal_id) is what you want. Now, the parser may be
smart enough to notice there is no function journal(), so this may not
be a problem here, but it still something to be aware of, lest it bite
you later.
You could check on the first two with a simple
SHOW CREATE TABLE journal;
You could fix #3 by changing the CREATE TABLE statement for comments:
CREATE TABLE comments
(
comment_id INT,
journal_id INT,
INDEX jrn_ind (journal_id),
FOREIGN KEY (journal_id) REFERENCES journal (journal_id)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE = INNODB
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help with foreign keys, new to mysql
i am creating my own little blog and i am trying to create a comments table that uses the primary key of my blog table as a foreign key. i've tried the only two ways that i know how and both ways yielded the same error: #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) i orginally created the comments table before i read about FOREIGN KEYS, so i tried executing the following command: ALTER TABLE comments ADD FOREIGN KEY(comment_journal_id) REFERENCES journal(journal_id) ON DELETE CASCADE ON UPDATE CASCADE; which resulted with the same error as listed above. so not really knowing what i was doing, i decided to try and create the comments table from scratch using the following the command thinking that it might work (which it didn't): CREATE TABLE comments( comment_id INT, journal_id INT, INDEX jrn_ind( journal_id ) , FOREIGN KEY ( journal_id ) REFERENCES journal( journal_id ) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
check the permissions on the mysql data dir, may be the user mysql or who ever runs mysql does not have sufficient privileges Kishore Jalleda On 8/19/05, John Gonzales [EMAIL PROTECTED] wrote: i am creating my own little blog and i am trying to create a comments table that uses the primary key of my blog table as a foreign key. i've tried the only two ways that i know how and both ways yielded the same error: #1005 - Can't create table '.\mydatabase\comments.frm' (errno: 150) i orginally created the comments table before i read about FOREIGN KEYS, so i tried executing the following command: ALTER TABLE comments ADD FOREIGN KEY(comment_journal_id) REFERENCES journal(journal_id) ON DELETE CASCADE ON UPDATE CASCADE; which resulted with the same error as listed above. so not really knowing what i was doing, i decided to try and create the comments table from scratch using the following the command thinking that it might work (which it didn't): CREATE TABLE comments( comment_id INT, journal_id INT, INDEX jrn_ind( journal_id ) , FOREIGN KEY ( journal_id ) REFERENCES journal( journal_id ) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE = INNODB now before i go and do something stupid, i thought i might ask for some help on this issue :D both my blog and comments tables are InnoDB. -- 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]
what WHERE how... i am confused: extra instances of foreign keys are being problematic
I am having trouble with this small bit of sql I am using for a homepage. I need to select information on the next two events from two separate tables; tblevents (which holds event related info), and tbleventdate (which holds info related to each date, including times and information) herein lies the problem: By using two separate tables (something that is required) I need to use a foreign key in tbleventdate. This foreign key is the key of tblevents, so when multiple dates occur for the same event (the reason for two tables), this foreign key is duplicated. When an event occurs on consecutive days, it will appear twice, being the only event seen (LIMIT 2). What I want is the next event which does not have the same eventid. And, just to clarify, it will not work if I say WHERE… =varDate, tblevents.eventid != tbleventdate.eventid (as this would return nothing) SELECT tblevents.eventid, tblevents.eventdescr, tblevents.eventname, tbleventdate.eventdate, tbleventdate.timeinfo, tbleventdate.eventid, tbleventdate.dateid, left(tblevents.eventdescr, 150) FROM tbleventdate, tblevents WHERE tblevents.eventid = tbleventdate.eventid AND tbleventdate.eventdate = varDate ORDER BY tbleventdate.eventdate DESC LIMIT 2 ***Note: varDate is defined as ?php date(Y-m-d) ? *** which returns -MM-DD Thank you in advance -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005
FW: what WHERE how... i am confused: extra instances of foreign keys are being problematic
I am having trouble with this small bit of sql I am using for a homepage. I need to select information on the next two events from two separate tables; tblevents (which holds event related info), and tbleventdate (which holds info related to each date, including times and information) herein lies the problem: By using two separate tables (something that is required) I need to use a foreign key in tbleventdate. This foreign key is the key of tblevents, so when multiple dates occur for the same event (the reason for two tables), this foreign key is duplicated. When an event occurs on consecutive days, it will appear twice, being the only event seen (LIMIT 2). What I want is the next event which does not have the same eventid. And, just to clarify, it will not work if I say WHERE… =varDate, tblevents.eventid != tbleventdate.eventid (as this would return nothing) SELECT tblevents.eventid, tblevents.eventdescr, tblevents.eventname, tbleventdate.eventdate, tbleventdate.timeinfo, tbleventdate.eventid, tbleventdate.dateid, left(tblevents.eventdescr, 150) FROM tbleventdate, tblevents WHERE tblevents.eventid = tbleventdate.eventid AND tbleventdate.eventdate = varDate ORDER BY tbleventdate.eventdate DESC LIMIT 2 ***Note: varDate is defined as ?php date(Y-m-d) ? *** which returns -MM-DD Thank you in advance -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.2/29 - Release Date: 6/27/2005
Primary and Foreign Keys
Is it good practice to have a primary key for a table even though the table has a foreign key that is the primary key of another table? If so, why is this the case? I would appreciate if you point me to some resources that point out the advantages and disadvantages of both approaches. Thank you. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary and Foreign Keys (Follow Up)
As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary and Foreign Keys (Follow Up)
If your FK really is unique, you don't need two tables. Example First table Second Table ID FK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary and Foreign Keys (Follow Up)
Hello. I appreciate your input. To clarify, I will provide you with an example: I have a table called business and another one called food_business. The field business_id is a primary key of table business and a foreign key of table food_business. In this case, the foreign key is unique and although this is a one-to-one relationship, it would be inappropriate to merge these 2 tables since food_business is a specialization of business and will therefore contain fields that only apply to food businesses and not to any generic business. - Asad On Wed, 15 Jun 2005, Stefan Kuhn wrote: If your FK really is unique, you don't need two tables. Example First table Second Table IDFK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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: Primary and Foreign Keys (Follow Up)
In this case, business_id should be both the primary key and a foreign key. If you don't set it as a primary key, then the database would allow duplicate entries into the food_business table. By implementing this simple rule, you can prevent a whole class of application bugs from causing problems. On a different note, some databases will, on update, log the whole row if there is no primary key specified. This means that the transaction log can get very full very fast because all of the fields are logged even though only one field is changed. In short, since the field does uniquely identify each row in the table, it should be made the primary key. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 -Original Message- From: Asad Habib [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 15, 2005 5:19 AM To: Stefan Kuhn Cc: mysql@lists.mysql.com Subject: Re: Primary and Foreign Keys (Follow Up) Hello. I appreciate your input. To clarify, I will provide you with an example: I have a table called business and another one called food_business. The field business_id is a primary key of table business and a foreign key of table food_business. In this case, the foreign key is unique and although this is a one-to-one relationship, it would be inappropriate to merge these 2 tables since food_business is a specialization of business and will therefore contain fields that only apply to food businesses and not to any generic business. - Asad On Wed, 15 Jun 2005, Stefan Kuhn wrote: If your FK really is unique, you don't need two tables. Example First table Second Table ID FK 1 1 2 2 3 3 ... So you can make this one table. On other words, it would be a one-to-one relation. And this would be one table. Only with a one-to-many relation two tables make sense, but then your FK can't be unique. Stefan Am Wednesday 15 June 2005 12:41 schrieb Asad Habib: As a follow up to my question, I did want to mention that the foreign key I am using is unique. - Asad -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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] -- 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]
Foreign keys - No action - Errors
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]
Re: mysqldump and Foreign Keys
Hello. Put SET FOREIGN_KEY_CHECKS=0 at the top of your dump file. In 4.1, mysqldump puts that automatically to the dump file. See comments at: http://dev.mysql.com/doc/mysql/en/mysqldump.html Michael Satterwhite [EMAIL PROTECTED] wrote: mysqldump backs all of the tables up in alphabetic order, including the table create statements. The problem is that if foreign keys exist on the tables, the reload will not work unless the backup file is hand edited to insure that tables depended upon are created first. Is there a way around this? If not, shouldn't the tables be created first and the foreign keys created at the end of the backup? I *REALLY don't want to hack mysqldump and am hoping that there's already a way around this - or that a way is being developed. -- 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]
