mysql2sqlite icon indicating copy to clipboard operation
mysql2sqlite copied to clipboard

Getting integer at the end of a foreign key definition if the column name has int as a substring

Open pgregorio-signalytic opened this issue 2 years ago • 0 comments

This MySQL query for creating a table:

CREATE TABLE IF NOT EXISTS appointment_service_type (
  appointment_service_type_id INT NOT NULL AUTO_INCREMENT ,
  appointment_service_id INT NOT NULL,
  name VARCHAR(50) NOT NULL ,
  duration_mins INT NULL ,
  date_created DATETIME NOT NULL ,
  creator INT NOT NULL ,
  date_changed DATETIME NULL ,
  changed_by INT NULL ,
  voided TINYINT NULL ,
  voided_by INT,
  date_voided DATETIME,
  void_reason VARCHAR(255),
  uuid  VARCHAR(38) NOT NULL UNIQUE,
  PRIMARY KEY (appointment_service_type_id) ,
  INDEX fk_appointment_service_idx (appointment_service_id ASC) ,
  UNIQUE INDEX service_name_dur_UNIQUE (appointment_service_id, name, duration_mins ASC) ,
  CONSTRAINT fk_appointment_service
  FOREIGN KEY (appointment_service_id)
  REFERENCES appointment_service (appointment_service_id)
)

Seems to generate a syntax error when being converted to sqlite3 using this script:

-- ./mysql2sqlite mysql_dump.sql > sqlite_dump.sql
CREATE TABLE `appointment_service_type` (
   `appointment_service_type_id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `appointment_service_id` integer NOT NULL
,  `name` varchar(50) NOT NULL
,  `duration_mins` integer DEFAULT NULL
,  `date_created` datetime NOT NULL
,  `creator` integer NOT NULL
,  `date_changed` datetime DEFAULT NULL
,  `changed_by` integer DEFAULT NULL
,  `voided` integer DEFAULT 0
,  `voided_by` integer DEFAULT NULL
,  `date_voided` datetime DEFAULT NULL
,  `void_reason` varchar(255) DEFAULT NULL
,  `uuid` varchar(38) NOT NULL
,  UNIQUE (`uuid`)
,  CONSTRAINT `fk_appointment_service` FOREIGN KEY (`appointment_service_id`) REFERENCES `appointment_service` integer
);

Notice the REFERENCES `appointment_service` integer at the very end?

It looks like the mysql2sqlite script incorrectly turns

REFERENCES `appointment_service` (`appointment_service_id`)

Into:

REFERENCES `appointment_service` integer

The rule on this line seems to be too greedy.. it seems to be matching the int in appoINTment and replacing the whole bit with integer.

pgregorio-signalytic avatar Mar 30 '23 17:03 pgregorio-signalytic