Table Type For PK/FK
I have been reading the definitive guide to MySQL5, and I am not finding a answer to a question. I am designing a project in which I am going to have primary keys, and foreign keys. In order to keep things proper, I am going to have delete on cascades on my foreign keys, so when the primary key is deleted, the data model is kept clean. As I have been reading, it appears that the InnoDB Storage Engine supports PK/FK relationships. And that the MyISAM does not. But I cannot verify that. Also, when I use replication, it appears that both engines, require a different replication technique? But what if I mix both types of engines, how do I replicate that? Sorry this is a bit of a broad post, but these two questions will help me figure my needs for the design. Thanks, Scott
Re: Table Type For PK/FK
Hi Scott, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. Replication is storage-engine independent, so you shouldn't have a problem with that. Basically it takes the commands from the binary (update,delete,insert,replace) logs and applies them to the slave. -Sheeri On 3/31/06, Scott Purcell [EMAIL PROTECTED] wrote: I have been reading the definitive guide to MySQL5, and I am not finding a answer to a question. I am designing a project in which I am going to have primary keys, and foreign keys. In order to keep things proper, I am going to have delete on cascades on my foreign keys, so when the primary key is deleted, the data model is kept clean. As I have been reading, it appears that the InnoDB Storage Engine supports PK/FK relationships. And that the MyISAM does not. But I cannot verify that. Also, when I use replication, it appears that both engines, require a different replication technique? But what if I mix both types of engines, how do I replicate that? Sorry this is a bit of a broad post, but these two questions will help me figure my needs for the design. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
As I have been reading, it appears that the InnoDB Storage Engine supports PK/FK relationships. And that the MyISAM does not. But I cannot verify that. That is correct. 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: Table Type For PK/FK
Hello Sheeri, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. No error? A comment? What use is that? If you want FKs, having the FKs as a comment is useless. Instead, re-create the table as of the InnoDB type. 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: Table Type For PK/FK
I didn't write the codebase for MySQL, so it's pointless to tell me that it's useless to be able to create a foreign key on a MyISAM table. I agree that it's useless, however, it's possible, which is why I put it in there -- as a caveat. The use is that apparently in future versions MyISAM will support foreign key constraints. It's a comment because it still shows up in SHOW CREATE TABLE and such. Like I said, I didn't design MySQL -- I just use it and was warning that it's possible to create a table. I've seen the dreaded Error number 150 way too many times, and sometimes it's because I forgot the engine=innodb part of the CREATE TABLE statement. -Sheeri On 3/31/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Sheeri, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. No error? A comment? What use is that? If you want FKs, having the FKs as a comment is useless. Instead, re-create the table as of the InnoDB type. 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: Table Type For PK/FK
From the 5.0.18 manual: The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.2.6.4, FOREIGN KEY Constraints. For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.5, CREATE TABLE Syntax. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.9.5, MySQL Differences from Standard SQL. You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. InnoDB supports the use of ALTER TABLE to drop foreign keys: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. For more information, see Section 14.2.6.4, FOREIGN KEY Constraints. Regards Keith ;-) In theory, theory and practice are the same; in practice they are not. On Fri, 31 Mar 2006, sheeri kritzer wrote: To: Martijn Tonies [EMAIL PROTECTED] From: sheeri kritzer [EMAIL PROTECTED] Subject: Re: Table Type For PK/FK I didn't write the codebase for MySQL, so it's pointless to tell me that it's useless to be able to create a foreign key on a MyISAM table. I agree that it's useless, however, it's possible, which is why I put it in there -- as a caveat. The use is that apparently in future versions MyISAM will support foreign key constraints. It's a comment because it still shows up in SHOW CREATE TABLE and such. Like I said, I didn't design MySQL -- I just use it and was warning that it's possible to create a table. I've seen the dreaded Error number 150 way too many times, and sometimes it's because I forgot the engine=innodb part of the CREATE TABLE statement. -Sheeri On 3/31/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Sheeri, Indeed, only the BDB and InnoDB storage engines support referential integrity. If you accidentally create the table as MyISAM, there is no error, though -- the constraints serve as a comment. No error? A comment? What use is that? If you want FKs, having the FKs as a comment is useless. Instead, re-create the table as of the InnoDB type. 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]
