Hi! Starting from version 4.0.13, InnoDB supports
ALTER TABLE ... DROP FOREIGN KEY internally_generated_foreign_key_id You have to use SHOW CREATE TABLE to look the internally generated foreign key id when you want to drop a foreign key. Version 4.0.13 came in May, but 4.1.0-alpha was released in April. That is why the feature is not yet in 4.1.0. Version 4.1.1 will have DROP FOREIGN KEY. My guess is that 4.1.1 will be released August 31st, 2003. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ ----- Original Message ----- From: "dcp" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, July 10, 2003 3:35 AM Subject: dropping foreign key constraint (version 4.1.0-alpha-max-nt) > I just installed the 4.1.0-alpha-max-nt version of MySql and have just > started playing around with it. > > My first test was to try to create a couple of tables, one with a > foreign key constraint. I can't seem to figure out how to drop the > foreign key constraint, I read through the docs and it says I need to > do a 'show create table <table_name>' to get the internally generated > foreign key id, but this does not show up when I do the show create > table <table_name> statement. I'm thinking this is something new with > the 4.1.0-alpha version, in that maybe now it allows you to just type > in the name of the foreign key, so I tried alter table child drop > foreign key parent_id but got the following error: > ERROR 1005: Can't create table '.\test\#sql-634_c.frm' (errno: 150) > > Here's the entire script output. Any ideas or solutions are greatly > appreciated! > > > C:\mysql\bin>mysql > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 12 to server version: 4.1.0-alpha-max-nt > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> use test; > Database changed > mysql> CREATE TABLE parent(id INT NOT NULL, > -> PRIMARY KEY (id)) TYPE=INNODB; > Query OK, 0 rows affected (0.07 sec) > > mysql> CREATE TABLE child(id INT, parent_id INT, > -> INDEX par_ind (parent_id), > -> FOREIGN KEY (parent_id) > -> REFERENCES parent(id) > -> ) TYPE=INNODB; > Query OK, 0 rows affected (0.07 sec) > > mysql> show create table child; > +-------+------------------------------------------------------------------- ---- > -------------------------------------------------------------------------- ------ > -----------------------------------------------------+ > | Table | Create Table > > | > +-------+------------------------------------------------------------------- ---- > -------------------------------------------------------------------------- ------ > -----------------------------------------------------+ > | child | CREATE TABLE `child` ( > `id` int(11) default NULL, > `parent_id` int(11) default NULL, > KEY `par_ind` (`parent_id`), > FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) > ) TYPE=InnoDB CHARSET=latin1 | > +-------+------------------------------------------------------------------- ---- > -------------------------------------------------------------------------- ------ > -----------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> alter table child drop foreign key parent_id; > ERROR 1005: Can't create table '.\test\#sql-634_c.frm' (errno: 150) > mysql> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
