1

I'm getting following error while i try to add foreign key to already created table by alter query as follows. There is two table usertype which contain usertypeid field as primary key and account table which has usertypeid which i want to be a foreign key.

I'm using the following query, and as I know the query is correct, but don't know why i'm getting this error.

alter table account add constraint fk_usertypeid foreign key usertypeid references usertype(usertypeid) on update cascade on delete cascade;

Following error occured while i try to execute query

 #1064 - 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 'references usertype(usertypeid) on update cascade on delete cascade' at line 1
Sumit P Makwana
  • 317
  • 5
  • 17
  • 1
    the problem here is you are wrapping identifier such as table name and column name with single quote. removing the single quotes will fix the problem `ALTER TABLE ACCOUNT ADD CONSTRAINT Fk_usertypeid FOREIGN KEY (USERTYPEID) REFERENCES USERTYPE(USERTYPEID) ON UPDATE CASCADE ON DELETE CASCADE` for more info: [MySQL - when to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/mysql-when-to-use-single-quotes-double-quotes-and-backticks) – John Woo Jul 27 '13 at 17:05
  • still that error occurs after removing quotes, I've updated the question and added the detail of error. – Sumit P Makwana Jul 28 '13 at 05:50

1 Answers1

1

I was getting error because index is not created on the field which is foreign key.

Don't know why but the foreign key required to be primary key, unique or a index must be created on it.

Sumit P Makwana
  • 317
  • 5
  • 17