mysql 语法错误 1064 at line 194 add foreign key for many-to-many relationship

Question

请帮帮我。当我尝试运行 sql 命令时收到以下消息:

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.

<h1>----------------------------------------------------------------------------------------</h1>

<p>DROP TABLE IF EXISTS <code>VFSGRPUSR</code>;</p>

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

<h1>----------------------------------------------------------------------------------------</h1>

<p>DROP TABLE IF EXISTS <code>VFSUSR</code>;</p>

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

<h1>----------------------------------------------------------------------------------------</h1>

<p>DROP TABLE IF EXISTS <code>VFSGRP</code>;</p>

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

<h1>----------------------------------------------------------------------------------------</h1>

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

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

Answer 1

为什么您还尝试设置 ENGINE 和 CHARSET？只需使用:

ALTER TABLE VFSGRPUSR
    ADD CONSTRAINT VFSGRPUSR_REF_VFSGRP
        FOREIGN KEY (VFSGRP)
        REFERENCES VFSGRP(VFSGRP_ID);

并引用官方文档了解可用的语法:http://dev.mysql.com/doc/refman/5.1/en/alter-table.html