user2900314 user2900314 - 6 months ago 15
SQL Question

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?

Answer

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