How can I drop this Foreign Key Column?

Question

I have looked at some of the other questions similar to this one but have not had any luck. I am trying to delete the whole foreign key column from the blogs table. It is linked to a primary key in my students table as well.

Create table students
(
studentid integer primary key NOT NULL,
ulid varchar(20) NOT NULL,
password varchar(20) NOT NULL,
email varchar(50) NOT NULL
)

Create table blogs
(
blogid integer primary key NOT NULL,
blogdate date NOT NULL,
title varchar(50) NOT NULL,
description varchar(8000) NOT NULL,
category varchar (50) NOT NULL,
studentid integer NOT NULL,
foreign key (studentid) references students (studentid)
)

I tried this and it didn't work

alter table blogs drop column studentid

alter table blogs drop constraint studentid

alter table blogs drop foreign key studentid

Possible duplicate of [MySQL Removing Some Foreign keys](http://stackoverflow.com/questions/838354/mysql-removing-some-foreign-keys). Basically, you need the actual constraint _name_, not which column it affects — Chris Forrence, Dec 03 '14 at 17:44
The linked duplicate tells you how to find the constraint name. — Michael McGriff, Dec 03 '14 at 18:03
@Tator if in your table only one foreign key is there then it will be normally like tablename_ibfk_1. for you it will be blogs_ibfk_1 — Suchit kumar, Dec 03 '14 at 18:09

Suchit kumar · Answer 1 · 2014-12-03T18:06:04.067

0

try this sql :

alter table blogs drop foreign key keyconstraint   // for you it will be `blogs_ibfk_1` i think

if you have any problem in droping the foreign constraind use:

show create table blogs

here you can see the created foreign key constraint name. then use it in sql

edited Dec 03 '14 at 18:06

answered Dec 03 '14 at 17:53

Suchit kumar

11,809
3
22
44

@Tator use the show sql which i have posted to know constraint name. – Suchit kumar Dec 03 '14 at 18:04
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//table name' at line 1 – Tator Dec 03 '14 at 18:10
when I do that it shows CREATE TABLE `blogs` ( `blogid` int(11) NOT NULL AUTO_INCREMENT, `blogdate` date NOT NULL, `title` varchar(50) NOT NULL, `description` varchar(8000) NOT NULL, `category` varchar(50) NOT NULL, `studentid` int(11) NOT NULL, PRIMARY KEY (`blogid`), KEY `studentid` (`studentid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 – Tator Dec 03 '14 at 18:16
if your problem is solved then you can accept the answer and close this topic. – Suchit kumar Dec 03 '14 at 18:22

How can I drop this Foreign Key Column?

1 Answers1