【已解决】建表语句求助

lfsyue

mysql> create table trains(tid integer not null,ttype varchar(10) not null,startstation integer not null,endstation integer not null,primary key(tid) constraint startstation foreign key trains(startstation) references stations(sid) on delete restrict on update restrict,constraint endstation foreign key trains(endstation) references stations(sid) on delete restrict on update restrict);
ERROR 1064 (42000): 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 'constraint startstation foreign key trains(startstation) references stations(sid' at line 1

why?
how to solve?

ajdan4q

create table trains(tid integer not null,ttype varchar(10) not null,startstation integer not null,endstation integer not null,primary key(tid) ,constraint startstation foreign key trains(startstation) references stations(sid) on delete restrict on update restrict,constraint endstation foreign key trains(endstation) references stations(sid) on delete restrict on update restrict);

omitted a comma(',') between 'primary key(tid)' and 'constraint startstation..'@lfsyue

chinafenghao

@lfsyue
楼上已经说了，你pk后面少了逗号。
DROP TABLE IF EXISTS  trains;
CREATE TABLE trains (
  tid INTEGER NOT NULL,
  ttype VARCHAR (10) NOT NULL,
  startstation INTEGER NOT NULL,
  endstation INTEGER NOT NULL,
  PRIMARY KEY (tid) ,
  CONSTRAINT fk_startstation FOREIGN KEY  (startstation) REFERENCES stations(sid) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT fk_endstation FOREIGN KEY  (endstation) REFERENCES stations (sid) ON DELETE RESTRICT ON UPDATE RESTRICT
)ENGINE=INNODB ;

DROP TABLE IF EXISTS stations;
CREATE TABLE stations (
id INT(11) NOT NULL AUTO_INCREMENT,
sid INT(11) NOT NULL,
PRIMARY KEY(id),
UNIQUE KEY (sid)
);

归隐乡村

1楼应该好好学学chinafenghao，你发上来的语句看着就纠结，还是看着fenghao发的语句比较清晰。回复 1# lfsyue