0

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?

Community
  • 1
  • 1
User27854
  • 824
  • 1
  • 16
  • 40

3 Answers3

0

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.

Juhi
  • 270
  • 2
  • 17
  • I tried removing the foreign key of trans1, alter table trans1 drop foreign key; and I am getting this error. ERROR 1005 (HY000): Can't create table 'jlcindia.#sql-b20_1' (errno: 150) – User27854 Mar 04 '14 at 05:53
  • Try This:ALTER TABLE trans1 DROP FOREIGN KEY accno. – Juhi Mar 04 '14 at 06:08
  • foreign key constraint has to be dropped by constraint name and not the index name. The syntax is: alter table footable drop foreign key fooconstraint – Juhi Mar 04 '14 at 06:12
  • mysql> alter table trans1 drop foreign key accno; ERROR 1025 (HY000): Error on rename of '.\jlcindia\trans1' to '.\jlcindia\#sql2-95c-1 (errno: 152) – User27854 Mar 04 '14 at 06:21
  • It is indeed a foreign key error, you can find out using perror:shell$ perror 152 MySQL error code 150: Foreign key constraint is incorrectly formed.To find out more details about what failed, you can use SHOW ENGINE INNODB STATUS and look for the LATEST FOREIGN KEY ERROR section it contains details about what is wrong.In your case, it is most likely cause something is referencing the accno column. – Juhi Mar 04 '14 at 06:25
  • Ok here it is.. Its showing, "Cannot find a constraint with the given id "accno"." – User27854 Mar 04 '14 at 06:31
  • check 'accno' must be not null.If youset it 'allow null' then it can cause this type of error. – Juhi Mar 04 '14 at 06:48
  • When i look at the table description it show that the default of "accno" is null.. how do i change it? – User27854 Mar 04 '14 at 06:51
  • ALTER TABLE trans1 MODIFY accno INT(11) NOT NULL; – Juhi Mar 04 '14 at 06:54
  • ALTER TABLE accounts1 MODIFY accno INT(11) NOT NULL – Juhi Mar 04 '14 at 06:55
  • I have modified trans1 table as you have told still i am not able to delete the foreign key.. mysql> alter table trans1 drop foreign key accno; ERROR 1025 (HY000): Error on rename of '.\jlcindia\trans1' to '.\jlcindia\#sql2-95c-1' (errno: 152) – User27854 Mar 04 '14 at 07:56
0

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

G one
  • 2,679
  • 2
  • 14
  • 18
  • I tried dropping the foreign key of trans1 table but I am getting the following error: Query: alter table trans1 drop foreign key; error that I am getting ERROR 1005 (HY000): Can't create table 'jlcindia.#sql-b20_1' (errno: 150) – User27854 Mar 04 '14 at 05:57
  • I dont know how to use fiddle. so I deleted all the existing tables and inserted your queries. but when i try to remove the foreign key I am still getting the error: – User27854 Mar 04 '14 at 06:12
  • The error that I am getting is: Error 1025(HY000): Error on rename of '.\jlcindia\trans1' to '.\jlcindia\#sql2-95c-1' (errno:152) – User27854 Mar 04 '14 at 06:15
  • Are you adding any other constraint?? Are you using Innodb engine?? – G one Mar 04 '14 at 06:17
  • No I am not adding any constraint. I am using mysql 5.5. As fat as engine is concerned when I gave the query show engines its showing Myisam Default engine as of MySQL 3.23 with great performance.. Os i guess the engine that I am having is Myisam – User27854 Mar 04 '14 at 06:27
  • Ok,, I guess that the engine is Innodb because when a user by name juhi asked me to execute SHOW ENGINE INNODB STATUS and check for foreign key error.. and I found this... Cannot find a constraint with the given id "accno" – User27854 Mar 04 '14 at 06:34
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/48926/discussion-between-g-one-and-user2900314) – G one Mar 04 '14 at 06:36
0

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
Mayur Sawant
  • 11
  • 1