[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Table Type For PK/FK
From:       mysql () karsites ! net
Date:       2006-03-31 17:55:58
Message-ID: Pine.LNX.4.61.0603311850430.11497 () karsites ! net
[Download RAW message or body]


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 <m.tonies@upscene.com>
> From: sheeri kritzer <awfief@gmail.com>
> 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 <m.tonies@upscene.com> 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/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]