List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 21 2006 4:31pm
Subject:Re: How to turn off all constraints in a table?
View as plain text  
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 <tomas.vichta@stripped> 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.
> 
> 
> SGreen@stripped 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" <awfief@stripped> 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 <tomas.vichta@stripped> 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/mysql?unsub=awfief@stripped
> > > >
> > > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1n@stripped
> > >
> 

Thread
How to turn off all constraints in a table?Tomáš Vichta17 Feb
  • Re: How to turn off all constraints in a table?sheeri kritzer20 Feb
    • Re: How to turn off all constraints in a table?SGreen21 Feb
Re: How to turn off all constraints in a table?SGreen21 Feb