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
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?
You need to drop the constraints in the following order(Which is the reverse of the order in which you created the keys):