Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Aug 10, 1999

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_ITEMS
FOREIGN 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.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date