×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MySQL to SQL Express

MySQL to SQL Express

MySQL to SQL Express

(OP)
Hi everybody,

I have an application that was previously running with MySQL, and I am in the midst of converting it to us SQL Express 2008. I originally had a .sql file I was running to create my tables, the indexes etc... that was created with the assistance of a friend. It worked very well, however, I quickly found out I can't use the same .sql file under SQL Express to do the same. I started trying to figure out how to convert it, but quite honestly got lost in no time.  I can create the tables to pretty much match the same structure, but I get lost when it comes to the indexes and relationships. I was wondering if someone would be kind enough to do the conversion for me. Here's the MySQL version:

CODE


CREATE DATABASE `booklib`
    CHARACTER SET 'latin1';

USE `booklib`;

SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

#
# Structure for the `inventory` table :
#

CREATE TABLE `inventory` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `EAN` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
  `ISBN` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
  `ASIN` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `UPC` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
  `Disks` smallint(6) DEFAULT NULL,
  `PublDate` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `Title` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `Description` varchar(5000) CHARACTER SET utf8 DEFAULT NULL,
  `Image` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `Pages` smallint(6) DEFAULT NULL,
  `ListPrice` decimal(19,4) DEFAULT NULL,
  `BindingID` int(11) DEFAULT NULL,
  `GenreID` int(11) DEFAULT NULL,
  `LabelID` int(11) DEFAULT NULL,
  `PublID` int(11) DEFAULT NULL,
  `Owner` varchar(50) DEFAULT NULL,
  `Location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ASIN` (`ASIN`),
  KEY `EAN` (`EAN`),
  KEY `ISBN` (`ISBN`),
  KEY `BindingID` (`BindingID`) USING BTREE,
  KEY `GenreID` (`GenreID`) USING BTREE,
  KEY `LabelID` (`LabelID`) USING BTREE,
  KEY `PublID` (`PublID`) USING BTREE,
  KEY `BindingInventory` (`BindingID`) USING BTREE,
  KEY `GenreInventory` (`GenreID`) USING BTREE,
  KEY `LabelsInventory` (`LabelID`) USING BTREE,
  KEY `PublishersInventory` (`PublID`) USING BTREE
);

#
# Structure for the `actors` table :
#

CREATE TABLE `actors` (
  `ActorID` int(11) NOT NULL AUTO_INCREMENT,
  `ActorName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `InventoryID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ActorID`),
  KEY `InventoryActors` (`InventoryID`),
  KEY `InventoryID` (`InventoryID`),
  CONSTRAINT `InventoryActors` FOREIGN KEY (`InventoryID`) REFERENCES `inventory` (`ID`) ON DELETE CASCADE
);

#
# Structure for the `artists` table :
#

CREATE TABLE `artists` (
  `ArtistID` int(11) NOT NULL AUTO_INCREMENT,
  `ArtistName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `InventoryID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ArtistID`),
  KEY `InventoryArtists` (`InventoryID`),
  KEY `InventoryID` (`InventoryID`),
  CONSTRAINT `InventoryArtists` FOREIGN KEY (`InventoryID`) REFERENCES `inventory` (`ID`) ON DELETE CASCADE
);

#
# Structure for the `author` table :
#

CREATE TABLE `author` (
  `AuthorID` int(11) NOT NULL AUTO_INCREMENT,
  `author` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `InventoryID` int(11) DEFAULT NULL,
  PRIMARY KEY (`AuthorID`),
  KEY `InventoryAuthor` (`InventoryID`),
  KEY `InventoryID` (`InventoryID`),
  CONSTRAINT `InventoryAuthor` FOREIGN KEY (`InventoryID`) REFERENCES `inventory` (`ID`) ON DELETE CASCADE
);

#
# Structure for the `binding` table :
#

CREATE TABLE `binding` (
  `BindingID` int(11) NOT NULL AUTO_INCREMENT,
  `binding` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`BindingID`)
);

#
# Structure for the `genre` table :
#

CREATE TABLE `genre` (
  `GenreID` int(11) NOT NULL AUTO_INCREMENT,
  `GenreType` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`GenreID`)
);

#
# Structure for the `labels` table :
#

CREATE TABLE `labels` (
  `LabelID` int(11) NOT NULL AUTO_INCREMENT,
  `LabelName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`LabelID`)
);

#
# Structure for the `publishers` table :
#

CREATE TABLE `publishers` (
  `PublID` int(11) NOT NULL AUTO_INCREMENT,
  `PublisherName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`PublID`)
);

#
# Structure for the `disks` table :
#

CREATE TABLE `disks` (
  `DiskID` int(11) NOT NULL AUTO_INCREMENT,
  `InventoryID` int(11) DEFAULT '0',
  PRIMARY KEY (`DiskID`),
  KEY `InventoryDisks` (`InventoryID`),
  KEY `InventoryID` (`InventoryID`),
  CONSTRAINT `InventoryDisks` FOREIGN KEY (`InventoryID`) REFERENCES `inventory` (`ID`) ON DELETE CASCADE
);

#
# Structure for the `tracks` table :
#

CREATE TABLE `tracks` (
  `TrackID` int(11) NOT NULL AUTO_INCREMENT,
  `DiskNo` smallint(6) DEFAULT NULL,
  `TrackNo` smallint(6) DEFAULT NULL,
  `TrackTitle` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `DiskID` int(11) NOT NULL,
  PRIMARY KEY (`TrackID`),
  UNIQUE KEY `PrimaryKey` (`TrackID`),
  KEY `DisksTracks` (`DiskID`),
  CONSTRAINT `DisksTracks` FOREIGN KEY (`DiskID`) REFERENCES `disks` (`DiskID`) ON DELETE CASCADE
);

Any help I can get will be greatly appreciated!!

 

Cheers,

Realm174
http://sites.google.com/site/mymediadatabase/

RE: MySQL to SQL Express

(OP)
ahhh never mind everybody, I figured out what the "Database Diagram" is for in the SQL Server Management Studio...

Mod: Feel free to delete this thread... I couldn't figure out how to delete my post.

 

Cheers,

Realm174
http://sites.google.com/site/mymediadatabase/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close