mysql2sqlite icon indicating copy to clipboard operation
mysql2sqlite copied to clipboard

Invalid extra comma for CREATE TABLE with CONSTRAINT

Open ml31415 opened this issue 2 years ago • 3 comments

Original:

CREATE TABLE "test1" (
  "id" bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  "match_id" bigint(20) unsigned NOT NULL,
  "season_id" int(11) unsigned DEFAULT NULL,
  "date" datetime DEFAULT NULL,
  PRIMARY KEY ("id"),
  UNIQUE KEY "match_id" ("match_id","date"),
  KEY "FK_SEASON_ID" ("season_id"),
  CONSTRAINT "FK_SEASON_ID" FOREIGN KEY ("season_id") REFERENCES "season" ("id") ON DELETE CASCADE
);

Currently becomes:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "test1" (
  "id" integer  NOT NULL PRIMARY KEY AUTOINCREMENT
,  "match_id" integer  NOT NULL
,  "season_id" integer  DEFAULT NULL
,  "date" datetime DEFAULT NULL
,  UNIQUE ("match_id","date")
,
,  CONSTRAINT "FK_SEASON_ID" FOREIGN KEY ("season_id") REFERENCES "season" ("id") ON DELETE CASCADE
);
CREATE INDEX "idx__" ON "" ("season_id");
END TRANSACTION;

The comma in the line between UNIQUE and CONSTRAINT causes an error in sqlite, that stops the table from being created, and the data import to fail:

Error: near line 4: in prepare, near ",": syntax error (1)

ml31415 avatar Jun 05 '23 10:06 ml31415