mysql - 创建 mysql 表，括号错误 : You have an error in your SQL syntax; check the manual . .. 在第 12 行 ')' 附近使用正确的语法

Question

作为一个最近学习 SQL 的人，我已经尝试过这段创建 6 个表的代码数十次，但无法弄清楚 shell 说“)”附近有问题是什么意思。

如果有明显的语法错误，我可以修复它。

感谢您的帮助。

-- Table APPOINTMENT 

CREATE TABLE APPOINTMENT (
  AppointmentID INTEGER NOT NULL AUTO_INCREMENT,
  DateAndTime VARCHAR(20) NULL DEFAULT NULL,
  Venue VARCHAR(20) NULL DEFAULT NULL,
  PatientName VARCHAR(20) NULL DEFAULT NULL,
  DoctorName VARCHAR(20) NULL DEFAULT NULL,
  PatientID VARCHAR(20) NOT NULL,
  DoctorID VARCHAR(20) NOT NULL,
  IsEmergency VARCHAR(3) DEFAULT "YES",
  IsVisited VARCHAR(3) DEFAULT "YES",
  PRIMARY KEY (AppointmentID),
);


-- Table PATIENT

CREATE TABLE PATIENT (
  PatientID VARCHAR(20) NOT NULL,
  PatientName VARCHAR(20) NULL DEFAULT NULL,
  Address VARCHAR(20) NULL DEFAULT NULL,
  CurrentMedication VARCHAR(20) NULL DEFAULT NULL,
  ChronicDisease VARCHAR(20) NULL DEFAULT NULL,
  Allergies VARCHAR(20) NULL DEFAULT NULL,
  MedicalHistory VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (PatientID),
);


-- Table DOCTOR

CREATE TABLE DOCTOR (
  DoctorID VARCHAR(20) NOT NULL,
  DoctorName VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (DoctorID),
);


-- Table BILL

CREATE TABLE BILL (
  AppointmentID INTEGER NOT NULL AUTO_INCREMENT,
  DoctorID VARCHAR(20) NOT NULL,
  PatientID VARCHAR(20) NOT NULL,
  DateAndTime VARCHAR(20) NULL DEFAULT NULL,
  Diagnosis VARCHAR(20) NULL DEFAULT NULL,
  Treatment VARCHAR(20) NULL DEFAULT NULL,
  Fees INTEGER NULL DEFAULT NULL,
  ServiceCharge INTEGER NULL DEFAULT NULL,
  ClaimId INTEGER NOT NULL,
  InsuredDeduction INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  ReceiptNumber INTEGER NOT NULL,
  AmountPaid INTEGER NULL DEFAULT NULL,
  TotalAmountDue INTEGER NULL DEFAULT NULL,
  PRIMARY KEY (AppointmentID),
);


-- Table PAYMENT

CREATE TABLE PAYMENT (
  ReceiptNumber INTEGER NOT NULL AUTO_INCREMENT,
  AppointmentID INTEGER NOT NULL,
  AmountPaid INTEGER NULL DEFAULT NULL,
  PatientID VARCHAR(20) NOT NULL,
  PRIMARY KEY (ReceiptNumber)
);


-- Table CLAIM

CREATE TABLE CLAIM (
  ClaimId INTEGER NOT NULL AUTO_INCREMENT,
  InsuranceCompanyName VARCHAR(20) NULL DEFAULT NULL,
  PatientID VARCHAR(20) NOT NULL,
  PRIMARY KEY (ClaimId),
);


-- Foreign Keys 

ALTER TABLE APPOINTMENT ADD FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID);
ALTER TABLE APPOINTMENT ADD FOREIGN KEY (DoctorID) REFERENCES DOCTOR (DoctorID);
ALTER TABLE PAYMENT ADD FOREIGN KEY (AppointmentID) REFERENCES BILL (AppointmentID);
ALTER TABLE PAYMENT ADD FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID);
ALTER TABLE BILL ADD FOREIGN KEY (DoctorID) REFERENCES DOCTOR (DoctorID);
ALTER TABLE BILL ADD FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID);
ALTER TABLE BILL ADD FOREIGN KEY (ClaimId) REFERENCES CLAIM (ClaimId);
ALTER TABLE BILL ADD FOREIGN KEY (ReceiptNumber) REFERENCES PAYMENT (ReceiptNumber);
ALTER TABLE CLAIM ADD FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID);

Answer 1

每个创建表语句中都有一个额外的逗号。例如 PRIMARY KEY (DoctorID) 后面有一个逗号。 MySql 期望在该逗号之后有另一个列声明。

-- Table DOCTOR

CREATE TABLE DOCTOR (
 DoctorID VARCHAR(20) NOT NULL,
 DoctorName VARCHAR(20) NULL DEFAULT NULL,
 PRIMARY KEY (DoctorID)
);