mysql syntax error 1064 at line 194 add foreign key for many-to-many relationship

2
votes

help me please. I get the following message when I try to run the sql commands:

ERROR 1064 (42000) at line 194: 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 '(
    ADD CONSTRAINT VFSGRPUSR_REF_VFSGRP
        FOREIGN KEY (VFSGRP)
        REFERENCES `VFS' at line 1

I'm trying to implement a many-to-many relationship.




----------------------------------------------------------------------------------------



DROP TABLE IF EXISTS VFSGRPUSR;



CREATE TABLE VFSGRPUSR
(
  VFSGRP INTEGER REFERENCES VFSGRP(VFSGRP_ID),
  VFSUSR INTEGER REFERENCES VFSUSR(VFSUSR_ID),
  PRIMARY KEY (VFSGRP, VFSUSR)
)ENGINE=InnoDB DEFAULT CHARSET utf8;



----------------------------------------------------------------------------------------



DROP TABLE IF EXISTS VFSUSR;



CREATE TABLE VFSUSR
(
  VFSUSR_ID INTEGER NOT NULL auto_increment,
  DEPARTMENT INTEGER,
  FIRSTNAME VARCHAR(255) NOT NULL,
  SURNAME VARCHAR(255) NOT NULL,
  LOGIN VARCHAR(255) NOT NULL,
  PASSWORD BLOB,
  TOKEN BLOB,
  PRIMARY KEY  (VFSUSR_ID),
  CONSTRAINT VFSUSR_REF_DEPARTMENT FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT (DEPARTMENT_ID)
)ENGINE=InnoDB DEFAULT CHARSET utf8;



----------------------------------------------------------------------------------------



DROP TABLE IF EXISTS VFSGRP;



CREATE TABLE VFSGRP
(
  VFSGRP_ID INTEGER NOT NULL auto_increment,
  GROUPNAME VARCHAR(255) NOT NULL,
  PRIMARY KEY (VFSGRP_ID)
)ENGINE=InnoDB DEFAULT CHARSET utf8;



----------------------------------------------------------------------------------------



ALTER TABLE VFSGRPUSR(
    ADD CONSTRAINT VFSGRPUSR_REF_VFSGRP
        FOREIGN KEY (VFSGRP)
        REFERENCES VFSGRP(VFSGRP_ID)
)ENGINE=InnoDB DEFAULT CHARSET utf8;



ALTER TABLE VFSGRPUSR(
    ADD CONSTRAINT VFSGRPUSR_REF_VFSUSR
        FOREIGN KEY (VFSUSR)
        REFERENCES VFSUSR(VFSUSR_ID)
)ENGINE=InnoDB DEFAULT CHARSET utf8;


    

				
				1
				

					

					mysqlmany-to-many
					

					

						

							
						

					

				

				
			

			

			
			

			
			
			
			
			
			

		

		
1 Answers

		
		
		

			

				
2  

				
votes

			

			

				
				
Why are you trying to also set ENGINE and CHARSET? Just use:
ALTER TABLE VFSGRPUSR
    ADD CONSTRAINT VFSGRPUSR_REF_VFSGRP
        FOREIGN KEY (VFSGRP)
        REFERENCES VFSGRP(VFSGRP_ID);

And refer to the official documentation for available syntax: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html