SQL for MS SQL 2000

[aramat]

I am in the position of having to create a database for the MS SQL 2000 server. However, I am on a mac and can not test the script that I will be creating. I will send it to a server administrator to upload. The application will be built with php. I will create the application and test it on my system with mySQL. I will then translate the SQL to a format suitable for the MS SQL server. I am new to it. How does the following sample script look (syntax and all) for a MS SQL 2000 server?

CREATE
TABLE projects
(
project_id int NOT NULL identity,
project_name varchar(150) NOT NULL,
type varchar(150) NOT NULL,
project_datetime datetime NOT NULL,
agency varchar(150) NOT NULL,
PRIMARY KEY (project_id)
)
CREATE
TABLE students
(
school_id varchar(75) NOT NULL,
first_name varchar(150) NOT NULL,
last_name varchar(150) NOT NULL,
PRIMARY KEY (school_id)
)
CREATE
TABLE volunteering
(
volunteering_id int NOT NULL identity,
school_id varchar(75) NOT NULL,
type varchar(150) NOT NULL,
agency varchar(150) NOT NULL,
date datetime NOT NULL,
PRIMARY KEY (volunteering_id),
KEY school_id (school_id)
)

Any comments and/or recommendations are appreciated. Thanks!!

[rmiao]

I'll use following:

CREATE
TABLE projects
(
project_id int NOT NULL identity
PRIMARY KEY,
project_name varchar(150) NOT NULL,
type varchar(150) NOT NULL,
project_datetime datetime NOT NULL,
agency varchar(150) NOT NULL
)

CREATE
TABLE students
(
school_id varchar(75) NOT NULL
PRIMARY KEY,
first_name varchar(150) NOT NULL,
last_name varchar(150) NOT NULL
)

CREATE
TABLE volunteering
(
volunteering_id int NOT NULL identity
PRIMARY KEY,
school_id varchar(75) NOT NULL
REFERENCES school_id (school_id),
type varchar(150) NOT NULL,
agency varchar(150) NOT NULL,
date datetime NOT NULL
)

[aramat]

From what I can tell, you have moved PRIMARY KEY in line with the information about that field.

You also changed the name of an index from KEY to REFERENCES and moved that in line with that field.

Is that correct? Would it have worked the other way?

Thanks!!

[rmiao]

I believe that's the correct syntax, and reference is used to set fkey. By the way, what is 'KEY school_id (school_id)' for in your code?

[aramat]

What exactly is the fkey?

With 'KEY school_id (school_id)' I was trying to create another key. It may not be necessary but I thought that I may also want to search files in the database through that key, periodically.

I am new at this and trying to translate a lot of it from mySQL.

[rmiao]

You can have only one primary key and up to 253 foreign keys (fkey) on the table, and should use reference to create fkey.

[aramat]

Thanks. I just looked into this some more. I guess that I should have referenced the table where this is the primary key.

school_id varchar(75) NOT NULL
REFERENCES students (school_id),