MySQL Conversion To MS SQL - Problems With Syntax As Well As With Pk/fk Relationships

Hello all,

For those of you who are able to assist, I'd like to thank you in advance right now. This is a pretty big problem for me.

First let me setup what it is I'm trying to do before I describe the problem in detail. This is part of a semester-long Software Engineering project for my SE class at school. Now, I've got a month for this project, but the database part of it is something I'm trying to get done by this week. Our team is using GoDaddy to host our account and so to simplify our problems with using Visual Studio and MySQL we're switching over to MS SQL, where our code works.

Okay, onto the specifics: I'm used to using a database modeling program called DBDesigner4. Unfortunately, they're support forums have been closed down (fabFORCE.net) and I've spoken with a GoDaddy representative and their servers don't allow me to directly connect with my modeling program and create the schema/tables using the program. However, the program does export to a MySQL table creation script or even an MDB XML file (MSAcess I believe). I'm trying to hand convert the creation script over to MS SQL syntax and I'm having alot of problems (please bear with me, I've never messed with MS SQL before).

For your reference, I'm going to display the actual MySQL script here (just skip this section if you'd like to see the actual problem below):


Code:


CREATE TABLE Addresses (
address_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
address_line1 VARCHAR(30) NOT NULL,
address_line2 VARCHAR(30) NULL,
address_city VARCHAR(30) NOT NULL,
address_state VARCHAR(2) NOT NULL,
address_zip MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY(address_id)
)
TYPE=InnoDB;

CREATE TABLE Broadcasts (
broadcast_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
address_id INTEGER UNSIGNED NOT NULL,
broadcast_title VARCHAR(30) NOT NULL,
broadcast_message VARCHAR(255) NOT NULL,
broadcast_image VARCHAR(255) NOT NULL,
broadcast_date_posted DATETIME NOT NULL,
broadcast_date_expires DATETIME NOT NULL,
broadcast_date_archived DATETIME NULL,
broadcast_clicks BIGINT NOT NULL DEFAULT 0,
broadcast_type ENUM('promo', 'announcement', 'other') NOT NULL DEFAULT 'promo',
broadcast_link VARCHAR(255) NULL,
PRIMARY KEY(broadcast_id),
INDEX Broadcasts_FKIndex1(address_id)
)
TYPE=InnoDB;

CREATE TABLE Classes (
user_id INTEGER UNSIGNED NOT NULL,
subject_id INTEGER UNSIGNED NOT NULL,
class_grade INTEGER UNSIGNED NOT NULL DEFAULT 100,
class_tardies INTEGER UNSIGNED NOT NULL DEFAULT 0,
class_absences INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(user_id, subject_id),
INDEX Users_has_Sections_FKIndex1(user_id),
INDEX Classes_FKIndex2(subject_id)
);

CREATE TABLE Classes_have_Grades (
subject_id INTEGER UNSIGNED NOT NULL,
user_id INTEGER UNSIGNED NOT NULL,
grade_id INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(subject_id, user_id, grade_id),
INDEX Grades_has_Classes_FKIndex1(grade_id),
INDEX Grades_has_Classes_FKIndex2(user_id, subject_id)
);

CREATE TABLE Grades (
grade_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
grade INTEGER UNSIGNED NOT NULL,
grade_type ENUM('h', 'q', 't') NOT NULL,
grade_desc VARCHAR(50) NOT NULL,
PRIMARY KEY(grade_id)
);

CREATE TABLE Locations (
loc_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
address_id INTEGER UNSIGNED NOT NULL,
location_id VARCHAR(20) NOT NULL,
location_name VARCHAR(25) NOT NULL,
PRIMARY KEY(loc_id),
INDEX Locations_FKIndex1(address_id)
)
TYPE=InnoDB;

CREATE TABLE Passports (
passport_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INTEGER UNSIGNED NOT NULL,
location_id INTEGER UNSIGNED NOT NULL,
passport_user VARCHAR(7) NOT NULL,
passport_code VARCHAR(32) NOT NULL,
passport_access ENUM('student', 'faculty', 'admin') NOT NULL DEFAULT 'student',
passport_tries TINYINT UNSIGNED NOT NULL DEFAULT 0,
passport_locked BOOL NOT NULL DEFAULT 'false',
passport_lastaccess DATETIME NULL,
PRIMARY KEY(passport_id),
INDEX Passports_FKIndex1(location_id),
INDEX Passports_FKIndex2(user_id)
)
TYPE=InnoDB;

CREATE TABLE Subjects (
subject_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
subject_name VARCHAR(50) NOT NULL,
subject_offered BOOL NOT NULL,
subject_grade ENUM('1', '2', '3', '4', '5', '6') NOT NULL,
PRIMARY KEY(subject_id)
)
TYPE=InnoDB;

CREATE TABLE Users (
user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
address_id INTEGER UNSIGNED NOT NULL,
user_fname VARCHAR(20) NOT NULL,
user_mletter VARCHAR(1) NULL,
user_lname VARCHAR(20) NOT NULL,
user_grade FLOAT NOT NULL,
user_type ENUM('student', 'faculty') NOT NULL DEFAULT 'student',
user_balance FLOAT NOT NULL,
user_phone CHAR(15) NOT NULL,
user_email VARCHAR(50) NOT NULL,
PRIMARY KEY(user_id),
INDEX Users_FKIndex1(address_id)
)
TYPE=InnoDB;



That script runs just fine on MySQL and generates my application's tables with the correct relationships intact, indexes, etc.

However, as I said, I need this setup in MS SQL with a few additional checks and I've hand converted most of this, not knowing how to properly maintain primary keys or foreign keys:


Code:


CREATE TABLE Addresses
(
address_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

address_line1
VARCHAR(30)
NOT NULL,

address_line2
VARCHAR(30)
NULL,

address_city
VARCHAR(30)
NOT NULL,

address_state
VARCHAR(2)
NOT NULL,

address_zip
ZIPCODE
CONSTRAINT CK_address_zip
CHECK (address_zip LIKE '[0-9][0-9][0-9][0-9][0-9] ')
)

CREATE TABLE Broadcasts
(
broadcast_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

address_id
INT
NOT NULL,

broadcast_title
VARCHAR(30)
NOT NULL,

broadcast_message
VARCHAR(255)
NOT NULL,

broadcast_image
VARCHAR(255)
NOT NULL,

broadcast_date_posted
DATETIME
NOT NULL,

broadcast_date_expires
DATETIME
NOT NULL,

broadcast_date_archived
DATETIME
NULL,

broadcast_clicks
INT
NOT NULL
DEFAULT 0,

broadcast_type
CHAR(1)
NOT NULL
DEFAULT(‘a’),

broadcast_link
VARCHAR(255)
NULL
)

CREATE TABLE Classes
(
user_id
INT
PRIMARY KEY
NOT NULL,

subject_id
INT
NOT NULL,

class_grade
INT
NOT NULL
DEFAULT 100,

class_tardies
INT
NOT NULL
DEFAULT 0,

class_absences
INT
NOT NULL
DEFAULT 0
)

CREATE TABLE Classes_have_Grades
(
subject_id
INT
NOT NULL,

user_id
INT PRIMARY KEY
NOT NULL,

grade_id
INT
NOT NULL
)

CREATE TABLE Grades
(
grade_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

Grade
INT
NOT NULL,

grade_type
CHAR(1)
NOT NULL
DEFAULT(‘h’),

grade_desc
VARCHAR(50)
NOT NULL
)

CREATE TABLE Locations
(
loc_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

address_id
INT
NOT NULL,

location_id
VARCHAR(20)
NOT NULL,

location_name
VARCHAR(25)
NOT NULL
)

CREATE TABLE Passports
(
passport_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

user_id
USERID
NOT NULL
CONSTRAINT CK_user_id
CHECK (user_id LIKE '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]'),

location_id
INT
NOT NULL,

passport_user
VARCHAR(7)
NOT NULL,

passport_code
VARCHAR(32)
NOT NULL,

passport_access
CHAR(1)
NOT NULL
DEFAULT(‘s’),

passport_tries
TINYINT
NOT NULL
DEFAULT(0),

passport_locked
BOOL
NOT NULL
DEFAULT(false),

passport_lastaccess
DATETIME
NULL
)

CREATE TABLE Subjects
(
subject_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

subject_name
VARCHAR(50)
NOT NULL,

subject_offered
BOOL
NOT NULL,

subject_grade
SMALLINT
NOT NULL
DEFAULT 6
)

CREATE TABLE Users
(
user_id
INT
PRIMARY KEY
IDENTITY(1,1)
NOT NULL,

address_id
INT
NOT NULL,

user_fname
VARCHAR(20)
NOT NULL,

user_mletter
CHAR(1)
NULL,

user_lname
VARCHAR(20)
NOT NULL,

user_grade
FLOAT
NOT NULL,

user_type
CHAR(1)
NOT NULL
DEFAULT(‘s’),

user_balance
FLOAT
NOT NULL,

user_phone
CHAR(15)
NOT NULL,

user_email
VARCHAR(50)
NOT NULL
)



Unfortunately, Query Analyzer seems to have a problem and says I have an error in my syntax (on line 74) near "'".

I've googled around and found out a couple of things: TSQL doesn't support enumerations (that I'm aware of - therefore I converted my enum fields to CHAR(1)s or INTs) and that the single quotes surrounding default values is the proper way to do DEFAULT values. I can't figure out for the life of me what's wrong with my syntax and I've been going at this for about 4 hours now.

If you can help me out by explaining how I should properly do the PKs/FKs (I believe there's a keyword REFERENCE(field1, field2) for FKs, but I'm not sure where to place it, etc.) I'll go through the SQL script and try to implement it, but I have a feeling this is going to be a long thread if I do get someone willing to help.

Thanks to everyone who took the time to read this,
Ahad L. Amdani