How can I rewrite this MS SQL Script as a MySQL Script?

Question

I'm trying to work my way through a Java Tutorial.

The author wrote the tutorial to work with MS SQL. I'd like to follow the tutorial using MySQL. I'm not completely sure how to translate the MS SQL script which uses "IDENTITY", "CONSTRAINT", and "CLUSTERED" as you'll see below:

CREATE TABLE [event_person] (
  [event_id] [int] NOT NULL,
  [person_id] [int] NOT NULL,
  CONSTRAINT [PK_event_person] PRIMARY KEY CLUSTERED 
  (
    [event_id] ASC,
    [person_id] ASC
  )
)

CREATE TABLE [events] (
  [id] [int] IDENTITY(1,1) NOT NULL,
  [dt] [datetime] NULL,
  [name] [nvarchar](50) NULL,
  CONSTRAINT [PK_events] PRIMARY KEY CLUSTERED 
  (
    [id] ASC
  )
)

CREATE TABLE [people] (
  [id] [int] IDENTITY(1,1) NOT NULL,
  [name] [varchar](50) NOT NULL,
  CONSTRAINT [PK_people] PRIMARY KEY CLUSTERED 
  (
    [id] ASC
  )
)

This is as far as I have been able to get with it:

CREATE TABLE event_person (
  event_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  person_id int NOT NULL
);

CREATE TABLE events (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  dt datetime NULL,
  name nvarchar(50) NOT NULL);


CREATE TABLE people (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name nvarchar(50) NOT NULL);

... but I'm concerned that the omitted code will result in lost functionality and even incompatibility with the rest of the tutorial.

Is there a better way I should write this?

Answer 1

The primary keys for the event_person tables are not equivalent in the two scripts. The SQL Server example is using a composite primary key, made up of two fields. This cannot be defined in the column specification, so we need to use a separate PRIMARY KEY clause:

CREATE TABLE `event_person` (
  `event_id` int NOT NULL,
  `person_id` int NOT NULL,
  PRIMARY KEY (`event_id`, `person_id`)
);

Also note that there is no nvarchar data type in MySQL. You would need to use a column character set:

CREATE TABLE `events` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `dt` datetime,
  `name` varchar(50) CHARACTER SET ucs2
);

In addition, note that in the above example we did not give names to the primary key constraints. While this is probably irrelevant, you may want to give names as follows:

CREATE TABLE `event_person` (
  `event_id` int NOT NULL,
  `person_id` int NOT NULL,
  CONSTRAINT `pk_event_person` PRIMARY KEY (`event_id`, `person_id`)
);

CREATE TABLE `events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dt` datetime,
  `name` varchar(50) CHARACTER SET ucs2,
  CONSTRAINT `pk_ events ` PRIMARY KEY (`id`)
);

Answer 2

+1 to @Daniel Vassallo's answer, and also these tips:

• Microsoft uses square brackets to delimit identifiers, whereas MySQL uses back-ticks. Both databases can be put into a mode to use ANSI SQL standard identifier delimiters, which are double-quotes. In MySQL, don't delimit data type names (e.g. [int]).

• IDENTITY is specific to Microsoft (and Sybase, fwiw). In MySQL, use AUTO_INCREMENT. You can also use SERIAL which is shorthand for BIGINT UNSIGNED AUTO_INCREMENT.

• There's no support for a CLUSTERED keyword in MySQL. The primary key is always a clustered index in MySQL's InnoDB storage engine (which you should use unless you have a good reason not to). Also you don't need to declare ASC or DESC for indexes in MySQL.