CODEWITHSUNDEEP
mysqlsqldatabaseprimary-keyalter-table
User27854
User27854

Reputation: 884

Usage of Alter command to drop Primary key and Foreign Key

I have created three tables

create table employee1 (eid int, ename varchar(25),email varchar(15));

create table accounts1 (eid int, accno int,atype varchar(2),bal int);

create table trans1(cid int, accno int, atype varchar(2), accounts int, bal int);

using alter command I have added primary key and foreign keys

alter table employee1 add primary key(eid);

alter table accounts1 add foreign key(eid) references employee1(eid);

alter table accounts1 add primary key(accno,atype);

alter table trans1 add foreign key(accno,atype) references accounts1(accno,atype);

Now my problem is I am trying to delete all the primary key and foreign keys of all the tables its giving me errors.

Initially I tried deleteing the primary key. then on refering to the below links showed me that foreign key must be deleted first and I tried doing that but still i am getting the error. MYSQL 5.5 Drop Primary Key

errors:

mysql> alter table employee1 drop constraint eid;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corres ponds to your MySQL server version for the right syntax to use near 'constraint eid' a t line 1

mysql> alter table accounts1 drop primary key;

ERROR 1025 (HY000): Error on rename of '.\jlcindia#sql-b20_1' to '.\jlcindia\accounts 1' (errno: 150)

mysql> alter table employee1 drop primary key;

ERROR 1025 (HY000): Error on rename of '.\jlcindia#sql-b20_1' to '.\jlcindia\employee 1' (errno: 150)

mysql> alter table trans1 drop foreign key;

ERROR 1005 (HY000): Can't create table 'jlcindia.#sql-b20_1' (errno: 150) mysql> show engine innodb status

Note: I have no auto increment or any such thing in any of my tables.

I checked the following links but none was of any help.

Remove Primary Key in MySQL

mysql, alter column remove primary key and auto incremement

Error altering primary key column in mysql table

How can I alter a primary key constraint using SQL syntax?

Upvotes: 0

Views: 4195

Answers (3)

Mayur Sawant
Mayur Sawant

Reputation: 11

First of all you need to find the constraint name related to the Primary and Foreign keys. Then you need to use these constraint names while dropping the keys.

select *
from
information_schema.key_column_usage
where
table_name = 'my_table'

From the above query you will be able to find the constraint names.

Now use the below syntax to drop the keys.

ALTER TABLE tablename DROP CONSTRAINT constraint name

Upvotes: 0

G one
G one

Reputation: 2729

You need to drop the constraints in the following order(Which is the reverse of the order in which you created the keys):

  • Foreign keys in the table trans1
  • Primary keys in the table accounts1
  • Foreign key in the table accounts1
  • Primary key in the table employee1

fiddle

Upvotes: 0

Juhi
Juhi

Reputation: 270

I think,You need to first remove the foreign key of table trans1.Then remove foreign key of table accounts1.Then try to remove primary key of table accounts1.then remove primary key of table *employee1 *.

If you remove foreign key and primary key in this order then you can remove all constraints as you want.

Upvotes: 0

Related Questions