CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
Nataraj
Nataraj

Reputation: 83

Drop a foreign key from a table in mysql

I have created a foreign key without giving it a name for a column in a table. Now i want to remove that column. First i've tried removing the foreign key constraint but there was an error
I've used the following sql command 

ALTER TABLE passenger 
DROP FOREIGN KEY bookedBy

The error message

#1091 - Can't DROP 'bookedBy'; check that column/key exists

i've ensured that the column exist.
I have not named the foreign key constraint. Is it possible to remove the foreign key constraint without naming it. Is there any default naming given to the foreign keys.

Upvotes: 5

Views: 15539

Answers (4)

Abdulla Nilam
Abdulla Nilam

Reputation: 38584

Find out the name of the foreign key:

SHOW CREATE TABLE your_table_name;

then you can drop it using the following command:

ALTER TABLE your_table_name DROP FOREIGN KEY foreign_key_name;

follow this(Old Answer)

  1. Log to phpMyAdmin
  2. Check which field you need to DROP(Ex I will use Product )

enter image description here

  1. then Select

enter image description here

  1. UnCheck this checkbox and click YES

enter image description here

Upvotes: 0

Ganesh Krishnan
Ganesh Krishnan

Reputation: 7395

The quickest and dirtiest way is to turn off foreign key checks, drop the table and then turn the checks back on

SET FOREIGN_KEY_CHECKS=0;

drop table mytable

SET FOREIGN_KEY_CHECKS=1;

Upvotes: -1

spencer7593
spencer7593

Reputation: 108370

Run the statement SHOW CREATE TABLE passenger.

The output from that will show the foreign key constraints, as well as the columns in the table. You should be able to figure out the name of the foreign key constraint you want to drop from that.

Or, you can muck with your queries of the tables in information_schema database. It's going to show up in there as well.

Followup

One possible query of information_schema to find the names of the foreign key constraints for a given table:

 SELECT kcu.constraint_schema
      , kcu.constraint_name
  --  , kcu.*
   FROM information_schema.key_column_usage kcu
  WHERE kcu.referenced_table_name IS NOT NULL 
    AND kcu.constraint_schema = 'mydatabase'
    AND kcu.table_name        = 'mytablename'

Upvotes: 1

The Reason
The Reason

Reputation: 7973

CREATE TABLE Orders
(
  O_Id int NOT NULL,
  OrderNo int NOT NULL,
  P_Id int,
  PRIMARY KEY (O_Id),
  FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

also you can add foreign key like this

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

and delete

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

How to find foreign key in my table 

   SELECT
         TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      REFERENCED_TABLE_NAME = '<table>';

Upvotes: 11

Related Questions