SQL 200 script error

Balaji1979 · August 3rd, 2008, 07:09 AM

Hi,

I am getting the following error while executing the script given below..I am using SQL 2000..

Error -

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'HASREP'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'HASMGR'.
Server: Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'SET'.
Server: Msg 170, Level 15, State 1, Line 55
Line 55: Incorrect syntax near 'PLACEDBY'.

Script -

CREATE TABLE CUSTOMERS
(CUST_NUM INTEGER NOT NULL,
COMPANY VARCHAR(20) NOT NULL,
CUST_REP INTERGER,
CREDIT_LIMIT MONEY,
PRIMARY KEY (CUST_NUM),
FOREIGN KEY HASREP (CUST_REP),
REFERENCES SALESREPS
ON DELETE SET NULL
)

CREATE TABLE OFFICERS
(OFFICE INTEGER NOT NULL,
CITY VARCHAR(15)NOT NULL,
REGION VARCHAR(10) NOT NULL,
MGR INTEGER,
TARGET MONEY,
SALES MONEY NOT NULL,
PRIMARY KEY (OFFICE),
FOREIGN KEY HASMGR (MGR)
REFERENCES SALESREPS
ONE DELETE SET NULL
)

CREATE TABLE SALESREPS
(EMPL_NUM INTEGER NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INTEGER,
REP_OFFICE INTEGER,
TITLE VARCHAR(10),
HIRE_DATE DATE NOT NULL,
MANAGER INTEGER,
QUOTA MONEY,
SALES MONEY NOT NULL,
PRIMARY KEY (EMPL_NUM),
FOREIGN KEY (MANAGER)
REFERENCES SALESREPS
ON DELETE SET NULL,
FOREIGN KEY WORKSIN (REP_OFFICE)
REFERENCES OFFICES
ON DELETE SET NULL
)

CREATE TABLE ORDERS
(
ORDER_NUM INTEGER NOT NULL,
ORDER_DATE DATE NOT NULL,
CUST INTEGER NOT NULL,
REP INTEGER,
MFR CHAR(3)NOT NULL,
PRODUCT CHAR(3)NOT NULL,
QTY INTEGER NOT NULL,
AMOUNT MONEY NOT NULL,
PRIMARY KEY (ORDER_NUM),
FOREIGN KEY PLACEDBY(CUST)
REFERENCES CUSTOMERS
ON DELETE CASCADE,
FOREIGN KEY TAKENBY(REP)
REFERENCES SALESREPS
ON DELETE SET NULL,
FOREIGN KEY ISFOR (MFR, PRODUCT)
REFERENCES PRODUCTS
ON DELETE RESTRICT
)

CREATE TABLE PRODUCTS
(
MFR_ID CHAR(3) NOT NULL,
PRODUCT_ID CHAR(3) NOT NULL,
DESCRIPTION VARCHAR(20) NOT NULL,
PRICE MONEY NOT NULL,
QTY_ON_HAND INTEGER NOT NULL,
PRIMARY KEY (MFR_ID, PRODUCT_ID)
)

Someone please help..urgent..

Old Pedant · August 3rd, 2008, 08:46 PM

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'HASREP'.
FOREIGN KEY HASREP (CUST_REP),

(1) T-SQL doesn't allow you to name your foreign keys.
(2) You have a COMMA *after* that and before the word REFERENCES. No. Big mistake.
(3) No idea where you came up with "SET DELETE".
And, by the way, there is no such data type as INTERGER, which is what you coded.

Maybe you should read the documentation???
http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx

So...

Code:

CREATE TABLE CUSTOMERS
(
    CUST_NUM INTEGER NOT NULL,
    COMPANY VARCHAR(20) NOT NULL,
    CUST_REP INTEGER,
    CREDIT_LIMIT MONEY,
    PRIMARY KEY (CUST_NUM),
    FOREIGN KEY (CUST_REP) REFERENCES SALESREP(CUST_REP) ON DELETE CASCADE
)

But you could do this more simply.

Code:

CREATE TABLE CUSTOMERS
(
    CUST_NUM INTEGER NOT NULL PRIMARY KEY,
    COMPANY VARCHAR(20) NOT NULL,
    CUST_REP INTEGER REFERENCES SALESREP(CUST_REP) ON DELETE CASCADE,
    CREDIT_LIMIT MONEY
)

Really, it would help to *carefully* read those docs and then follow what they say. I'm guessing that you are converting this code from some other DB, given your use of "SET NULL" which is not at all appropriate for SQL Server.

Continuing, looking at your other errors:

Code:

CREATE TABLE OFFICERS
(
    OFFICE INTEGER NOT NULL PRIMARY KEY,
    CITY VARCHAR(15)NOT NULL,
    REGION VARCHAR(10) NOT NULL,
    MGR INTEGER REFERENCES SALESREPS(MGR) ON DELETE CASCADE,
    TARGET MONEY,
    SALES MONEY NOT NULL,
)

CREATE TABLE SALESREPS
(
   EMPL_NUM INTEGER NOT NULL PRIMARY KEY
   NAME VARCHAR(15) NOT NULL,
   AGE INTEGER,
   REP_OFFICE INTEGER REFERENCES OFFICES(REP_OFFICE) ON DELETE CASCADE,
   TITLE VARCHAR(10),
   HIRE_DATE DATETIME NOT NULL,
   MANAGER INTEGER REFERENCES SALESREPS(MANAGER) ON DELETE CASCADE,
   QUOTA MONEY,
   SALES MONEY NOT NULL
)

I don't know what to do about your ORDERS table, because I have no idea what "ON DELETE RESTRICT" is supposed to mean. It has no meaning at all in SQL Server.

Anyway, when you change from one version of SQL to another, it pays to learn the new dialect.