Drop Foreign Key

melgra70 · #1 (**permalink**) 03-14-11, 22:16

I've tried to follow all the instructions and posts and came up with this statement but it doesn't work:
My table is 'horse'
My foreign key is 'trainer_id'
Statement:

alter table horse
drop foreign key trainer_id;

I notice that someone else got the same type of error as I did:
Error on rename of '.\horsedb\horse' to '.\horsedb\#sq12-838-2' (errno: 152)

I see that the example on the forum indicates I should use internally_generated_foreign_key_id but I don't understand what is being asked for here, if it is not just the name of the foreign key.

subhbwn · #2 (**permalink**) 03-15-11, 02:03

USE SHOW TABLE command from the mysql command prompt.
It will show you the key_id. Drop that. To learn in detail about the MySQL ALTER TABLE, this may be useful for you MySQL altering table - MySQL Tutorial | w3eresource

it-iss.com · #3 (**permalink**) 03-15-11, 03:56

Here is an example of how it works. There is no easy command to quickly identify the constraints on a table. However, using the SHOW CREATE TABLE command we get the CREATE TABLE statement to recreate the table. This includes the names of the foreign key constraints. Using this information you can then drop the necessary constraints:

Code:

mysql> CREATE TABLE parent (id INT NOT NULL,
    ->                      PRIMARY KEY (id)
    -> ) ENGINE=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)
    ->                       ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.04 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`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table child drop foreign key child_ibfk_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

melgra70 · #4 (**permalink**) 03-16-11, 20:44

Thanks for this Ronan. I understand it but I have an error in my create table coding.

This is what successfully creates my table; it does not, obviously, designate the foreign key, although it's obviously there:
create table horse
(
horse_id int not null auto_increment primary key,
horse_name varchar(20) not null,
horse_colour varchar(20),
horse_sire int not null,
horse_dam int not null,
horse_born year(4) not null,
trainer_id int references trainer(trainer_id)
) type=InnoDB;

If I prepend FOREIGN KEY to my trainer_id, which would then be
FOREIGN KEY (trainer_id) int references trainer(trainer_id)
I get an error. What am I doing wrong?

r937 · #5 (**permalink**) 03-16-11, 21:02

Code:

CREATE TABLE horse
(
...
, trainer_id INTEGER 
, FOREIGN KEY trainer_fk ( trainer_id ) 
     REFERENCES trainer ( trainer_id )
)

mysql doesn't support the REFERENCES attribute on a column declaration, you simply need to declare it separately with the FOREIGN KEY declaration

Internet Services

Web Development

Digital Marketing

Consumer Tech