Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 3rd, 2008, 07:09 AM
Registered User
 
Join Date: May 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL 200 script error

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 August 3rd, 2008, 08:46 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Connect Seagate Crystal Report with SQL Server 200 abypvarghese SQL Server 2005 6 June 23rd, 2006 03:10 AM
Problem with AGGREGATE FUNCTIONS in SQL SERVER 200 vikiboye SQL Server 2005 1 February 20th, 2006 12:07 PM
generate XML from SQL server 200 table sasidhar79 SQL Server DTS 0 August 2nd, 2005 04:47 PM
problem 2 connecting SQL Server 200 using ASP.NET nagen111 ADO.NET 5 February 16th, 2005 01:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.