MySQL to SQL Express

[realm174]

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:

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/

 

[realm174]

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/