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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may