devxlogo

MySQL to MS SQL Syntax

MySQL to MS SQL Syntax

Question:
I have the following dump for MySQL and am having a problem getting it corrected for MS SQL. Can you help?

Here is the dump file:

# MySQL dump CREATE TABLE tblBids (  biNum int(11) DEFAULT '0' NOT NULL auto_increment,  biItem int(11),  biUser int(11),  biQuantity int(11),  biPrice decimal(7,2),  biMax decimal(7,2),  biTime datetime,  biOutbidflag tinyint(1),  PRIMARY KEY (biNum),  UNIQUE PRI (biNum));CREATE TABLE tblItems (  itNum int(11) DEFAULT '0' NOT NULL auto_increment,  itCategory varchar(255),  itTitle varchar(255),  itSeller int(11),  itStart decimal(7,2),  itReserve decimal(7,2),  itIncrement decimal(7,2),  itQuantity int(11),  itOpen datetime,  itClose datetime,  itDesc text,  itImage varchar(255),  itClosedflag tinyint(1),  PRIMARY KEY (itNum),  UNIQUE PRI (itNum));CREATE TABLE tblRatings (  raNum int(11) DEFAULT '0' NOT NULL auto_increment,  raRating tinyint(4),  raComment varchar(255),  raUser int(11),  raRater int(11),  PRIMARY KEY (raNum),  UNIQUE PRI (raNum));CREATE TABLE tblUsers (  usNum int(11) DEFAULT '0' NOT NULL auto_increment,  usName varchar(255),  usEmail varchar(255),  usPassword varchar(255),  usFullname varchar(255),  usStreet varchar(255),  usCity varchar(255),  usState char(2),  usZip varchar(10),  usPhone varchar(13),  PRIMARY KEY (usNum),  UNIQUE PRI (usNum));

Thanks!

Answer:
My assumption is that this is MS SQL Server 6.5.

The first thing to do for the tblBids declaration is change the first line from

biNum int(11) DEFAULT '0' NOT NULL auto_increment  

to

biNum int IDENTITY(1,1) NOT NULL

Note that you don’t specify the width of INT, the keyword for SQL Server’s auto-incrementing property is called the IDENTITY property, and you do not need a DEFAULT on it. You also don’t need quotes around an INT type, and you can forget about the UNIQUE constraint, because just naming a PRIMARY KEY presumes this constraint.

The first declaration looks like this:

CREATE TABLE tblBids (  biNum int IDENTITY(1,1) NOT NULL,   biItem int,  biUser int,  biQuantity int,  biPrice decimal(7,2),  biMax decimal(7,2),  biTime datetime,  biOutbidflag tinyint,  PRIMARY KEY (biNum))

You did not say anything about biItem, which I suspect is a foreign key contributed by tblItems. There is no reference to the primary key of tblItems as the foreign key into tblBids, although you could make it so by executing the following:

, CONSTRAINT FK_ITEMS REFERENCES tblItems.itNum

The issue with doing this is that you haven’t created tblItems yet, much less its primary key. Therefore, it’s a little bit “cart before the horse”-like to create this constraint at this point.

Let’s create tblItems now with this edited statement:

 CREATE TABLE tblItems (  itNum int IDENTITY(1,1) NOT NULL ,  itCategory varchar(255),  itTitle varchar(255),  itSeller int,  itStart decimal(7,2),  itReserve decimal(7,2),  itIncrement decimal(7,2),  itQuantity int,  itOpen datetime,  itClose datetime,  itDesc text,  itImage varchar(255),  itClosedflag tinyint,  PRIMARY KEY (itNum))

This should run without incident. Now that tblItems has been created with a primary key, you can use it as a reference in tblBids. You need to add this constraint to tblBids after the fact, though, because you didn’t have the primary key without first creating tblItems. Now that you have it, you can alter the table tblBids to use tblItems primary key as a foreign key by executing this:

ALTER TABLE tblBIDS ADD CONSTRAINT FK_ITEMSFOREIGN KEY (biNum)REFERENCES tblItems(itNum)

This establishes the referential integrity between tblItems and tblBids?meaning that there will be no orphan item records because the bids are linked to many items.

I don’t see any other corrections necessary except those of the type I discussed earlier having to do with INT data types, so if you change your last two scripts accordingly, the tables should be created just fine.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist