Re: How to turn off all constraints in a table?
Easier than that (though Sheeri's way would work): ALTER TABLE yourtablename DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ... do your processing ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS; http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 02/20/2006 12:28:54 PM: Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, Tomáš Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- 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: How to turn off all constraints in a table?
Once CHECK CONSTRAINTS are enabled, I am sure there will be a way to turn them off temporarily. Right now, you can create tables that have them but they are not enforced. To quote http://dev.mysql.com/doc/refman/5.0/en/create-table.html InnoDB tables support checking of foreign key constraints. See Section 14.2, “The InnoDB Storage Engine”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.2.6.4, “FOREIGN KEY Constraints”. For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.9.5.5, “Foreign Keys”. So, by disabling the keys (which turns off all UNIQUE and PRIMARY keys) and by disabling all FK checks, you have everything covered. :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tomáš Vichta [EMAIL PROTECTED] wrote on 02/21/2006 11:10:42 AM: Great, that's what I need, thanx very much. And is any similar possibility how to disable all constraints? For example CHECK CONSTRAINT type. [EMAIL PROTECTED] wrote: Easier than that (though Sheeri's way would work): ALTER TABLE yourtablename DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; ... do your processing ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS; http://dev.mysql.com/doc/refman/5.0/en/alter-table.html http://dev.mysql.com/doc/refman/5.0/en/set-option.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine sheeri kritzer [EMAIL PROTECTED] wrote on 02/20/2006 12:28:54 PM: Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, TomĂĄĹĄ Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- 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: How to turn off all constraints in a table?
Drop the keys and references, change it, and then re-create the keys and references. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html (a SHOW CREATE TABLE will show you the constraints including foreign keys -- copy those, so you can create them later. Then you can use ALTER TABLE to drop the keys, change your columns, and then you can use ALTER TABLE to add the keys back in, although it sounds like you won't have a primary key in the new table). -Sheeri On 2/17/06, Tomáš Vichta [EMAIL PROTECTED] wrote: Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- 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]
How to turn off all constraints in a table?
Hello, I need to turn off all constraints in a table (especially primary and foreign keys) - because for example I need to exchange primary key values of two rows in a table. And because of primary key constraints I can do it directly. I would to turn off the constraint, rename value of PK1 to PK2 - now I have the same 2 values PK2 and it's the problem, if the primary key constraint is enabled. Thanx very much for answer, TV. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
